MySQL 8.0 出来也有很长一段时间了，最近计划将 MySQL 5.7.39 升级为 MySQL 8.0.32，由于原数据中使用的字符集和排序规则比较杂乱，且 MySQL 8.0 默认引擎、字符集都改为了 InnoDB 和 utf8mb4，所以趁这次机会把数据库中使用的字符集和排序规则统一一下。
本来打算人工修改，但是上百张表修改起来也是一个不小的工作量，于是通过网络找到了找到了如下方法：
1. 更改数据库的默认字符集和排序规则#
-- ALTER DATABASE `<数据库名>` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
SELECT
CONCAT('ALTER DATABASE `', SCHEMA_NAME, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS `SQL`
FROM
information_schema.`SCHEMATA`
WHERE
-- 排除MySQL自带的数据库，这里可以修改为: SCHEMA_NAME IN ('数据库1', '数据库2', '数据库3')
SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND (DEFAULT_CHARACTER_SET_NAME != 'utf8mb4' OR DEFAULT_COLLATION_NAME != 'utf8mb4_unicode_ci')
;
2. 更改表存储引擎、默认字符集和排序规则#
-- ALTER TABLE `<数据库名>`.`<表名>` ENGINE = InnoDB, CHARACTER SET = utf8mb4, COLLATE = utf8mb4_unicode_ci;
SELECT
CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE = InnoDB, CHARACTER SET = utf8mb4, COLLATE = utf8mb4_unicode_ci;') AS `SQL`
FROM
information_schema.`TABLES`
WHERE
-- 排除MySQL自带的数据库，这里可以修改为: TABLE_SCHEMA IN ('数据库1', '数据库2', '数据库3')
TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND (ENGINE != 'InnoDB' OR TABLE_COLLATION != 'utf8mb4_unicode_ci')
;
3. 更改字段字符集和排序规则#
-- ALTER TABLE `<数据库名>`.`<表名>`
-- MODIFY COLUMN `<字段名>` <字段类型> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '<备注>';
SELECT
CONCAT(
-- 指定的库和表名
'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
-- 指定的字段和数据类型
'MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
-- 指定的字符集和对应的排序规则
' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ',
-- 是否非空和默认值的组合
(CASE
WHEN IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NULL THEN 'NOT NULL '
WHEN IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NOT NULL THEN CONCAT('NOT NULL DEFAULT \'', COLUMN_DEFAULT, '\' ')
WHEN IS_NULLABLE = 'YES' AND COLUMN_DEFAULT IS NULL THEN 'NULL DEFAULT NULL '
WHEN IS_NULLABLE = 'YES' AND COLUMN_DEFAULT IS NOT NULL THEN CONCAT('NULL DEFAULT \'', COLUMN_DEFAULT, '\' ')
END),
-- 保留原始注释信息
'COMMENT \'', COLUMN_COMMENT, '\';'
) AS `SQL`
FROM
information_schema.`COLUMNS`
WHERE
-- 排除MySQL自带的数据库，这里可以修改为: TABLE_SCHEMA IN ('数据库1', '数据库2', '数据库3')
TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND (CHARACTER_SET_NAME != 'utf8mb4' OR COLLATION_NAME != 'utf8mb4_unicode_ci')
AND DATA_TYPE IN ('varchar', 'char', 'text')
;
注意: 如果需要修改生产环境数据库，请在访问低峰期时执行 SQL 语句，如果表中涉及外键可使用 SET FOREIGN_KEY_CHECKS=0; 暂时关闭外键约束，执行完后再使用 SET FOREIGN_KEY_CHECKS=1; 打开外键约束。