做后端开发这些年MySQL 建表语句写了无数遍字符集基本是无脑utf8mb4排序规则嘛……说实话大部分时候我也是直接跟着默认走。直到上周隔壁组的同事突然跑来问我“MySQL 排序规则到底用哪个general_ci和unicode_ci有啥区别为什么我两张表 JOIN 老报Illegal mix of collations”我愣了几秒发现自己能说出口的只有“呃……一般都是utf8mb4_general_ci吧”至于为什么、区别在哪、会不会影响查询结果我答得含含糊糊。那天晚上回去我老老实实把 MySQL 的字符集和排序规则从头捋了一遍越捋越觉得这玩意儿看似不起眼实则坑很深。这篇文章就把我梳理的结果写下来排序规则到底是什么、几个常用规则怎么选、选错了会出什么幺蛾子、老库怎么迁。如果你也被同事问过类似问题或者正在为 JOIN 报错抓瞎这篇应该能帮上忙。1. 先分清楚两件事字符集管“存”排序规则管“比”1.1 字符集和排序规则不是一回事很多人把utf8mb4和utf8mb4_0900_ai_ci混着说其实这是两个完全不同的概念。字符集Character Set负责的是“编码”——把抽象的字符映射成二进制字节。比如utf8mb4用 1 到 4 个字节表示一个字符所以它能存下 emoji、生僻字而老的utf8实际是utf8mb3最多只有 3 个字节遇到 emoji 就会直接报Incorrect string value错误。这块大多数人已经比较熟了。排序规则Collation负责的是“比较”——在同一个字符集内部决定两个字符串谁大谁小、是否相等、怎么排序。同样是a和A在_ci规则下相等在_bin规则下不相等。这就是排序规则在做的事。MySQL 里每个字符集都会配套一个或多个排序规则用这条 SQL 可以看全貌SHOW COLLATION LIKE utf8mb4%;输出会列出一大堆utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_0900_ai_ci、utf8mb4_bin……第一次看的人很容易懵感觉像同一件衣服的十几个配色。它们确实都服务于同一个字符集只是“比较规则”不同适用的场景也不同。1.2 排序规则的命名规律后缀才是关键搞清楚命名规律你就不用死记硬背了。MySQL 排序规则的命名格式一般是字符集_语言/版本_后缀拿utf8mb4_0900_ai_ci举例utf8mb4是字符集0900表示基于 Unicode 9.0 的排列算法UCA 9.0.0后面两个后缀才是重点_aiaccent insensitive口音不敏感也就是é和e会被当成同一个字符_cicase insensitive大小写不敏感A和a会被当成同一个字符反过来还有_asaccent sensitive口音敏感和_cscase sensitive大小写敏感_binbinary直接按二进制字节比最严格所以看到名字你就能猜出行为utf8mb4_0900_as_cs就是“口音敏感 大小写敏感”utf8mb4_bin就是“按字节硬比”。MySQL 里后缀_bin不算在“敏感”体系里它是最底层的二进制比较连字符集层面的“语言习惯”都不管。提示utf8mb4_unicode_ci和utf8mb4_0900_ai_ci虽然都是_ci但前者基于 Unicode 4.0 算法后者基于 9.0两者在特殊字符的排序和比较上并不完全一致。2. 三个最常用的 utf8mb4 排序规则差异到底有多大2.1 general_ci老版本的默认选择utf8mb4_general_ci是 MySQL 5.7 及之前版本的默认排序规则。它的设计目标是“快”——实现了一套简化的比较逻辑没有完整实现 Unicode 排序算法。它快到什么程度在早期版本里general_ci比unicode_ci快不少因为前者很多字符直接按字节大小比省去了查 Unicode 排序表的开销。但在 MySQL 8.0 里这个性能差距已经被大幅缩小0900_ai_ci甚至在某些场景下更快所以“为了性能选 general_ci”这个理由在 8.0 上基本不成立了。general_ci的问题在于“粗”。它对扩展拉丁字符、特殊符号的归类不够细致比如某些西文字符的比较结果和 Unicode 标准不一致。对中文业务来说日常大部分场景是等值查询和普通排序它表现还行但一旦涉及多语言数据或者要求符合 Unicode 标准的排序就露馅了。2.2 unicode_ci更“讲究”但存在感尴尬utf8mb4_unicode_ci基于真正的 Unicode Collation AlgorithmUCA 4.0比较规则更符合语言习惯对多种语言文字的排序更准确。在老版本里它是“比 general_ci 准、比 general_ci 慢”的折中选择。但它的存在感现在很尴尬MySQL 8.0 默认的是更先进的0900_ai_ci基于 UCA 9.0功能上全面覆盖了unicode_ci的优点还修正了不少排序问题。如果你还在用 5.7且需要更准确的 Unicode 排序unicode_ci是个合理选择如果你已经升到 8.0就没必要再用它了。2.3 0900_ai_ciMySQL 8.0 的新默认utf8mb4_0900_ai_ci是 MySQL 8.0 的默认排序规则基于 Unicode 9.0 的 UCA 算法_ai_ci意味着口音不敏感 大小写不敏感。它和unicode_ci最直观的区别我用一条 SQL 演示过SELECT é e COLLATE utf8mb4_0900_ai_ci AS r1, é e COLLATE utf8mb4_unicode_ci AS r2;结果是r1 1r2 0。在0900_ai_ci下é和e被视为同一个字符只要你的业务不要求区分这两个字符这通常不是问题反而更像人类语言的自然习惯。但如果你做的是一个对字符精度要求极高的系统比如代码标识符校验这个行为就要特别小心了。三个规则的核心差异我整理成了表格排序规则基础版本大小写口音排序准确性适用版本utf8mb4_general_ci简化算法不敏感不敏感较低5.7 默认utf8mb4_unicode_ciUnicode 4.0不敏感不敏感较高5.7 可选utf8mb4_0900_ai_ciUnicode 9.0不敏感不敏感高8.0 默认注意0900_ai_ci的“口音不敏感”是对业务影响最隐蔽的一点。如果你从 5.7 的general_ci迁到 8.0 默认等于数据比较规则变了某些原来查不到的记录突然能查到了千万别忽略这个行为差异。3. 排序规则不是小事它如何悄悄影响查询结果和索引3.1 等值查询的“大小写不敏感”陷阱排序规则最直接的影响就是WHERE条件里的等值判断。在_ci规则下SELECT * FROM user WHERE username Admin;这条语句能查到username admin的记录因为_ci把大小写视为相等。对登录场景来说这往往不是你想要的行为——用户注册了Admin和admin两个账号登录时随便输哪个都可能串号。这就是为什么不少系统的用户名字段会单独指定utf8mb4_bin或者utf8mb4_0900_as_cs让比较严格区分大小写。注意utf8mb4_bin是“按二进制字节比较”它不仅能区分大小写还能区分所有等同字符是最严格的方案utf8mb4_0900_as_cs是“口音敏感 大小写敏感”更符合语言习惯。具体选哪个取决于你的业务到底是“要精确”还是“要严格区分”。有个反直觉的点_ci规则下WHERE name a用到了索引但如果你在查询里显式写了WHERE name a COLLATE utf8mb4_bin这个索引通常就用不上了。因为索引是按列本身的排序规则构建的查询里临时改成另一个 collationMySQL 无法直接利用原有索引顺序。这个坑很隐蔽容易在功能正常时埋下性能隐患。3.2 ORDER BY 的排序结果可能完全不符合预期排序规则对ORDER BY的影响很多人都是踩了坑才发现。我见过一个真实案例某系统按用户名排序分页展示用的utf8mb4_general_ci结果字母大小写混在一起时排序“看起来没问题”但中文用户的排序顺序就完全不可控。原因在于中文在utf8mb4下是按 Unicode 码点排序的并不是按拼音。utf8mb4_general_ci和utf8mb4_0900_ai_ci都不解决中文拼音排序的问题。如果你需要按拼音排通常得在应用层转拼音或者使用专门的字段存储拼音索引指望排序规则是做不到的。如果你要的是“稳定的字节序”可以直接在查询里指定SELECT name FROM user ORDER BY name COLLATE utf8mb4_bin;但同样要注意一旦在ORDER BY里临时指定 collation索引排序优化基本就失效了数据量大时性能会肉眼可见地下降。3.3 JOIN 报错不是玄学Illegal mix of collations 的根因同事问我的另一个问题就是这个报错ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 这个报错的本质是JOIN 的两边字段字符集相同但排序规则不同MySQL 不知道按谁的规则来比较只能罢工。最常见的产生原因有三个建表时没显式指定 collationA 表建在 MySQL 5.7B 表建在 MySQL 8.0默认值不一样表级别统一了但列级别单独指定过比如某列建表时写了CHARACTER SET utf8mb4 COLLATE utf8mb4_bin两库字符集相同但排序规则默认不同跨库 JOIN 时最容易踩解决思路有几种。最省事的临时方案是在 JOIN 条件里强制指定一边SELECT * FROM a JOIN b ON a.name b.name COLLATE utf8mb4_0900_ai_ci;但这只是治标COLLATE写在查询里会影响索引使用。治本的办法是统一表的 collation让两边完全一致。3.4 前缀索引长度限制被排序规则间接绑架排序规则还会间接影响你能建多长的索引。InnoDB 在 5.7 的某些行格式下索引前缀限制是 767 字节utf8mb4一个字符最多 4 字节所以767 / 4 ≈ 191这就是为什么老 MySQL 教程里总说“varchar 要用 191”。到了 8.0InnoDB 默认行格式是 DYNAMIC索引前缀限制放宽到 3072 字节3072 / 4 768所以 8.0 里 varchar 可以建到 768 个字符的索引。这里有个容易忽略的点utf8mb4字符集的排序规则不同占用的字节数是一样的所以索引长度限制主要看字符集而不是排序规则。但如果你在建索引时遇到Specified key was too long先确认表的行格式和 innodb 参数再考虑缩短前缀别一上来就改排序规则方向容易错。4. 新项目和老库到底怎么选我的实操方案4.1 新项目直接默认特殊情况再覆盖如果你从零开始一个新项目并且用的是 MySQL 8.0我的建议很简单不要折腾就用默认的utf8mb4_0900_ai_ci。理由有三个。第一它是 8.0 的官方默认所有工具链、ORM、迁移脚本都围绕它测试过第二它基于 Unicode 9.0比较规则更准确é和e等同这个行为在绝大多数业务里是好事而不是坏事第三团队后续接手的人不容易困惑因为你没引入额外变量。但有两个字段类型需要单独覆盖排序规则用户名、邮箱、邀请码这类需要严格区分的字段建议单独指定utf8mb4_bin或utf8mb4_0900_as_cs唯一键约束的字段注意_ai_ci下e和é被视为相同字符如果业务上这俩必须不同唯一索引会直接拦截这时候必须用_bin建表示例CREATE TABLE user ( id BIGINT PRIMARY KEY, username VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, nickname VARCHAR(64) NOT NULL, UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;这样nickname用默认的宽松比较username用严格比较互不干扰。4.2 老库迁移先摸清现状再统一对齐老库迁移最大的风险不是“改”而是“漏”。有的表是 5.7 时代建的有的是后来人工建列时单独指定的还有的是从别的库导入带过来的。改之前必须先摸清底细。用这几条 SQL 快速盘点-- 查看某个库所有表的默认排序规则 SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db; -- 查看所有字符类型的列及其排序规则 SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db AND DATA_TYPE IN (varchar, char, text, enum, set);盘点完如果你的目标是整体统一到utf8mb4_0900_ai_ci可以用这条语句批量转换ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CONVERT TO会同时改表默认 collation 和所有字符列的 collation比逐列改省事得多。但要注意几点表会重建行数大时务必在低峰期操作期间会有锁改完必须重建索引CONVERT TO对已存在的索引是保留的但索引逻辑是跟着列的 collation 走的列改完之后索引是否需要重算取决于 MySQL 版本和具体操作方式稳妥起见可以OPTIMIZE TABLE或者ANALYZE TABLE看状态先在一张临时表上演练导出建表语句、跑一遍转换、对比数据一致性再上生产提示老库迁移别只改库不改进程。如果应用代码里写了SET NAMES utf8mb4或者 JDBC 连接串里配置了characterEncoding确认连接层面的字符集设置和库表一致否则客户端和服务器各说各话照样出乱码。4.3 特殊业务场景的选型对照我把常见的业务场景和推荐方案整理成了一张表方便你直接对照业务场景推荐方案理由通用业务、内容展示utf8mb4_0900_ai_ci宽松比较默认行为搜索友好用户名、密码提示词、代码标识utf8mb4_bin严格区分大小写和等价字符需要区分大小写但不需二进制严格utf8mb4_0900_as_cs语言级比较区分大小写多语言搜索、国际化排序utf8mb4_0900_ai_ciUnicode 9.0 规则最准确老系统 5.7 保持行为稳定utf8mb4_general_ci不改行为迁移风险最小这里的核心思路是默认宽松敏感字段单独收紧。别一刀切全用_bin也别全用默认。排序规则选错了往往不是立刻报错而是某个边界输入的行为悄悄变了这种 bug 最难查。5. 踩过的坑混用排序规则的血泪教训5.1 坑一列级别排序规则悄悄覆盖了表级别有次我排查一个查询超时问题定位到最后发现某张表的默认 collation 是utf8mb4_0900_ai_ci但业务方为了某个字段“精确匹配”单独给mobile列指定了utf8mb4_bin。应用层用mobile做 JOIN 时和另一张表的utf8mb4_0900_ai_ci字段直接冲突MySQL 每次都在执行期做隐式转换索引全部失效。排查时最容易忽略的就是列级别覆盖。SHOW CREATE TABLE一定要看完整别只盯表级别的DEFAULT COLLATE。很多 ORM 的迁移工具在加列时可以指定 collation时间长了就有“漏网之鱼”。5.2 坑二主从复制环境下的不一致主库是 5.7从库是 8.0或者反过来是迁移过程中最常见的场景。主库表是utf8mb4_general_ci从库表建出来可能变成utf8mb4_0900_ai_ci。如果 DDL 语句里没有显式写 collation复制到从库后默认值就跟着从库版本走了。这时候如果从库承担了分析查询JOIN 行为就和主库不一致查出来的结果集可能不一样。更麻烦的是主从切换后应用层的“预期”全部失效。所以迁移期间DDL 语句里务必显式写出字符集和排序规则别依赖默认值。5.3 坑三改完排序规则唯一索引突然不唯一了utf8mb4_general_ci和utf8mb4_0900_ai_ci对等价字符的判定有差异。比如general_ci下ß和s不一定相等但某些版本下0900_ai_ci对特定字符的组合会更宽松。如果原来general_ci下能插入两条记录迁到0900_ai_ci后插入第二条直接报唯一键冲突这种问题在批量导入数据时偶发。迁移前最好的办法是导出两列数据在新 collation 下跑一遍SELECT COUNT(*) ... GROUP BY看有没有GROUP BY结果比以前少的字段——那就是被“合并”了。5.4 坑四连接层排序规则不一致库表统一了还不够客户端连接也得对齐。MySQL 连接建立后有自己的character_set_connection和collation_connection如果应用连接串里没指定用的就是服务端全局默认值。8.0 全局默认是utf8mb4_0900_ai_ci但如果你通过init_connect设置了别的值或者应用层 ORM 自己发了SET NAMES就会短路掉全局默认。这类问题最常见的表现是用客户端工具查没问题服务端查询却报 collation 冲突。检查一下连接参数和启动脚本里的SET NAMES往往能快速定位。6. 最后分享一个我自己的排查工具踩了几次坑之后我养成了一个习惯所有涉及字符串比较的查询先跑一条检查语句确认当前库、表、列的排序规则确实是我以为的那个。下面这条 SQL 是我最常用的“体检”语句SELECT character_set_database, collation_database; SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA DATABASE(); SELECT COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA DATABASE() AND DATA_TYPE IN (varchar, char, text) AND COLLATION_NAME IS NOT NULL;每次建表、迁移、接到 collation 相关报错时先跑一遍状态就一目了然。排序规则这件事说白了就是“存储的归字符集比较的归排序规则”。新项目跟着默认走敏感字段单独收紧老库迁移前先盘点后演练大多数问题都能避开。回到同事那个问题我现在的回答是“看你 MySQL 版本。8.0 直接用默认的utf8mb4_0900_ai_ci注意用户名字段用_bin5.7 就用utf8mb4_general_ci别乱改如果你在 JOIN 报Illegal mix八成是两边表的 collation 不一致先查information_schema再统一。” 这十几分钟的梳理换来的是一整年不再为排序规则头疼的踏实。希望这篇也能帮你少踩几个坑。