MySQL utf8mb4 排序规则迁移的字段检查

把 MySQL 表从旧的 utf8mb4 排序规则迁到 utf8mb4_0900_ai_ci，最危险的做法是先写 ALTER TABLE，再等报错告诉你遗漏了什么。排序规则不只影响“排序”，它还定义字符串何时相等。大小写、重音符号和尾部空格的比较结果一变，唯一键、外键、分页顺序和应用层去重都可能跟着变化。

更稳妥的思路是把迁移当成一次接口契约变更：数据库字段是输入，索引和约束是调用方，比较规则是接口语义。先把依赖列清楚，再决定哪些表能一起改、哪些数据必须先治理。

先定义迁移契约：目标不只是改字符集

utf8mb4 是字符集，决定字符如何编码；utf8mb4_0900_ai_ci 是排序规则，决定字符如何比较和排序。在 MySQL 8.4 中，它是 utf8mb4 的默认排序规则，基于 Unicode Collation Algorithm 9.0；名称里的 ai 表示不区分重音，ci 表示不区分大小写。

UCA 9.0 及之后的 utf8mb4 排序规则通常使用 NO PAD，而 utf8mb4_unicode_ci、utf8mb4_general_ci 等旧规则常见的是 PAD SPACE。不能只看规则名猜测，应直接查询元数据：

-- 核对候选排序规则是否可用，以及尾部空格采用 PAD SPACE 还是 NO PAD。
SELECT COLLATION_NAME, CHARACTER_SET_NAME, IS_DEFAULT, PAD_ATTRIBUTE
FROM INFORMATION_SCHEMA.COLLATIONS
WHERE COLLATION_NAME IN (
  'utf8mb4_unicode_ci',
  'utf8mb4_0900_ai_ci',
  'utf8mb4_0900_as_cs'
);

这里要先由业务方确认三件事：账号、邮箱、编码等标识是否区分大小写；自然语言内容是否区分重音；尾部空格是否应当参与相等判断。只有答案明确，目标排序规则才算选定。

调用方真正依赖的是相等、排序和唯一性

数据库外部的调用方很少直接关心 collation 名称，但会依赖它产生的结果。例如登录查询依赖字符串相等，排行榜和字典页依赖排序，游标分页依赖稳定顺序，注册接口依赖唯一索引。若迁移后两个历史值被判定为相等，应用层即使一行代码没改，也可能出现注册失败、更新冲突或分页抖动。

连接层执行 SET NAMES utf8mb4 只能调整客户端、连接和返回结果使用的字符集，不能替代字段排序规则迁移。字段定义、索引和已存数据仍然要按表结构变更处理。

第一张清单：哪些字段会被改

不要从表默认值开始猜。字段可以显式指定与表默认值不同的排序规则，历史建表脚本也可能让同一张表混用多个规则。先从 INFORMATION_SCHEMA.COLUMNS 导出真实清单：

-- 盘点目标库全部字符字段，记录字符集、排序规则、类型和键属性。
SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE,
       CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_KEY,
       GENERATION_EXPRESSION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'app_db'
  AND CHARACTER_SET_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION;

输出结果至少要补上四类标记：是否显式指定排序规则、是否参与索引、是否为生成列、是否被应用当作标识符。CHARACTER_SET_NAME IS NOT NULL 会把数值、日期和二进制字段排除，便于把注意力集中在真正受比较规则影响的列上。

图1：结构示意图，展示数据库默认值、表默认值、字符字段、显式字段规则、生成列与应用比较契约之间的关系。

如果准备使用整表转换语句，所有字符列都会被纳入转换，不只是你最关注的那个字段。因此清单必须覆盖 CHAR、VARCHAR、TEXT 及其变体，并单独标记存储大文本的列，因为它们通常决定复制流量、临时空间和变更时长。

第二张清单：索引和唯一键会不会撞值

字符列参与普通索引时，迁移意味着索引键值需要按新规则重建；参与唯一索引时，还要先回答“旧规则下不同的值，在新规则下是否会相等”。先列出字符字段上的索引：

-- 找出字符字段参与的索引及前缀长度，评估重建成本和唯一性语义。
SELECT s.TABLE_NAME, s.INDEX_NAME, s.NON_UNIQUE, s.SEQ_IN_INDEX,
       s.COLUMN_NAME, s.SUB_PART, c.COLUMN_TYPE, c.COLLATION_NAME
FROM INFORMATION_SCHEMA.STATISTICS AS s
JOIN INFORMATION_SCHEMA.COLUMNS AS c
  ON c.TABLE_SCHEMA = s.TABLE_SCHEMA
 AND c.TABLE_NAME = s.TABLE_NAME
 AND c.COLUMN_NAME = s.COLUMN_NAME
WHERE s.TABLE_SCHEMA = 'app_db'
  AND c.CHARACTER_SET_NAME IS NOT NULL
ORDER BY s.TABLE_NAME, s.INDEX_NAME, s.SEQ_IN_INDEX;

SUB_PART 不为空表示前缀索引。对字符列来说，前缀长度按字符计，但 utf8mb4 每字符可能占用更多字节，仍应结合 MySQL 版本、存储引擎和完整联合索引估算索引空间。

