修改MySQL全表全列排序规则时遇外键兼容错误的自动化解决方法

需要修改MySQL中my_schema下所有基础表的char、varchar类型列排序规则为utf8mb4_0900_ai_ci，使用了以下生成ALTER语句的SQL脚本：

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME,'` ',
              DATA_TYPE, IF(CHARACTER_MAXIMUM_LENGTH IS NULL
       OR DATA_TYPE LIKE 'longtext', '', CONCAT('(', CHARACTER_MAXIMUM_LENGTH,
                                         ')')
       ), ' COLLATE utf8mb4_0900_ai_ci;') AS 'USE whitesource_collate_test;'
    FROM   INFORMATION_SCHEMA.COLUMNS
    WHERE  TABLE_SCHEMA = 'my_schema'
           AND (SELECT INFORMATION_SCHEMA.TABLES.TABLE_TYPE
                FROM   INFORMATION_SCHEMA.TABLES
                WHERE  INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA =
                       INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA
                       AND INFORMATION_SCHEMA.TABLES.TABLE_NAME =
                           INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
                LIMIT  1) LIKE 'BASE TABLE'
           AND DATA_TYPE IN ( 'char', 'varchar' )

执行部分表后出现错误：

Referencing column 'COL1_ID_' and referenced column 'ID_' in foreign key constraint 'MY_FK_GROUP' are incompatible

推测错误由表的处理顺序导致（子表先修改列排序规则，父表未同步修改，引发外键约束不兼容），寻求除手动处理报错表外的最优解决方法。

方法1：按「父表优先」顺序生成ALTER语句 #

核心逻辑是先修改被外键引用的父表（包含主键/唯一键被其他表引用的表），再修改引用它的子表，确保子表修改时父表列的排序规则已同步，避免外键兼容性错误。

修改后的生成脚本：

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME,'` ',
              DATA_TYPE, IF(CHARACTER_MAXIMUM_LENGTH IS NULL
       OR DATA_TYPE LIKE 'longtext', '', CONCAT('(', CHARACTER_MAXIMUM_LENGTH,
                                         ')')
       ), ' COLLATE utf8mb4_0900_ai_ci;') AS alter_statement
FROM   INFORMATION_SCHEMA.COLUMNS
LEFT JOIN (
    SELECT DISTINCT REFERENCED_TABLE_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = 'my_schema' AND REFERENCED_TABLE_NAME IS NOT NULL
) AS parent_tables ON COLUMNS.TABLE_NAME = parent_tables.REFERENCED_TABLE_NAME
WHERE  TABLE_SCHEMA = 'my_schema'
       AND (SELECT TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA AND TABLE_NAME = COLUMNS.TABLE_NAME
            LIMIT 1) = 'BASE TABLE'
       AND DATA_TYPE IN ( 'char', 'varchar' )
ORDER BY 
    -- 优先处理父表，再处理子表
    CASE WHEN parent_tables.REFERENCED_TABLE_NAME IS NOT NULL THEN 0 ELSE 1 END,
    TABLE_NAME, COLUMN_NAME;

方法2：临时禁用外键检查（高效但需注意风险） #

若执行期间可接受暂时关闭外键约束，可通过临时禁用外键检查来批量执行所有ALTER语句，完成后恢复约束。

操作步骤：

1. 禁用外键检查：

SET FOREIGN_KEY_CHECKS = 0;

2. 执行所有生成的ALTER语句
3. 恢复外键检查：

SET FOREIGN_KEY_CHECKS = 1;

注意：执行期间若有业务写入，可能导致数据违反外键约束，建议在业务低峰期操作，且执行前做好数据备份。

方法3：生成「删除外键-修改列-重建外键」的完整脚本（最严谨） #

该方法先删除子表的外键约束，修改父表和子表的列排序规则，最后重建外键约束，彻底规避兼容性问题，适合对数据一致性要求高的场景。

步骤1：生成删除外键的语句 #

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` DROP FOREIGN KEY `', CONSTRAINT_NAME, '`;') AS drop_fk_statement
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'my_schema'
      AND REFERENCED_TABLE_NAME IS NOT NULL;

步骤2：生成修改列排序规则的ALTER语句 #

可使用原脚本或方法1中按父表优先排序后的脚本。

步骤3：生成重建外键的语句 #

SELECT CONCAT('ALTER TABLE `', kcu.TABLE_NAME, '` ADD CONSTRAINT `', kcu.CONSTRAINT_NAME, '` FOREIGN KEY (`', kcu.COLUMN_NAME, '`) REFERENCES `', kcu.REFERENCED_TABLE_NAME, '` (`', kcu.REFERENCED_COLUMN_NAME, '`);') AS add_fk_statement
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE kcu.TABLE_SCHEMA = 'my_schema'
      AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.TABLE_NAME;

执行顺序：先运行所有删除外键的语句，再执行修改列的语句，最后运行重建外键的语句。

内容的提问来源于stack exchange，提问作者lm.