修改MySQL表字符集/排序规则未同步至列的问题咨询

一、为什么表级修改不影响现有列？ #

MySQL中，表级的字符集和排序规则仅作为新列的默认配置，不会自动修改已存在列的字符集和排序规则。你执行的ALTER TABLE语句只是更新了表的元数据默认值，对已创建的VARCHAR等字符串列完全没有影响，这就是show full columns结果里列仍显示latin1_swedish_ci的原因。

二、表级与列级字符集/排序规则的区别与作用 #

• 表级设置：仅作用于后续新增的字符串类型列（如VARCHAR、TEXT），当你添加新列时如果不指定字符集/排序规则，就会继承表级的配置。
• 列级设置：是每个列实际生效的字符集和排序规则，优先级高于表级设置。已存在的列会一直沿用自身的列级配置，这也是你出现“非法排序规则混合错误”的根源——表和列的排序规则不一致，在关联查询、排序等操作中会触发冲突。

修改表级排序规则的意义：

• 统一后续新增列的字符集规范，避免每次加列都手动指定；
• 让表的元数据与业务的Unicode需求对齐，减少新列出现字符集不一致的概率。

三、批量修改现有列的解决方案 #

要把现有字符串列的字符集和排序规则改成utf8mb4和utf8mb4_0900_ai_ci，需要明确指定修改列，以下两种方案适配不同场景：

方案1：直接修改指定列（适用于少量列） #

列数量不多时，直接在ALTER TABLE中列出需要修改的列，注意保留列原有的类型、长度、nullable属性和默认值：

ALTER TABLE schema.tablename
  MODIFY COLUMN col1 VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  MODIFY COLUMN col2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  ...;

方案2：批量生成修改语句（适用于大量列） #

列数量较多时，用SQL自动生成修改语句，再批量执行：

SELECT CONCAT(
  'ALTER TABLE schema.tablename MODIFY COLUMN ',
  COLUMN_NAME, ' ',
  DATA_TYPE, IF(CHARACTER_MAXIMUM_LENGTH IS NOT NULL, CONCAT('(', CHARACTER_MAXIMUM_LENGTH, ')'), ''), ' ',
  'CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci',
  IF(IS_NULLABLE = 'YES', ' NULL', ' NOT NULL'),
  IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT)), ''),
  ';'
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'schema'
  AND TABLE_NAME = 'tablename'
  AND DATA_TYPE IN ('varchar', 'text', 'mediumtext', 'longtext');

执行这条语句后，会生成每个目标列的ALTER TABLE MODIFY COLUMN语句，复制这些语句执行即可完成批量修改。

生产环境操作注意事项 #

1. 操作前必须备份表数据，避免数据丢失；
2. 修改列字符集会锁表，建议在业务低峰期执行；
3. 若列中存在latin1编码的特殊字符，先在测试环境验证转码是否无损；
4. 完成列修改后，再执行一次表级修改语句确保默认配置正确：

ALTER TABLE schema.tablename CHARACTER SET = utf8mb4 , COLLATE = utf8mb4_0900_ai_ci ;

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