0%

MySQL批量更改数据库、表、字段的字符集和排序规则

MySQL 8.0 出来也有很长一段时间了,最近计划将 MySQL 5.7.39 升级为 MySQL 8.0.32,由于原数据中使用的字符集和排序规则比较杂乱,且 MySQL 8.0 默认引擎、字符集都改为了 InnoDB 和 utf8mb4,所以趁这次机会把数据库中使用的字符集和排序规则统一一下。

本来打算人工修改,但是上百张表修改起来也是一个不小的工作量,于是通过网络找到了找到了如下方法:

1. 更改数据库的默认字符集和排序规则

1
2
3
4
5
6
7
8
9
10
11
-- 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. 更改表存储引擎、默认字符集和排序规则

1
2
3
4
5
6
7
8
9
10
11
-- 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. 更改字段字符集和排序规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 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; 打开外键约束。

【MySQL】 批量更改库,表,字段的字符集 - emdzz - 博客园
MySQL 批量修改字符集 - 简书