MySQL批量更改資料庫、表格、欄位的字符集和排序規則

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; 打開外鍵約束。

【MySQL】批量更改庫，表，字段的字符集 - emdzz - 博客園
 MySQL 批量修改字符集 - 簡書