注册接口拒绝了一个手机号，理由是"已被占用"——而数据库里查出来的那条记录，手机号是带空格的。原因是表用了 utf8mb4_general_ci 排序规则，而 ci 表示"不区分大小写"，同时它把尾随空格视为相等，于是 "13800138000 " 与 "13800138000" 撞了唯一索引。
排序规则（collation）看起来是建表时的一个选项，实际它决定了字符串如何比较、如何排序、以及什么算唯一。这个代价有个具体形态：改排序规则会重建整张表与该列上所有索引，而唯一的"无损"方式是新建列、回填、切换——于是它变成一个项目，而不是一次 DDL。
而它一旦选定，改动的代价远高于"改一个字段"。
1. 排序规则决定三件事
| 决定 | 影响 |
|---|---|
| 比较是否区分大小写 | 唯一约束、查询匹配 |
| 是否忽略尾随空格 | 唯一约束、等值查询 |
| 字符的排序权重 | ORDER BY 结果、索引顺序 |
第二项最容易被忽略：很多 _ci 规则的 PAD SPACE 语义把尾随空格视为无意义，于是"看起来不同"的两个值在索引层面相同。
第三项影响索引复用：不同排序规则的列无法共用索引，联表时可能退化为全扫。第二项还有个别名：PAD SPACE 与 NO PAD。MySQL 8 的部分规则改为 NO PAD，于是"带空格与不带空格"不再相等——所以同一段代码在升级后行为可能变化。
2. 常见规则的含义
| 规则 | 含义 |
|---|---|
utf8mb4_general_ci |
不区分大小写，较简单的权重 |
utf8mb4_unicode_ci |
不区分大小写，更符合 Unicode 排序 |
utf8mb4_0900_ai_ci |
MySQL 8 默认，不区分大小写与重音 |
utf8mb4_bin |
按字节比较，完全区分 |
utf8mb4_general_cs |
区分大小写 |
第四项最"严格"：它是唯一能保证"字符串完全相同才相等"的规则，适合做唯一标识（如 token、哈希、邮箱的规范化后形式）。
第五项（_cs）在 MySQL 里并不常见，实际项目更多用 _bin 来表达"严格比较"。所以"区分大小写"这件事，实践上等于"用 _bin"。
第三项的 ai 表示"忽略重音"：café 与 cafe 被视为相等。这对多语言输入是便利，对唯一标识是风险。
-- 查看列的实际排序规则（排查的第一步）
SELECT column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema='shop' AND table_name='users';
-- 典型意外：业务以为区分大小写，而列是 _ci
3. 唯一索引 + _ci 的三种后果
| 后果 | 表现 |
|---|---|
| 大小写不同的值撞唯一 | 用户注册被拒 |
| 尾随空格撞唯一 | 无法插入"看起来不同"的值 |
| 重音不同的值撞唯一 | 国际化场景冲突 |
第一项在"邮箱大小写"上最常见：[email protected] 与 [email protected] 被当成同一个。有些业务希望这样（邮箱通常不区分大小写），有些则不希望（用户名）。
第三项在多语言产品里麻烦："José" 与 "Jose" 撞唯一，而用户认为这是两个人。
-- 决策：唯一标识到底是"字节相同"还是"语义相同"
-- 方案一：唯一标识用 _bin（严格）
ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_bin;
-- 方案二：唯一标识用"规范化后的值"（更稳）
-- 存 email_normalized（小写+去空格），唯一索引加在它上面
第二项的额外好处是"可审计"：规范化规则可以写成代码、写进测试，而排序规则是数据库配置，代码里看不见。所以前者的行为更容易被人理解。
第二项通常更稳：把"规范化"显式化成数据处理步骤，索引只在规范化值上做严格比较。这样"业务语义"与"字节比较"分开，改动可控。
"规范化"这一步有三个常被漏掉的规则，值得写成代码而不是靠记忆：
// 规范化：三类字段，三套规则（显式、可测）
def norm_email(s: str) -> str:
return s.strip().lower() # 邮箱：去空格 + 小写
def norm_username(s: str) -> str:
return s.strip() # 用户名：只去空格，保留大小写
def norm_phone(s: str) -> str:
return "".join(ch for ch in s if ch.isdigit()) # 手机号：只留数字
// 关键：规范化函数有测试；索引建在规范化列上，排序规则用 _bin
4. 排序规则与索引
| 场景 | 问题 |
|---|---|
| 联表两列规则不同 | 索引无法使用 |
| 查询用字面量 | 隐式转换，索引失效 |
| 规则变更 | 需重建索引 |
第二项很隐蔽：WHERE name = 'X' 中字面量的规则由连接或列决定，两侧不一致时会做转换，从而无法用索引。
第三项的成本：改建排序规则等于重建整张表的索引，大表上是数小时的 DDL。第一项（联表两列规则不同）的表现是"同一个查询，单独跑很快，联表就慢"。因为联表时的比较无法用索引，优化器只能选全扫加哈希。
-- 检查是否因排序规则导致索引失效
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- 关注 key 是否为 NULL；若 NULL 且列有索引 → 可能是规则不一致
SHOW WARNINGS; -- 有时会提示"排序规则混用，无法使用索引"
还有一个"连接层"的坑：连接串里可以设 collation / characterEncoding，而它会成为字面量的比较规则。于是"同一段 SQL 在不同连接上结果不同"，而代码完全一致。
// 连接层影响比较的三个参数（MySQL 示例）
characterEncoding=utf8mb4 → 字符集
connectionCollation=utf8mb4_bin → 字面量的比较规则（关键）
-- 若不设，继承服务端默认，可能与列不一致
// 症状：应用查不到，客户端手工查得到
5. 跨库与跨系统的坑
| 场景 | 坑 |
|---|---|
| 应用与数据库规则不同 | 应用层判重与库不一致 |
| 分库后规则不同 | 同一查询不同结果 |
| 数据同步到 ES | 分词与比较语义不同 |
| 导出到文件再导入 | 默认规则覆盖 |
第三项（同步到 ES）的差异不只是分词：ES 的 keyword 字段默认区分大小写，而 MySQL 的 _ci 不区分，于是"库里有 1 条、索引里 2 条"，而两边各自都"正确"。
第一项最典型：Java 的 equals 区分大小写，而数据库不区分，于是"应用认为不重复、数据库报重复"。这类错误只在真实数据出现大小写混用时暴露。
第三项在搜索场景里常见：数据库按 _ci 判重，ES 按分词后匹配，两侧的"相同"定义不同。
排查这类问题的第一个动作应当固定下来：把两个值都 HEX() 出来对比。因为"看起来一样"经常不一样——尾随空格、零宽字符、全角半角、不同 Unicode 归一化形式，全都在肉眼上不可见。
-- 两个"看起来相同"的值，字节是否真的相同
SELECT HEX('13800138000 '), HEX('13800138000');
-- 前者多一个 20（空格），这就是唯一冲突的原因
-- 找出表里带尾随空格的记录
SELECT id, HEX(email) FROM users WHERE email LIKE '% ';
6. 排错：五种"字符串比较不符预期"
| 现象 | 成因 | 处理方向 |
|---|---|---|
| 大小写不同判重复 | _ci 规则 |
改 _bin 或规范化 |
| 带空格判重复 | PAD SPACE |
规范化去空格 |
| 重音不同判重复 | ai 规则 |
改 _bin 或保留重音 |
| 查询用不上索引 | 规则混用 | 统一规则 |
| 应用与库判断不一致 | 双侧语义不同 | 显式规范化 |
第五项是"系统性"问题：两套判重逻辑必然不一致第四项（规则混用）还有一个"跨库"版本：分库后不同分片用了不同规则（因为建表时间不同），于是同一查询在不同分片返回不同结果——这类问题只有在数据量大到跨分片时才出现。
，因为"字符串相等"在两个系统里定义不同。解法是"只保留一套判重"（通常在数据库），应用层不做预判。
7. 边界：什么情况不必关心排序规则
| 场景 | 为什么 | 做法 |
|---|---|---|
| 纯数字/ID 列 | 无字符串比较 | 不必 |
| 内部临时表 | 生命周期短 | 不必 |
| 仅用于展示的文本 | 不参与比较 | 不必 |
| 已统一规范化 | 比较语义明确 | 不必 |
最后一条与"迁移"有关的边界：跨数据库迁移时排序规则几乎必然要重定（MySQL 与 PostgreSQL 的默认不同）。所以"迁移后唯一约束失效/变严"是常见事故，应当在迁移前专门核对每个唯一索引的语义。
判断口径一句话：这个字段会不会参与"相等判断"或"唯一约束"。会，排序规则就是业务规则的一部分；只用于展示，它只是存储细节。
8. 自测
- 排序规则决定的三件事分别是什么？为什么"尾随空格"会导致唯一冲突？
utf8mb4_bin与utf8mb4_0900_ai_ci的核心差别是什么？各自适合什么字段？- 为什么"存规范化后的值 + 唯一索引"比"改排序规则"更稳？
- 排序规则混用如何导致索引失效？怎么验证？
- 为什么"应用层判重 + 数据库判重"必然不一致？解法是什么？
- 判断"是否需要关心排序规则"的那个问题是什么？