软件开发技术分享
0%

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

发表于 分类于
本文字数： 3k 阅读时长 ≈ 3 分钟
MySQL 5.7.x 升级 MySQL 8.0，趁机统一数据库存储引擎、字符集和排序规则。

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 批量修改字符集 - 简书

相关文章