做数据库的人大概都见过这么一行报错ERROR 1267 (HY000): Illegal mix of collations for operation 我第一次看到它是在一个凌晨上线的报表查询里两张千万级的表 JOIN明明字段类型都是 varchar字符集也都是 utf8mb4可 SQL 就是跑不过。后来定位了很久才把问题锁定在“排序规则”上。在 mysql 里1267 Illegal mix of collations不是偶发灵异事件它几乎必然出现在项目升级、环境迁移、接手别人老库这些场景里。这篇文章不打算绕弯子我直接把处理这个报错的原理、排查顺序、临时方案、根治方案和建表时的预防手段都摊开讲。适合正在跟 1267 搏斗的开发也适合刚被它坑过的运维同学。1. 1267报错到底在说什么字符集、排序规则与“隐式合并”规则很多人第一反应是去查“字符集”因为报错里有 collations 这个词看起来跟 charset 差不多。其实它们是两码事理解这一点后边的修复动作才不会做偏。1.1 字符集管“装”排序规则管“比”字符集决定这张表能存哪些字符。比如utf8mb4能存汉字、拉丁字母、日文假名连 emoji 都能放所以现在大多数新项目都选它。但“能存”和“能比较”是两回事。同一串字符不同排序规则比较出来的结果可能完全不同。举例来说utf8mb4_bin下a和A不相等因为是按二进制逐字节比utf8mb4_general_ci和utf8mb4_unicode_ci下a和A相等因为_ci表示大小写不敏感对于带重音的é、扩展字符ß、ø这类字符unicode_ci与general_ci的处理细节也有差异。也就是说字符集只是“箱子”排序规则才是“尺子”。两个字段哪怕字符集一模一样只要尺子不一样MySQL 就没法直接拿它们做等值比较、排序、分组或合并。这个“尺子不一致”的状态就会触发 Illegal mix of collations。而且排序规则并不是只有列一个层级。MySQL 里至少存在四个层级服务器默认值collation_server、库默认值collation_database、表默认值DEFAULT CHARSET和DEFAULT COLLATE、列定义。建表时不写全列就会继承表表没写全继承库库没写全继承服务器。这一条继承链上任何一环被改过新老表之间就可能出现两套规则并存的局面。1267 的根源往往就是这条链上某个环节断了。1.2 看明白报错文本IMPLICIT和COERCIBLE是什么意思真实报错信息通常长这样ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation 括号里有两个关键元素排序规则名和括号里的强度标记。IMPLICIT表示这个排序规则是“隐含地”来自字段或表的定义不是用户在 SQL 里临时指定的。两个IMPLICIT对撞MySQL 就没有合法依据替你选一个只能停工报错。如果其中一个操作数是用户直接给的字符串常量它的强度通常是COERCIBLE中文可以理解为“可被同化”。MySQL 遇到COERCIBLE时会优先迁就列的定义所以很多查询里直接写WHERE name 搜索词并不报错因为常量被自动同化了。真正爱报错的是两个字段做关联比较、两个子查询结果集做 UNION 这类双方都是“真身”的场景。这里还有个容易混淆的点报错信息里的for operation 不一定就是等值比较。你在 JOIN、LIKE、IN、NOT IN 里都可能看到 1267但因为 MySQL 底层大多会转成某种比较操作所以报错统一归到operation后面。也就是说别看到就只查等值条件把整条 SQL 里所有字符类型的关联条件都过一遍才是正道。1.3 MySQL为什么不自动“翻译”成同一种排序规则这是我早期特别不理解的一点既然知道两边不一致自动转一下不就行了吗后来发现它不敢自动转。排序规则直接影响相等判定和排序结果如果 MySQL 擅自把一边按另一边规则“翻译”很可能改变业务匹配结果。比如用户表的mobile用utf8mb4_unicode_ci订单表的mobile用utf8mb4_general_ci有些冷门字符在两种规则下的比较结果不同自动选一个方向就等于默许错误。所以 MySQL 选择停下来把 1267 抛给你让你自己去统一。这是设计上的保守不是 bug。明白这一点你也就明白了为什么修复的核心永远是“让两边用同一把尺子”而不是东补一块西补一块。2. 排错第一步把冲突来源从四层配置里定位出来报错只告诉你有冲突不会告诉你冲突发生在哪条列上。MySQL 的排序规则从上到下分了好几层服务器全局默认、数据库默认、表默认、列定义再加上连接层的排序规则。任何一个环节不一致都可能酿成 1267。2.1 先把服务器和连接的默认值拉出来看排查时我喜欢先跑这两条 SQL确认宏观环境SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;看character_set_server和collation_server这是服务器级的兜底默认值再看character_set_connection和collation_connection这是当前会话的连接级设置。如果连接层是utf8mb4_general_ci而表字段是utf8mb4_unicode_ci那字符串常量、函数返回值都有可能和列“吵起来”。然后针对报错涉及的表直接执行SHOW CREATE TABLE t_user\G SHOW CREATE TABLE t_order\GSHOW CREATE TABLE会原样输出建表语句表和列上的排序规则一眼就能看出来。这是定位第一步也是我建议每个人都先养成的习惯——不要猜直接看定义。2.2 用information_schema扫描全库“打架”字段当报错牵涉的表很多或者你接手老库想提前排查隐患时一条 SQL 扫全库最省事SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db AND DATA_TYPE IN (char, varchar, text, enum, set, mediumtext, longtext) ORDER BY TABLE_NAME, ORDINAL_POSITION;这个结果集会很长我先关注的是COLLATION_NAME这一列有没有杂音。更快的办法是聚合一下SELECT COLLATION_NAME, COUNT(*) AS cnt FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db GROUP BY COLLATION_NAME ORDER BY cnt DESC;如果结果里有两三种排序规则并存那就是标准的 1267 温床。别急着改先看看哪些表是核心业务表哪些只是临时表再决定统一方向。2.3 一个真实案例三张表JOIN时我先查了什么说个我实际遇到的案例。一张会员表t_user、一张订单表t_order按mobile字段 JOIN报错 1267。我执行了SHOW CREATE TABLE后发现问题很有意思两张表字段类型都是varchar(20)但t_user.mobile是utf8mb4_unicode_cit_order.mobile是utf8mb4_general_ci。原因也不难查会员表建得很早那时候项目默认排序规则是utf8mb4_unicode_ci订单表是大半年后另一个人建的建表时库默认值已经被 DBA 改成了utf8mb4_general_ci。团队没有规定统一规则于是两张“看起来一模一样”的表在底层用的却是两把不同的尺子。这就说明了 1267 的“人格”它专治各种历史遗留、人员变动、规范缺失。光靠加索引、优化 SQL 是治不了的本质上是你库里并存了两套规则。3. 三种修复路径临时匹配、物理改造、全局统一定位到具体列之后修复一般分三档。从最省事的 SQL 临时处理到彻底改表结构再到把库默认值和连接层全部拉齐。我建议按这个顺序评估。3.1 最省事的临时方案在SQL里加COLLATE如果只是某一条查询急等上线不碰表结构就能救火的方法是在比较表达式中显式指定排序规则SELECT u.id, o.order_no FROM t_user u JOIN t_order o ON u.mobile o.mobile COLLATE utf8mb4_unicode_ci WHERE u.mobile 13800138000;这里把右侧的o.mobile显式改成utf8mb4_unicode_ciMySQL 就会把左侧的隐式规则迁就过来冲突消除。也可以加在左侧效果一样。UNION 场景同理SELECT name COLLATE utf8mb4_unicode_ci FROM customer_2019 UNION SELECT name FROM customer_2020;但要明确这是临时救火不是根治。而且有一个代价容易被忽略——索引可能失效。我有一次在 200 万行的订单表上临时加COLLATE跑关联执行计划直接从索引查找变成全表扫描因为优化器认为比较规则和原有索引排序规则对不上了。临时方案只能应急不能沉淀成习惯。3.2 治本方案ALTER TABLE统一列定义真正消除 1267 的物理操作是把冲突列的排序规则改成同一个。以刚才案例为例ALTER TABLE t_order MODIFY COLUMN mobile VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ;注意一点MODIFY COLUMN会重写整列定义如果你原来有NOT NULL DEFAULT 、COMMENT 手机号这类属性一定要一并带上。我见过有人只写了类型和排序规则结果把默认值和注释改没了上线后又多花一轮精力修复。在大表上做ALTER TABLE之前先确认三件事这列上有没有索引、有没有唯一约束、有没有外键引用。有外键的情况下通常得先删外键再改列改完再重建有唯一约束时顺序执行可能因为中间状态短暂变宽松需要评估业务影响。生产环境的建议是去低峰期执行或者用pt-online-schema-change这类在线工具把阻塞窗口压缩到几乎为零。3.3 更大范围的统一库默认值与连接参数列改完后还要把“土壤”也统一否则下一次新建表可能又带回旧的默认值。先改库默认ALTER DATABASE your_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;再让连接层也统一口径。客户端在每次新建连接后可以在连接池初始化时执行SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;或者使用客户端驱动自带的连接参数在连接建立时指定字符集与排序规则。必须理解的是ALTER DATABASE和SET NAMES都只影响“未来新建对象”和“当前连接里的字符串常量”救不了已经存在的列定义。所以正确顺序永远是先处理列再处理库默认最后处理连接层三个动作缺一不可。4. 排序规则怎么选1267背后真正的决策题修复 1267 只是第一步真正难的是决定“统一成谁”。如果选错了方向几个月后另一批新表又会跟你唱反调。4.1 认识几组高频排序规则general_ci / unicode_ci / 0900_ai_ci / bin我用这么一张表来概括最常遇到的几组排序规则所属环境特点常见备注utf8mb4_general_ciMySQL 5.5规则简单、计算快老项目默认值对部分扩展字符排序不严谨utf8mb4_unicode_ciMySQL 5.5基于UCA算法排序更规范兼容性好5.7和8.0都支持utf8mb4_0900_ai_ciMySQL 8.0基于UCA 9.0支持重音不敏感MySQL 8.0 的官方默认值utf8mb4_bin全版本按二进制逐字节比较大小写、重音都敏感适合精确匹配命名里藏着规律ci是 case-insensitiveai是 accent-insensitivebin是 binary。_0900_ai_ci只在 MySQL 8.0 以后能用拿到 5.7 上执行会直接报“Unsupported collation”所以跨版本环境不能轻易选它。4.2 转换前的副作用检查清单真到了要批量转换列的时候我建议按这个清单过一遍顺序不要乱先用SHOW INDEX FROM 表名看这列是不是索引的前缀列再用information_schema.KEY_COLUMN_USAGE看有没有外键牵制检查是否有视图、存储过程、触发器中用到了这些字段的排序规则或比较逻辑如果是从非 utf8mb4 转成 utf8mb4先抽样确认数据里没有目标字符集装不下的字符否则转换后会出现问号或乱码统一方向选定后同库同环境所有字符类型的列尽量用同一个排序规则至少同一个业务域内要完全一致。我把这五条列在团队文档里每次做表结构迁移都先过一遍。1267 看起来只是一个小报错但它背后往往是“哪个环境用哪套规则”没有定论所以最后一步是定标准。4.3 版本升级场景下的12675.7和8.0默认值不同MySQL 5.7 的 utf8mb4 默认排序规则是utf8mb4_general_ciMySQL 8.0 换成了utf8mb4_0900_ai_ci。这个差异在版本切换时特别容易埋雷。我遇到过的典型场景是开发本地用 MySQL 8.0测试库还是 5.7两边数据通过中间表同步。同步本身没问题但后续做跨库 JOIN 或比对脚本时一边表的字段继承 8.0 默认的0900_ai_ci另一边是 5.7 的general_ci1267 立刻蹦出来。这种跨版本环境的统一方向我最推荐utf8mb4_unicode_ci。它不是性能最优的但两个版本都认不会出现识别不了的排序规则也避免为了迁就老环境强行降级。5. 从源头避免1267建表规范与迁移清单说句实在话1267 这个错本身不难解难的是它频繁出现在“没有规范”的团队里。既然已经踩过坑就得在建表和迁移阶段把门关死。5.1 建表时把字符集和排序规则写全别依赖默认值最好的防火墙是在建表语句里显式写全两层配置CREATE TABLE t_order ( mobile VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT , user_id BIGINT NOT NULL, PRIMARY KEY (id), KEY idx_mobile (mobile) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;有人嫌啰嗦觉得反正库默认已经是 utf8mb4 了varchar(20)就够了。可默认值是可以被 DBA 改的建表工具也是可以被版本迭代换掉的。多写这几个字换来的是一张表无论被搬到哪个环境行为都一致。我另外有个习惯复制已有表结构时也绝不省略这两段因为CREATE TABLE NEW LIKE OLD虽然会把列属性带过去但如果你后来在别的环境手工同步表结构漏写配置的概率非常高。5.2 复制表结构、导数据、接老库时的检查清单项目里经常要接历史库、同步老表这时候不要只盯着数据量还要花几分钟检查元数据。我的迁移前检查清单大致是这样对参与同步的库执行一次information_schema.COLUMNS聚合查询确认排序规则种类对比源环境和目标环境的collation_server差异过大时先想好统一方向对老库中排序规则明显偏离主流的表先把SHOW CREATE TABLE导出分析列级别的规则处理列之前确认外键和唯一约束避免在线 DDL 中断或锁表如果是跨 MySQL 大版本迁移优先选两代版本都兼容的排序规则比如utf8mb4_unicode_ci。这个清单看起来很基础但能挡住一大半 1267。它本质上是在告诉你任何两个环境的数据要“碰面”排序规则得先讲好。5.3 我最终定下的团队规范我在团队里定过几条很严的规定后来 1267 几乎绝迹。第一新增和修改字符类型列时建表 SQL 里必须写CHARACTER SET和COLLATE代码评审时专门有人盯这一条。第二所有环境统一用utf8mb4utf8mb4_unicode_ci除非某个环境有特殊理由否则不允许另起规则。第三DBA 的默认参数如果有调整必须同步更新开发规范文档避免下一个人建表时拿到过期的错误默认值。也有人问我0900_ai_ci在 MySQL 8 下性能更好为什么不用它原因就一条团队还有 5.7 环境为了全局一致宁可选择一个“舒服但不出错”的方案。等所有环境都升到 8.0再统一切到0900_ai_ci也不迟。工具选型从来不是挑最好而是挑当前团队最不会出错的那个。