尧图网页设计 
1. 错误 1267 的背后你第一次撞见它时的典型场景先说说我自己第一次遇到这个报错的感觉。当时我做的是两张业务表的字段关联A 表和 B 表都在同一个库里明明不是什么复杂的查询结果执行 JOIN 的时候一跑就炸SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 看到 Illegal mix of collations 这个提示第一反应是“这是个啥”。当时我把两边的字段类型、编码都看了一遍发现都是 utf8根本想不通哪里“混”了。后来才弄明白在 MySQL 里“编码一致”和“排序规则一致”是两码事这个报错恰恰是针对排序规则的字符集一样不等于排序规则一样。这个错误适合谁看基本覆盖了所有人只要写 MySQL 查询就会遇到的场景从后端开发、数据分析师到兼职帮别人维护数据库的运维哪怕是零基础刚接触 MySQL 的小白只要你能运行 SQL这一天早晚会来。它不一定是你写错了 SQL更可能是库、表、列、连接这几层里的排序规则没对齐。顺便说一句网上很多资料让直接改一个字段就完事这种解决办法有时候有效有时候会给你留下更大的隐患。所以我更建议把这个问题拆成“它为什么会发生”“怎么定位”“哪些修法能长期用”“哪些修法只适合临时救火”四个维度来理解。这篇博客就是按这个思路写的里面也包含我在实际生产库里排查和修复时踩过的坑。1.1 字符集和排序规则的关系先补三分钟基础如果你已经比较懂这块可以跳过这一节。如果你一看到“collation”就发怵那我建议花三分钟看一下搞清楚这两个概念之后剩下的排查会轻松非常多。字符集Character Set决定了哪些字符能存以及这些字符怎么转换成二进制。比如 utf8mb4 可以存中文、emoji、特殊符号utf8 在 MySQL 里其实最多存 3 字节遇到某些 4 字节符号就会出问题。排序规则Collation则是在字符集的基础上规定字符之间怎么比较大小、怎么排序。举个例子utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin这三种排序规则都在 utf8mb4 字符集下但是比较逻辑完全不一样。_ci结尾的表示不区分大小写_bin表示二进制比较也就是严格区分大小写。_general_ci比较快但 Unicode 排序规则相对简单。_unicode_ci基于更完整的 Unicode 规则对某些多语言场景更准确。_0900_ai_ci是 MySQL 8.0 里引入的 UCA 9.0.0 版本排序规则对重音符号、大小写的处理更精细也是 utf8mb4 在 MySQL 8.0 里的默认排序规则。同一个字符集下面的不同排序规则在比较的时候完全可以“意见不合”。MySQL 一旦发现两边排序规则不一致就会直接给你 1267 这个错误而不是自作主张替你选一个排序规则。这么做是很谨慎的因为不同排序规则看起来差不多实际比较结果可能差很远。1.2 为什么 MySQL 不直接“兼容”不同排序规则这里我想说一个很多人容易误解的点。你以为 MySQL 会在两个不同排序规则之间做个隐式转换其实它确实会但只在满足一定条件时才会。MySQL 内部有一个叫coercibility强制级别的机制简单理解就是当两个操作数排序规则不同时MySQL 会看看谁的“优先级”更高。优先级从高到低大致是这样你显式写了COLLATE子句的值优先级最高。列、字段默认的排序规则优先级中等。表的默认排序规则优先级更低。数据库默认排序规则再低一点。连接层/会话变量的默认排序规则优先级最低。如果其中一个操作数优先级明显更高MySQL 就把另一个隐式转换过去不报错。但如果两个操作数的优先级差不多、互相不服气MySQL 就谁也劝不动只能报 1267 让你自己处理。搜索热词里的“mysql 报错 1267”“Illegal mix of collations”之所以三天两头上热搜就是因为很多开发者在建表时根本不关心排序规则依赖 MySQL 默认值而不同版本的 MySQL 默认值又不一样最终把几个默认值混到了一起自然就吵起来了。2. 追根溯源从库、表、字段、连接四个层面定位冲突既然知道根因是“排序规则不一致”那下一步就是找到到底是谁和谁不一致。很多人的做法是全库扫描然后瞎猜我觉得效率太低建议按下面顺序来。2.1 先看当前会话和数据库的排序规则执行这几条命令把环境亮出来SHOW VARIABLES LIKE collation_server; SHOW VARIABLES LIKE collation_database; SHOW VARIABLES LIKE collation_connection; SELECT character_set_client, character_set_connection;这三条分别对应服务器层、当前库、当前连接的排序规则。很多时候1267 并不是真的表和表之间不一致而是你输入的字符串常量或参数用了连接层的排序规则而列用了列本身的排序规则双方一比较就出问题。如果你用的是 Navicat、MySQL Workbench 这类图形工具可以在连接属性里面看到字符集和排序规则但最常见的还是命令行或程序连接池里的默认值。建议先把这几个值截图或记下来后面排查会反复用到。2.2 查看库、表、列的真实定义再用这三条组合拳查看结构SHOW CREATE DATABASE your_database_name; SHOW CREATE TABLE your_table_name; SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema your_database_name AND table_name your_table_name;如果你不确定是哪一列就用这条 SQL 把表中的字段全扫出来SELECT table_schema, table_name, column_name, data_type, character_set_name, collation_name FROM information_schema.columns WHERE table_schema your_database_name AND table_name your_table_name;information_schema.columns 就是我排查这种事情的首选工具它能直接反映出某一张表里哪些列是 utf8mb4_general_ci哪些列是 utf8mb4_unicode_ci。所以你根本不需要肉眼去翻建表语句。2.3 检查 JOIN、WHERE、ORDER BY、GROUP BY 这些操作点1267 的报错信息里一般会告诉你是在哪个操作符上报错的常见的是for operation 也可能是like、、、order by等。你把这些操作涉及的列和常量都检查一遍基本就能锁定两列关联时A 列是 utf8_general_ciB 列是 utf8_unicode_ci。一列是 utf8mb4_unicode_ci常量来自另一个连接连接使用的排序规则是 utf8mb4_general_ci。调用函数时比如CONCAT、COALESCE、CASE WHEN多个参数来自不同排序规则的字段。存储过程或视图里面局部变量、入参、游标里带的排序规则不一致。很多开发者在创建存储过程时根本没管 collation局部变量直接继承函数的默认值结果在过程内部做字符串比较时开始报错。这一点容易被忽略因为存储过程内部报错往往会被外层封装成其他错误提示让人更难定位。2.4 用“最小化复现”确认根因如果你不确定是谁和谁冲突我给你推荐一个很笨但很有效的方法把 SQL 拆到最小。比如你原来是这样SELECT * FROM orders o JOIN users u ON o.user_name u.name WHERE o.remark 测试;那就先测试SELECT 1 FROM users WHERE name 测试;再测试SELECT 1 FROM orders WHERE remark 测试;再测试两个字段单独关联SELECT o.user_name, u.name FROM orders o, users u WHERE o.user_name u.name;哪一步开始报错就把哪一步涉及到的字段和常量拿出来用 information_schema 查。这个方法虽然看起来笨但效率极高。我在生产环境里排查过几个存储过程连环报错最后都是靠这种逐层缩小范围的方式定位到根因的。3. 修复的三条路线改字段、写 COLLATE、调连接定位到根因之后修复路线基本就是三条。我按“改动范围从小到大”排一下你可以根据实际情况选不要一上来就 ALTER TABLE 整个库。3.1 只针对当前 SQL在表达式或查询里显式指定 COLLATE如果你只想让某一条查询立刻跑通最稳妥的临时方案是显式加COLLATE让它覆盖冲突。比如SELECT * FROM users u JOIN orders o ON u.name o.user_name COLLATE utf8mb4_unicode_ci WHERE u.name 张三 COLLATE utf8mb4_unicode_ci;注意COLLATE要放在具体的列或字符串字面量后面。在 JOIN 条件里我习惯把不统一的那个字段加上COLLATE让两边统一到同一个排序规则。这样做的原理就是利用我们前面说的“coercibility”显式指定的COLLATE优先级最高可以直接压过字段本身的默认排序规则。这个方案的好处是不用改表结构不会影响索引也不会锁表。坏处是治标不治本如果你有很多地方都要这样加你的 SQL 会变得很啰嗦而且别人接手时很容易看漏。所以这只适合“应急”或者“个别 SQL”的场景。3.2 调整连接使用的排序规则适合业务系统连接池如果你发现整条链路主要是“程序传进来的字符串常量”和“表里的字段”冲突那可以考虑调整连接层的排序规则。MySQL 中可以用SET NAMES来指定客户端发送 SQL 时使用的字符集和排序规则SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;这条命令相当于同时设置了character_set_client、character_set_connection和character_set_results在命令行中很好用。但是对于 Java、Python 等业务系统里的数据库连接池你更应该在连接初始化时就把这个值固定住。以常见的 HikariCP 连接池为例在配置里可以有一个connectionInitSqls里面写connectionInitSqls[0]SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci或在 JDBC URL 上设置jdbc:mysql://localhost:3306/test?useUnicodetruecharacterEncodingutf8需要提醒的是不同版本的 JDBC 驱动对排序规则的处理并不完全相同有时候你只设置了 characterEncoding驱动自己又给连接指定了一套默认排序规则结果照样报 1267。所以我还是比较推荐用SET NAMES初始化直白且可控。这种方式适合“不想改表结构、但希望所有新连接都统一口径”的情况配合连接池重启后会立刻生效。但是如果数据库中已经存在混乱的字段定义只改连接并不能解决所有 JOIN 场景。3.3 从根本上改库、改表、改字段ALTER TABLE这是最彻底的方式让数据字典里的列定义从“根上”保持一致。我大多数情况下会用到这一招但不是每次都建议对整库执行。如果想把整张表的所有字符列统一成某一种字符集和排序规则可以这样做ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这里的关键词是CONVERT TO它会转换表中所有字符类列而不是仅仅修改表的默认值。如果你只想改某一个列可以用ALTER TABLE users MODIFY COLUMN name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这里有一个特别常见的坑你用CONVERT TO之后因为数据重新编码、排序规则变了原本唯一的索引可能突然出现重复值。比如某列之前是utf8mb4_general_ci不区分大小写改成utf8mb4_bin区分大小写之后原来的唯一索引可能没变化反过来如果你从_bin改成_ci原来认为不同的“ABC”和“abc”现在就变成一样的了唯一索引会报 duplicate entry。所以执行任何 ALTER 之前先自己问一句这个字段有没有唯一索引排序规则改完之后会不会触发重复键冲突同时在生产库上做 ALTER 前一定要先备份并尽量在低峰期执行。大表上的 ALTER 会非常耗时MySQL 5.7 的CONVERT TO不能只改元数据可能要重建整张表如果你用的是 MySQL 8.0虽然很多 DDL 是 in-place 的但字符集转换这类操作仍可能锁表或极大增加 IO。如果表实在太大我的建议是别直接 ALTER考虑创建新表再导数据。比如CREATE TABLE users_new LIKE users; ALTER TABLE users_new CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; INSERT INTO users_new SELECT * FROM users; RENAME TABLE users TO users_old, users_new TO users;这样虽然繁琐但你可以控制节奏还能在切换前先比对行数和关键数据。skipped 到 Online DDL 或者 gh-ost 工具也可以不过那属于大表进阶操作这里先不展开。4. Navicat、ORM、连接池这些场景里最容易踩的坑光讲纯 SQL 其实不够因为大多数人不是直接在命令行敲 SQL而是通过图形工具或者业务系统。接下来聊聊我在 Navicat、Workbench、Java/Python 项目里经常遇到的几种特殊情况。4.1 图形工具里看起来全是 utf8mb4一执行却报错用 Navicat 打开表结构字符集那里显示 utf8mb4排序规则那里却可能各不相同。有人建表时用了 Navicat 的默认“utf8mb4_general_ci”有人建表时用 SQL 脚本写的是“utf8mb4_unicode_ci”还有人是直接从别的库导过来的带着源库的“utf8mb4_0900_ai_ci”。这种由图形工具拼接出来的“混合排序规则”比命令行环境更容易埋雷。排查方式很简单在 Navicat 里选中表右键选“设计表”看每个字符列的排序规则列或者直接打开查询工具运行我上面提到的 information_schema 查询。不要相信“全部显示 utf8mb4 就代表统一了”一定要看完整的 collation_name。另一个和图形工具相关的坑是导入脚本。比如你从 A 环境导出一个 .sql 文件里面写着CREATE DATABASE或CREATE TABLE时带有DEFAULT COLLATEutf8mb4_general_ci而你导入 B 环境时 B 环境默认是utf8mb4_unicode_ci导入之后就会并存两种排序规则。在 Navicat 里导入数据时它通常不会主动帮你做统一转换只会原样执行脚本。所以如果你在迁移环境最好先检查 SQL 脚本头部有无显式的COLLATE没有的话导入后再用前面提到的扫描 SQL 验证一遍。4.2 ORM 框架下的“看不见的连接排序规则”在 Java 项目中很多兄弟会在实体类上用Column(columnDefinition varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci)这种写法让 MyBatis 之类的框架生成 DDL。这个写法本身没什么问题但如果你的项目里早期一些表用的注解写的是utf8mb4_general_ci后来新表换成了utf8mb4_unicode_ci那你的数据库里就会出现新旧混杂。更隐蔽的是 MySQL 驱动初始化连接时自带的排序规则。MySQL Connector/J 8.x 在未指定排序规则时通常会根据服务器默认值来设置连接排序规则。如果你的数据库服务器默认排序规则是utf8mb4_0900_ai_ci而业务代码里绑定的是utf8mb4_general_ci的列那么动态 SQL 中如果直接对比变量和字段照样可能 1267。对此我的建议是所有连接池初始化 SQL 中统一加上SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci。虽然这不能完全替代表结构的统一但至少能保证“从客户端进入的字符串”不再额外制造冲突。因为 1267 这种错误往往是“第三方变量”参与比较时才出现连接层统一之后很多偶发问题会直接消失。4.3 函数、存储过程、视图里的隐式继承存储过程是另一个容易中招的地方。你在过程中写一个局部变量MySQL 不会自动把它设置为表的排序规则而是继承当前函数或会话的默认值。所以即使你的表结构是整齐划一的 utf8mb4_unicode_ci只要过程的入参变量带了别的排序规则在过程内部做IF a b或WHERE a b时也可能报 1267。我的习惯是在存储过程或函数开头显式声明参数类型时顺便带上排序规则或者在需要比较的字段后面加COLLATE。虽然会增加一点代码量但能避免很多偶发问题。视图也有这个问题因为视图其实是把查询 SQL 固化下来查询里各个字段的排序规则都不隐藏。你用 SHOW CREATE VIEW 看到的 SQL 里如果能看到字段来自不同排序规则那这个视图早晚会在某个 JOIN 场景爆雷。5. 排序规则不影响索引吗这个问题直接决定你要不要 ALTER很多人在解决 1267 时喜欢“宁杀错不放过”直接把整库改成同一个排序规则。但我想提醒一句排序规则不是只影响比较结果它还会影响索引的排序方式和唯一性判断。乱改一气可能让某些查询走不了索引或者让语义发生变化。5.1 排序规则对索引的影响MySQL 的二级索引本质上就是一种有序结构索引里的数据按照列定义时的排序规则来排列。当你在 WHERE 条件里用一个和索引列不同排序规则的值去比较时优化器有时候就不能直接利用索引前缀匹配甚至放弃这个索引。比如列定义是utf8mb4_bin查询时你对这一列加COLLATE utf8mb4_general_ci去比较MySQL 会老老实实先算出分组结果再去做匹配索引往往很难生效查询性能会掉得让你怀疑人生。所以如果你要改列的排序规则一定要先想想这个比较规则是否符合业务语义需要区分大小写存储时_bin是合理的。注册登录场景中想忽略大小写匹配_ci更合适。如果只是为了解决 1267却把整库改成_bin那之前不区分大小写的登录逻辑可能会立刻出问题。我在实际项目里见过有人把用户名列改成utf8mb4_bin后用户登录时发现以前能匹配的大小写变体现在进不去了最终只能再做一层 LOWER() 处理既麻烦又影响索引。这就是典型的“只管 1267不管业务语义”。5.2 临时表、子查询中的排序规则另一个容易忽略的索引问题是临时表。当 MySQL 无法直接在内存中完成排序就会创建临时表。如果临时表内的排序规则和原表不一致比如 GROUP BY 时对字段做隐式转换就会出现 using temporary 的性能现象。更糟的情况下临时表里的比较同样会报 1267。遇到这类问题你只有两条路要么给相关的字段加显式 COLLATE让临时表统一要么把整表统一排序规则。单独验证一条 SQL 是否走索引可以用 EXPLAIN 查看key字段是否生效。如果发现 key 变成 NULL而之前明明是索引优先怀疑排序规则冲突导致的隐式转换。5.3 大表 ALTER 要不要用在线 DDL 工具前面讲过ALTER TABLE CONVERT TO CHARACTER SET对大表来说这个操作可能非常漫长。如果生产环境核心表有几百万行甚至上亿行直接 ALTER 可能锁表很久业务侧直接告警。我的经验是MySQL 8.0 虽然是 in-place DDL但字符集转换仍然需要重写数据页IO 压力巨大。如果必须改优先在低峰期执行。超过千万行的表我建议用 gh-ost 或 pt-osc 这类工具创建触发器同步新表最后秒级切换可以极大减少锁表时间。改完第一时间重建统计信息一般来说ANALYZE TABLE一下避免优化器沿用旧统计信息。尤其是生产库千万不要为了“统一”两个字直接把全库刷一遍。很多字段根本没参与跨排序规则的比较改它反而徒增风险。只改真正冲突的字段才是长期稳健的做法。6. 经验长谈从建库开始规范才能少当救火队长最后我想给一些“防患于未然”的建议。这些习惯如果有可能永远遇不到 1267如果已经遇到了也可以边修边补。6.1 建库建表时把默认排序规则写清楚第一件事建库时不要只写 CHARACTER SET顺手把 COLLATE 也写上CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;建表时同理CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(64) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;很多 1267 的根源就是“我用了数据库默认”但不同实例默认值不一样。一台测试机器上默认是utf8mb4_general_ci另一台生产机器是utf8mb4_0900_ai_ci脚本一迁过来就全乱套。显式写清楚之后至少迁移时有一个明确基准。6.2 用一条 SQL 扫描全库的排序规则情况如果手上已经有一个老库我强烈建议你把这些信息列出来看看各表之间的排序规则分布SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema NOT IN (mysql,information_schema,performance_schema,sys) ORDER BY table_collation;想精确到字段可以这样SELECT table_schema, table_name, column_name, collation_name FROM information_schema.columns WHERE table_schema your_db_name AND character_set_name IS NOT NULL ORDER BY collation_name;这种查询我基本每个月跑一次尤其在上线新功能前。如果发现某个字段的排序规则跟主流不一样先问一句“这个字段为什么不一样”。如果确实是有意为之要在文档里备注如果不是趁早改掉避免后续在 JOIN 里爆雷。6.3 MySQL 版本升级后的额外提醒从 MySQL 5.7 升级到 MySQL 8.0或者从 8.0 的小版本跨越到新小版本默认排序规则可能会发生变化。比如 utf8mb4 在 8.0 的默认排序规则是utf8mb4_0900_ai_ci在 5.7 里可能是utf8mb4_general_ci。你用 mysqldump 导出的建表语句如果没带 DEFAULT COLLATE导入到 8.0 之后就会变成新默认值进而和旧库的其他表产生排序规则不一致。升级前我的习惯是先在测试环境里用同版本的 MySQL 8.0 导入一遍把所有的建表语句检查一遍尤其注意看字段的 COLLATE 是否和你原库一致。不一致的优先修改 SQL 脚本而不是事后 ALTER。6.4 最后说一个实用小技巧如果你已经有一堆库、一堆表实在不想逐个检查我建议用下面这条 SQL 把“和全库默认排序规则不一致的字段”找出来SELECT c.table_schema, c.table_name, c.column_name, c.collation_name FROM information_schema.columns c JOIN information_schema.tables t ON c.table_schema t.table_schema AND c.table_name t.table_name WHERE t.table_collation c.collation_name AND c.character_set_name IS NOT NULL ORDER BY c.table_schema, c.table_name;这并不能代表一定有问题但它能快速暴露“哪些字段是特立独行的”。我看到结果之后会顺着业务逻辑一个个确认是不是业务需要是不是历史遗留如果都不是再按下文提到的修表流程统一。用这种扫描代替肉眼去翻建表语句效率会高很多。从第一次遇到 1267 到现在我基本不再被它吓到了。只要搞懂字符集和排序规则的关系、定位到冲突的边界再根据场景决定是用显式 COLLATE 救急、修改连接参数还是彻底改表这个错误其实就只是 MySQL 给你的一个善意的警告它不想在你不确定的情况下帮你做决定。数据库设计越规范这种“警告”就越少真遇上了也千万别想着“改一次碰运气”把修复过程当成一次梳理库结构的契机反而能让你以后少踩很多坑。 MySQL错误1267：字符集与排序规则冲突的排查与修复指南
1. 错误 1267 的背后你第一次撞见它时的典型场景先说说我自己第一次遇到这个报错的感觉。当时我做的是两张业务表的字段关联A 表和 B 表都在同一个库里明明不是什么复杂的查询结果执行 JOIN 的时候一跑就炸SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 看到 Illegal mix of collations 这个提示第一反应是“这是个啥”。当时我把两边的字段类型、编码都看了一遍发现都是 utf8根本想不通哪里“混”了。后来才弄明白在 MySQL 里“编码一致”和“排序规则一致”是两码事这个报错恰恰是针对排序规则的字符集一样不等于排序规则一样。这个错误适合谁看基本覆盖了所有人只要写 MySQL 查询就会遇到的场景从后端开发、数据分析师到兼职帮别人维护数据库的运维哪怕是零基础刚接触 MySQL 的小白只要你能运行 SQL这一天早晚会来。它不一定是你写错了 SQL更可能是库、表、列、连接这几层里的排序规则没对齐。顺便说一句网上很多资料让直接改一个字段就完事这种解决办法有时候有效有时候会给你留下更大的隐患。所以我更建议把这个问题拆成“它为什么会发生”“怎么定位”“哪些修法能长期用”“哪些修法只适合临时救火”四个维度来理解。这篇博客就是按这个思路写的里面也包含我在实际生产库里排查和修复时踩过的坑。1.1 字符集和排序规则的关系先补三分钟基础如果你已经比较懂这块可以跳过这一节。如果你一看到“collation”就发怵那我建议花三分钟看一下搞清楚这两个概念之后剩下的排查会轻松非常多。字符集Character Set决定了哪些字符能存以及这些字符怎么转换成二进制。比如 utf8mb4 可以存中文、emoji、特殊符号utf8 在 MySQL 里其实最多存 3 字节遇到某些 4 字节符号就会出问题。排序规则Collation则是在字符集的基础上规定字符之间怎么比较大小、怎么排序。举个例子utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin这三种排序规则都在 utf8mb4 字符集下但是比较逻辑完全不一样。_ci结尾的表示不区分大小写_bin表示二进制比较也就是严格区分大小写。_general_ci比较快但 Unicode 排序规则相对简单。_unicode_ci基于更完整的 Unicode 规则对某些多语言场景更准确。_0900_ai_ci是 MySQL 8.0 里引入的 UCA 9.0.0 版本排序规则对重音符号、大小写的处理更精细也是 utf8mb4 在 MySQL 8.0 里的默认排序规则。同一个字符集下面的不同排序规则在比较的时候完全可以“意见不合”。MySQL 一旦发现两边排序规则不一致就会直接给你 1267 这个错误而不是自作主张替你选一个排序规则。这么做是很谨慎的因为不同排序规则看起来差不多实际比较结果可能差很远。1.2 为什么 MySQL 不直接“兼容”不同排序规则这里我想说一个很多人容易误解的点。你以为 MySQL 会在两个不同排序规则之间做个隐式转换其实它确实会但只在满足一定条件时才会。MySQL 内部有一个叫coercibility强制级别的机制简单理解就是当两个操作数排序规则不同时MySQL 会看看谁的“优先级”更高。优先级从高到低大致是这样你显式写了COLLATE子句的值优先级最高。列、字段默认的排序规则优先级中等。表的默认排序规则优先级更低。数据库默认排序规则再低一点。连接层/会话变量的默认排序规则优先级最低。如果其中一个操作数优先级明显更高MySQL 就把另一个隐式转换过去不报错。但如果两个操作数的优先级差不多、互相不服气MySQL 就谁也劝不动只能报 1267 让你自己处理。搜索热词里的“mysql 报错 1267”“Illegal mix of collations”之所以三天两头上热搜就是因为很多开发者在建表时根本不关心排序规则依赖 MySQL 默认值而不同版本的 MySQL 默认值又不一样最终把几个默认值混到了一起自然就吵起来了。2. 追根溯源从库、表、字段、连接四个层面定位冲突既然知道根因是“排序规则不一致”那下一步就是找到到底是谁和谁不一致。很多人的做法是全库扫描然后瞎猜我觉得效率太低建议按下面顺序来。2.1 先看当前会话和数据库的排序规则执行这几条命令把环境亮出来SHOW VARIABLES LIKE collation_server; SHOW VARIABLES LIKE collation_database; SHOW VARIABLES LIKE collation_connection; SELECT character_set_client, character_set_connection;这三条分别对应服务器层、当前库、当前连接的排序规则。很多时候1267 并不是真的表和表之间不一致而是你输入的字符串常量或参数用了连接层的排序规则而列用了列本身的排序规则双方一比较就出问题。如果你用的是 Navicat、MySQL Workbench 这类图形工具可以在连接属性里面看到字符集和排序规则但最常见的还是命令行或程序连接池里的默认值。建议先把这几个值截图或记下来后面排查会反复用到。2.2 查看库、表、列的真实定义再用这三条组合拳查看结构SHOW CREATE DATABASE your_database_name; SHOW CREATE TABLE your_table_name; SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema your_database_name AND table_name your_table_name;如果你不确定是哪一列就用这条 SQL 把表中的字段全扫出来SELECT table_schema, table_name, column_name, data_type, character_set_name, collation_name FROM information_schema.columns WHERE table_schema your_database_name AND table_name your_table_name;information_schema.columns 就是我排查这种事情的首选工具它能直接反映出某一张表里哪些列是 utf8mb4_general_ci哪些列是 utf8mb4_unicode_ci。所以你根本不需要肉眼去翻建表语句。2.3 检查 JOIN、WHERE、ORDER BY、GROUP BY 这些操作点1267 的报错信息里一般会告诉你是在哪个操作符上报错的常见的是for operation 也可能是like、、、order by等。你把这些操作涉及的列和常量都检查一遍基本就能锁定两列关联时A 列是 utf8_general_ciB 列是 utf8_unicode_ci。一列是 utf8mb4_unicode_ci常量来自另一个连接连接使用的排序规则是 utf8mb4_general_ci。调用函数时比如CONCAT、COALESCE、CASE WHEN多个参数来自不同排序规则的字段。存储过程或视图里面局部变量、入参、游标里带的排序规则不一致。很多开发者在创建存储过程时根本没管 collation局部变量直接继承函数的默认值结果在过程内部做字符串比较时开始报错。这一点容易被忽略因为存储过程内部报错往往会被外层封装成其他错误提示让人更难定位。2.4 用“最小化复现”确认根因如果你不确定是谁和谁冲突我给你推荐一个很笨但很有效的方法把 SQL 拆到最小。比如你原来是这样SELECT * FROM orders o JOIN users u ON o.user_name u.name WHERE o.remark 测试;那就先测试SELECT 1 FROM users WHERE name 测试;再测试SELECT 1 FROM orders WHERE remark 测试;再测试两个字段单独关联SELECT o.user_name, u.name FROM orders o, users u WHERE o.user_name u.name;哪一步开始报错就把哪一步涉及到的字段和常量拿出来用 information_schema 查。这个方法虽然看起来笨但效率极高。我在生产环境里排查过几个存储过程连环报错最后都是靠这种逐层缩小范围的方式定位到根因的。3. 修复的三条路线改字段、写 COLLATE、调连接定位到根因之后修复路线基本就是三条。我按“改动范围从小到大”排一下你可以根据实际情况选不要一上来就 ALTER TABLE 整个库。3.1 只针对当前 SQL在表达式或查询里显式指定 COLLATE如果你只想让某一条查询立刻跑通最稳妥的临时方案是显式加COLLATE让它覆盖冲突。比如SELECT * FROM users u JOIN orders o ON u.name o.user_name COLLATE utf8mb4_unicode_ci WHERE u.name 张三 COLLATE utf8mb4_unicode_ci;注意COLLATE要放在具体的列或字符串字面量后面。在 JOIN 条件里我习惯把不统一的那个字段加上COLLATE让两边统一到同一个排序规则。这样做的原理就是利用我们前面说的“coercibility”显式指定的COLLATE优先级最高可以直接压过字段本身的默认排序规则。这个方案的好处是不用改表结构不会影响索引也不会锁表。坏处是治标不治本如果你有很多地方都要这样加你的 SQL 会变得很啰嗦而且别人接手时很容易看漏。所以这只适合“应急”或者“个别 SQL”的场景。3.2 调整连接使用的排序规则适合业务系统连接池如果你发现整条链路主要是“程序传进来的字符串常量”和“表里的字段”冲突那可以考虑调整连接层的排序规则。MySQL 中可以用SET NAMES来指定客户端发送 SQL 时使用的字符集和排序规则SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;这条命令相当于同时设置了character_set_client、character_set_connection和character_set_results在命令行中很好用。但是对于 Java、Python 等业务系统里的数据库连接池你更应该在连接初始化时就把这个值固定住。以常见的 HikariCP 连接池为例在配置里可以有一个connectionInitSqls里面写connectionInitSqls[0]SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci或在 JDBC URL 上设置jdbc:mysql://localhost:3306/test?useUnicodetruecharacterEncodingutf8需要提醒的是不同版本的 JDBC 驱动对排序规则的处理并不完全相同有时候你只设置了 characterEncoding驱动自己又给连接指定了一套默认排序规则结果照样报 1267。所以我还是比较推荐用SET NAMES初始化直白且可控。这种方式适合“不想改表结构、但希望所有新连接都统一口径”的情况配合连接池重启后会立刻生效。但是如果数据库中已经存在混乱的字段定义只改连接并不能解决所有 JOIN 场景。3.3 从根本上改库、改表、改字段ALTER TABLE这是最彻底的方式让数据字典里的列定义从“根上”保持一致。我大多数情况下会用到这一招但不是每次都建议对整库执行。如果想把整张表的所有字符列统一成某一种字符集和排序规则可以这样做ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这里的关键词是CONVERT TO它会转换表中所有字符类列而不是仅仅修改表的默认值。如果你只想改某一个列可以用ALTER TABLE users MODIFY COLUMN name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这里有一个特别常见的坑你用CONVERT TO之后因为数据重新编码、排序规则变了原本唯一的索引可能突然出现重复值。比如某列之前是utf8mb4_general_ci不区分大小写改成utf8mb4_bin区分大小写之后原来的唯一索引可能没变化反过来如果你从_bin改成_ci原来认为不同的“ABC”和“abc”现在就变成一样的了唯一索引会报 duplicate entry。所以执行任何 ALTER 之前先自己问一句这个字段有没有唯一索引排序规则改完之后会不会触发重复键冲突同时在生产库上做 ALTER 前一定要先备份并尽量在低峰期执行。大表上的 ALTER 会非常耗时MySQL 5.7 的CONVERT TO不能只改元数据可能要重建整张表如果你用的是 MySQL 8.0虽然很多 DDL 是 in-place 的但字符集转换这类操作仍可能锁表或极大增加 IO。如果表实在太大我的建议是别直接 ALTER考虑创建新表再导数据。比如CREATE TABLE users_new LIKE users; ALTER TABLE users_new CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; INSERT INTO users_new SELECT * FROM users; RENAME TABLE users TO users_old, users_new TO users;这样虽然繁琐但你可以控制节奏还能在切换前先比对行数和关键数据。skipped 到 Online DDL 或者 gh-ost 工具也可以不过那属于大表进阶操作这里先不展开。4. Navicat、ORM、连接池这些场景里最容易踩的坑光讲纯 SQL 其实不够因为大多数人不是直接在命令行敲 SQL而是通过图形工具或者业务系统。接下来聊聊我在 Navicat、Workbench、Java/Python 项目里经常遇到的几种特殊情况。4.1 图形工具里看起来全是 utf8mb4一执行却报错用 Navicat 打开表结构字符集那里显示 utf8mb4排序规则那里却可能各不相同。有人建表时用了 Navicat 的默认“utf8mb4_general_ci”有人建表时用 SQL 脚本写的是“utf8mb4_unicode_ci”还有人是直接从别的库导过来的带着源库的“utf8mb4_0900_ai_ci”。这种由图形工具拼接出来的“混合排序规则”比命令行环境更容易埋雷。排查方式很简单在 Navicat 里选中表右键选“设计表”看每个字符列的排序规则列或者直接打开查询工具运行我上面提到的 information_schema 查询。不要相信“全部显示 utf8mb4 就代表统一了”一定要看完整的 collation_name。另一个和图形工具相关的坑是导入脚本。比如你从 A 环境导出一个 .sql 文件里面写着CREATE DATABASE或CREATE TABLE时带有DEFAULT COLLATEutf8mb4_general_ci而你导入 B 环境时 B 环境默认是utf8mb4_unicode_ci导入之后就会并存两种排序规则。在 Navicat 里导入数据时它通常不会主动帮你做统一转换只会原样执行脚本。所以如果你在迁移环境最好先检查 SQL 脚本头部有无显式的COLLATE没有的话导入后再用前面提到的扫描 SQL 验证一遍。4.2 ORM 框架下的“看不见的连接排序规则”在 Java 项目中很多兄弟会在实体类上用Column(columnDefinition varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci)这种写法让 MyBatis 之类的框架生成 DDL。这个写法本身没什么问题但如果你的项目里早期一些表用的注解写的是utf8mb4_general_ci后来新表换成了utf8mb4_unicode_ci那你的数据库里就会出现新旧混杂。更隐蔽的是 MySQL 驱动初始化连接时自带的排序规则。MySQL Connector/J 8.x 在未指定排序规则时通常会根据服务器默认值来设置连接排序规则。如果你的数据库服务器默认排序规则是utf8mb4_0900_ai_ci而业务代码里绑定的是utf8mb4_general_ci的列那么动态 SQL 中如果直接对比变量和字段照样可能 1267。对此我的建议是所有连接池初始化 SQL 中统一加上SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci。虽然这不能完全替代表结构的统一但至少能保证“从客户端进入的字符串”不再额外制造冲突。因为 1267 这种错误往往是“第三方变量”参与比较时才出现连接层统一之后很多偶发问题会直接消失。4.3 函数、存储过程、视图里的隐式继承存储过程是另一个容易中招的地方。你在过程中写一个局部变量MySQL 不会自动把它设置为表的排序规则而是继承当前函数或会话的默认值。所以即使你的表结构是整齐划一的 utf8mb4_unicode_ci只要过程的入参变量带了别的排序规则在过程内部做IF a b或WHERE a b时也可能报 1267。我的习惯是在存储过程或函数开头显式声明参数类型时顺便带上排序规则或者在需要比较的字段后面加COLLATE。虽然会增加一点代码量但能避免很多偶发问题。视图也有这个问题因为视图其实是把查询 SQL 固化下来查询里各个字段的排序规则都不隐藏。你用 SHOW CREATE VIEW 看到的 SQL 里如果能看到字段来自不同排序规则那这个视图早晚会在某个 JOIN 场景爆雷。5. 排序规则不影响索引吗这个问题直接决定你要不要 ALTER很多人在解决 1267 时喜欢“宁杀错不放过”直接把整库改成同一个排序规则。但我想提醒一句排序规则不是只影响比较结果它还会影响索引的排序方式和唯一性判断。乱改一气可能让某些查询走不了索引或者让语义发生变化。5.1 排序规则对索引的影响MySQL 的二级索引本质上就是一种有序结构索引里的数据按照列定义时的排序规则来排列。当你在 WHERE 条件里用一个和索引列不同排序规则的值去比较时优化器有时候就不能直接利用索引前缀匹配甚至放弃这个索引。比如列定义是utf8mb4_bin查询时你对这一列加COLLATE utf8mb4_general_ci去比较MySQL 会老老实实先算出分组结果再去做匹配索引往往很难生效查询性能会掉得让你怀疑人生。所以如果你要改列的排序规则一定要先想想这个比较规则是否符合业务语义需要区分大小写存储时_bin是合理的。注册登录场景中想忽略大小写匹配_ci更合适。如果只是为了解决 1267却把整库改成_bin那之前不区分大小写的登录逻辑可能会立刻出问题。我在实际项目里见过有人把用户名列改成utf8mb4_bin后用户登录时发现以前能匹配的大小写变体现在进不去了最终只能再做一层 LOWER() 处理既麻烦又影响索引。这就是典型的“只管 1267不管业务语义”。5.2 临时表、子查询中的排序规则另一个容易忽略的索引问题是临时表。当 MySQL 无法直接在内存中完成排序就会创建临时表。如果临时表内的排序规则和原表不一致比如 GROUP BY 时对字段做隐式转换就会出现 using temporary 的性能现象。更糟的情况下临时表里的比较同样会报 1267。遇到这类问题你只有两条路要么给相关的字段加显式 COLLATE让临时表统一要么把整表统一排序规则。单独验证一条 SQL 是否走索引可以用 EXPLAIN 查看key字段是否生效。如果发现 key 变成 NULL而之前明明是索引优先怀疑排序规则冲突导致的隐式转换。5.3 大表 ALTER 要不要用在线 DDL 工具前面讲过ALTER TABLE CONVERT TO CHARACTER SET对大表来说这个操作可能非常漫长。如果生产环境核心表有几百万行甚至上亿行直接 ALTER 可能锁表很久业务侧直接告警。我的经验是MySQL 8.0 虽然是 in-place DDL但字符集转换仍然需要重写数据页IO 压力巨大。如果必须改优先在低峰期执行。超过千万行的表我建议用 gh-ost 或 pt-osc 这类工具创建触发器同步新表最后秒级切换可以极大减少锁表时间。改完第一时间重建统计信息一般来说ANALYZE TABLE一下避免优化器沿用旧统计信息。尤其是生产库千万不要为了“统一”两个字直接把全库刷一遍。很多字段根本没参与跨排序规则的比较改它反而徒增风险。只改真正冲突的字段才是长期稳健的做法。6. 经验长谈从建库开始规范才能少当救火队长最后我想给一些“防患于未然”的建议。这些习惯如果有可能永远遇不到 1267如果已经遇到了也可以边修边补。6.1 建库建表时把默认排序规则写清楚第一件事建库时不要只写 CHARACTER SET顺手把 COLLATE 也写上CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;建表时同理CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(64) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;很多 1267 的根源就是“我用了数据库默认”但不同实例默认值不一样。一台测试机器上默认是utf8mb4_general_ci另一台生产机器是utf8mb4_0900_ai_ci脚本一迁过来就全乱套。显式写清楚之后至少迁移时有一个明确基准。6.2 用一条 SQL 扫描全库的排序规则情况如果手上已经有一个老库我强烈建议你把这些信息列出来看看各表之间的排序规则分布SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema NOT IN (mysql,information_schema,performance_schema,sys) ORDER BY table_collation;想精确到字段可以这样SELECT table_schema, table_name, column_name, collation_name FROM information_schema.columns WHERE table_schema your_db_name AND character_set_name IS NOT NULL ORDER BY collation_name;这种查询我基本每个月跑一次尤其在上线新功能前。如果发现某个字段的排序规则跟主流不一样先问一句“这个字段为什么不一样”。如果确实是有意为之要在文档里备注如果不是趁早改掉避免后续在 JOIN 里爆雷。6.3 MySQL 版本升级后的额外提醒从 MySQL 5.7 升级到 MySQL 8.0或者从 8.0 的小版本跨越到新小版本默认排序规则可能会发生变化。比如 utf8mb4 在 8.0 的默认排序规则是utf8mb4_0900_ai_ci在 5.7 里可能是utf8mb4_general_ci。你用 mysqldump 导出的建表语句如果没带 DEFAULT COLLATE导入到 8.0 之后就会变成新默认值进而和旧库的其他表产生排序规则不一致。升级前我的习惯是先在测试环境里用同版本的 MySQL 8.0 导入一遍把所有的建表语句检查一遍尤其注意看字段的 COLLATE 是否和你原库一致。不一致的优先修改 SQL 脚本而不是事后 ALTER。6.4 最后说一个实用小技巧如果你已经有一堆库、一堆表实在不想逐个检查我建议用下面这条 SQL 把“和全库默认排序规则不一致的字段”找出来SELECT c.table_schema, c.table_name, c.column_name, c.collation_name FROM information_schema.columns c JOIN information_schema.tables t ON c.table_schema t.table_schema AND c.table_name t.table_name WHERE t.table_collation c.collation_name AND c.character_set_name IS NOT NULL ORDER BY c.table_schema, c.table_name;这并不能代表一定有问题但它能快速暴露“哪些字段是特立独行的”。我看到结果之后会顺着业务逻辑一个个确认是不是业务需要是不是历史遗留如果都不是再按下文提到的修表流程统一。用这种扫描代替肉眼去翻建表语句效率会高很多。从第一次遇到 1267 到现在我基本不再被它吓到了。只要搞懂字符集和排序规则的关系、定位到冲突的边界再根据场景决定是用显式 COLLATE 救急、修改连接参数还是彻底改表这个错误其实就只是 MySQL 给你的一个善意的警告它不想在你不确定的情况下帮你做决定。数据库设计越规范这种“警告”就越少真遇上了也千万别想着“改一次碰运气”把修复过程当成一次梳理库结构的契机反而能让你以后少踩很多坑。