【Confluence】Mysql数据库规则更改

背景

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

修改实例规则

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

修改表规则

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'
    );

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

修改字段规则

Varchar类型

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]

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'
    );

注意

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

SET FOREIGN_KEY_CHECKS=0;