对每个唯一键，应在变更前用目标规则模拟分组。下面以邮箱为例，显式转换为 utf8mb4 后再套用目标排序规则：

-- 用目标排序规则模拟相等性，提前找出可能撞上唯一索引的值。
SELECT CONVERT(email USING utf8mb4)
         COLLATE utf8mb4_0900_ai_ci AS normalized_email,
       COUNT(*) AS row_count
FROM app_db.users
GROUP BY normalized_email
HAVING COUNT(*) > 1;

这条查询用于发现候选冲突，不应直接据此删除数据。还要把原始主键和原始值导出，由业务确认应该合并、改名还是拒绝迁移。联合唯一键则必须按完整列组合分组，不能只检查其中一个字段。

图2：关系示意图，展示目标排序规则与唯一索引、前缀索引、重复候选、PAD 属性及外键两端的约束关系。

外键字段必须作为一组迁移

非二进制字符串外键的父列与子列需要具有匹配的字符集和排序规则。只改父表或只改子表，可能导致外键重建失败，常见表现是 Error 1005 或 errno 150。先把外键两端与各自的排序规则放在同一份清单里：

-- 列出字符型外键两端的排序规则，父子列必须成组规划迁移。
SELECT k.TABLE_NAME AS child_table,
       k.COLUMN_NAME AS child_column,
       cc.COLLATION_NAME AS child_collation,
       k.REFERENCED_TABLE_NAME AS parent_table,
       k.REFERENCED_COLUMN_NAME AS parent_column,
       pc.COLLATION_NAME AS parent_collation
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
JOIN INFORMATION_SCHEMA.COLUMNS AS cc
  ON cc.TABLE_SCHEMA = k.CONSTRAINT_SCHEMA
 AND cc.TABLE_NAME = k.TABLE_NAME
 AND cc.COLUMN_NAME = k.COLUMN_NAME
JOIN INFORMATION_SCHEMA.COLUMNS AS pc
  ON pc.TABLE_SCHEMA = k.UNIQUE_CONSTRAINT_SCHEMA
 AND pc.TABLE_NAME = k.REFERENCED_TABLE_NAME
 AND pc.COLUMN_NAME = k.REFERENCED_COLUMN_NAME
WHERE k.CONSTRAINT_SCHEMA = 'app_db'
  AND k.REFERENCED_TABLE_NAME IS NOT NULL
  AND cc.CHARACTER_SET_NAME IS NOT NULL
ORDER BY child_table, k.CONSTRAINT_NAME;

迁移计划应以“约束组”为单位，而不是按单表随意排序。若需要临时删除并重建外键，要把约束名称、删除语句、恢复语句和数据一致性检查一起写进变更单，不能只保留最终的 ALTER TABLE。

生成列、默认值和长度也要保留

生成列表达式可能包含 LOWER()、拼接或比较，它们的输出与索引都可能受到新规则影响；基于生成列建立的索引需要一起验证。手工使用 MODIFY COLUMN 时，还必须重述 NOT NULL、显式 DEFAULT、注释和生成表达式等属性，否则遗漏的属性可能被重置。

还要确认旧数据确实按字段声明的源字符集编码。若历史系统把一种编码的字节错误地声明成另一种编码，直接转换可能产生替换字符或不可逆损失。这类问题应先抽样解码并修正源数据，而不是寄希望于排序规则迁移自动修复乱码。

DDL 失败与回滚边界

整表转换可以使用下面的语句，但它应当是检查通过后的执行动作，而不是迁移的起点：

-- 在备份、重复值和外键检查通过后，于维护窗口执行整表转换。
ALTER TABLE app_db.users
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

字符集或排序规则转换通常涉及表重建和索引重建。不要在未验证目标版本、表定义和存储引擎之前承诺“在线、无锁”。预发环境至少要记录执行时长、额外磁盘空间、主从延迟、元数据锁等待和失败后的恢复时间。

回滚也不是简单把 collation 名称改回去。只要上线期间已经写入了在新规则下合法、在旧规则下语义不同的数据，反向转换就可能再次撞上唯一键或改变比较结果。可靠的回滚边界应包含：变更前备份或快照、写入冻结策略、应用版本回退点，以及恢复后重新校验约束的方法。

一份可执行的迁移顺序

1. 记录当前数据库、表和字段级字符集与排序规则，不用建表脚本代替线上元数据。
2. 确认目标规则的大小写、重音和 PAD_ATTRIBUTE，让业务方签字确认比较语义。
3. 枚举字符字段上的普通索引、唯一索引、前缀索引、生成列索引和字符型外键。
4. 按目标规则执行重复候选查询，逐组处理唯一键冲突，不自动删除。
5. 按外键依赖把父子表归为同一迁移单元，并准备约束重建脚本。
6. 在预发副本执行真实 DDL，测量重建空间、耗时、锁等待和复制影响。
7. 正式窗口前完成备份、监控、停止条件和回滚点确认，再执行转换。
8. 迁移后复查字段定义、约束、关键查询排序和应用层去重结果。

这套检查的核心不是把所有表都改成同一个名称，而是确保每个依赖字符串比较的调用方都得到预期结果。只要字段清单、比较契约、约束组和回滚边界四项都能被明确回答，utf8mb4 排序规则迁移就从一次冒险 DDL，变成了一次可评审的数据库变更。