【Confluence】Mysql数据库规则更改

## 背景

在Confluence进行版本升级的时候，如果之前是7，升级到9那么也需要进行数据库的排序规则升级，如果不升级的话，会出现错误的故障信息

## 修改实例规则

```sql
ALTER DATABASE `confluencedb` CHARACTER SET `utf8mb4` COLLATE `utf8mb4_bin`;
```

## 修改表规则

```sql
SELECT CONCAT('ALTER TABLE `', `table_name`, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;')
FROM `information_schema`.`TABLES` AS `T`,
     `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY` AS `C`
WHERE `C`.`collation_name` = `T`.`table_collation`
  AND `T`.`table_schema` = 'confluencedb'
  AND (
    `C`.`CHARACTER_SET_NAME` != 'utf8mb4'
        OR
    `C`.`COLLATION_NAME` != 'utf8mb4_bin'
    );
```

上面的搜索查询出来之后是所有需要修改的表结构的语句，全部复制出来，同时运行，截图如下

![](https://oss-image.taowiki.com/blog-image/2025/03/13/67d2398fbd5b6.png)![](https://oss-image.taowiki.com/blog-image/2025/03/13/67d239a813572.png)

## 修改字段规则

### Varchar类型

```sql
SELECT CONCAT('ALTER TABLE `', `table_name`, '` MODIFY `', `column_name`, '` ', `DATA_TYPE`, '(',
              `CHARACTER_MAXIMUM_LENGTH`, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
              (CASE WHEN `IS_NULLABLE` = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'confluencedb'
  AND `DATA_TYPE` = 'varchar'
  AND (
    `CHARACTER_SET_NAME` != 'utf8mb4'
        OR
    `COLLATION_NAME` != 'utf8mb4_bin'
    );
```

上面的搜索查询出来之后是所有需要修改的表结构的语句，全部复制出来，同时运行。

但是修改字段会很慢，因为有些表里面的数据庞大，就会导致语句执行很慢

### 非Varchar类型

[scode type="yellow"]

这里用Sql执行出来的部分语句是有问题的，比如：

ALTER TABLE \`AO\_02369B\_LSDATA\_ENTITY\` MODIFY \`KEYKEY\` longtext(4294967295) CHARACTER SET utf8mb4 COLLATE utf8mb4\_bin;

longtext不应该有长度，所以需要手动替换成

ALTER TABLE \`AO\_02369B\_LSDATA\_ENTITY\` MODIFY \`KEYKEY\` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4\_bin;

[/scode]

```sql
SELECT CONCAT('ALTER TABLE `', `table_name`, '` MODIFY `', `column_name`, '` ', `DATA_TYPE`, '(',
              `CHARACTER_MAXIMUM_LENGTH`, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
              (CASE WHEN `IS_NULLABLE` = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'confluencedb'
  AND `DATA_TYPE` != 'varchar'
  AND (
    `CHARACTER_SET_NAME` != 'utf8mb4'
        OR
    `COLLATION_NAME` != 'utf8mb4_bin'
    );
```

## 注意

在处理数据的时候会出现外键的问题，所以在处理之前先忽略。处理完成之后在恢复

```sql
SET FOREIGN_KEY_CHECKS=0;
```