MySQL 8.0.2修改含外键列的字符集，如何级联更新下游表？

核心逻辑 #

MySQL没有直接支持级联修改外键关联列字符集的语法，但可以通过先修改所有下游关联列，再修改主表列的方式解决。关键是先找出所有依赖主表列的关联表（包括多层嵌套的），再批量修改它们的字符集，最后修改主表。

步骤1：查询所有依赖test.my_code的关联列（含多层依赖） #

用MySQL 8.0支持的递归CTE，一次性找出所有直接/间接关联的表和列：

WITH RECURSIVE fk_deps AS (
    SELECT 
        TABLE_NAME AS child_table,
        COLUMN_NAME AS child_column
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE 
        REFERENCED_TABLE_NAME = 'test' 
        AND REFERENCED_COLUMN_NAME = 'my_code'
        AND TABLE_SCHEMA = '你的数据库名' -- 替换为实际数据库名
    UNION ALL
    SELECT 
        k.TABLE_NAME,
        k.COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
    JOIN fk_deps fd ON k.REFERENCED_TABLE_NAME = fd.child_table
    WHERE 
        k.TABLE_SCHEMA = '你的数据库名' -- 替换为实际数据库名
)
SELECT DISTINCT child_table, child_column FROM fk_deps;

步骤2：生成并执行批量修改下游列的语句 #

基于上面的查询结果，自动生成修改字符集的SQL脚本，避免手动逐个编写：

WITH RECURSIVE fk_deps AS (
    SELECT 
        TABLE_NAME AS child_table,
        COLUMN_NAME AS child_column
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE 
        REFERENCED_TABLE_NAME = 'test' 
        AND REFERENCED_COLUMN_NAME = 'my_code'
        AND TABLE_SCHEMA = '你的数据库名' -- 替换为实际数据库名
    UNION ALL
    SELECT 
        k.TABLE_NAME,
        k.COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
    JOIN fk_deps fd ON k.REFERENCED_TABLE_NAME = fd.child_table
    WHERE 
        k.TABLE_SCHEMA = '你的数据库名' -- 替换为实际数据库名
)
SELECT DISTINCT CONCAT(
    'ALTER TABLE ', child_table, ' MODIFY ', child_column, ' VARCHAR(64) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL;'
) AS alter_stmt FROM fk_deps;

执行这个语句后，复制输出的所有ALTER TABLE语句并执行，把所有下游关联列的字符集改成ascii。

步骤3：修改主表test的列字符集 #

当下游所有关联列的字符集和主表要修改的目标一致后，就可以安全执行主表的修改语句：

ALTER TABLE test MODIFY my_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL UNIQUE;

关键注意事项 #

• 数据兼容性检查：修改前务必确认所有关联列的数据没有非ASCII字符，否则修改会失败。可以用以下语句检查：

  -- 检查主表
  SELECT my_code FROM test WHERE NOT my_code REGEXP '^[\\x00-\\x7F]+$';
  -- 检查下游表（替换为实际表名和列名）
  SELECT 关联列名 FROM 关联表名 WHERE NOT 关联列名 REGEXP '^[\\x00-\\x7F]+$';
  

• 备份数据：执行所有修改操作前，建议备份相关表或整个数据库，避免数据丢失。

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