mysql 1267 Illegal mix of collations这报错但凡是在表关联、union、where条件里比较过头疼后面一定会给你加一句for operation 或者for operation join。第一次碰到的人往往会懵明明两个字段都是varchar值也一模一样凭什么告诉我“不能混合”其实这话说的不是数据而是两个字段在底层使用的排序规则collation不同。MySQL在做比较的时候除了看字符串内容还必须先确认两边的字符集和排序规则能对齐一旦一个用的utf8mb4_general_ci另一个用的utf8mb4_unicode_ci它就直接罢工报1267。这篇文章我不打算念文档就按我实际排查这种报错的顺序来写先讲清楚错误的本质再给一套定位冲突列的通用SQL然后分临时解法、永久解法两条路线逐步操作最后聊聊怎么从建表层面避免再次踩坑。不管你是开发、DBA还是运维照着这套流程走一遍基本都能在半小时内搞定。1. 先搞清楚1267错误到底在说什么1.1 字符集和排序规则的关系很多人把字符集和排序规则混在一起说其实这是两个层面的东西。字符集character set决定字符怎么编码存储比如utf8mb4、latin1、gbk排序规则collation决定同一字符集下的比较规则比如大小写是否敏感、是否按二进制比较、特殊字符的权重怎么排。以utf8mb4为例它最常见的几种排序规则是排序规则特点典型场景utf8mb4_general_ci比较速度较快但对某些语言字符的排序不够精确ci表示不区分大小写老项目默认值兼容性最好utf8mb4_unicode_ci基于Unicode标准排序算法对多语言支持更好推荐新项目使用utf8mb4_0900_ai_ciMySQL 8.0默认基于UCA 9.0支持更精细的排序和重音不敏感MySQL 8.0新库首选utf8mb4_bin按二进制比较区分大小写最快需要精确匹配、唯一性校验的场景如果你用的是不同的字符集比如一张表是latin1_swedish_ci一张表是utf8mb4_general_ci那跨表关联时不光排序规则不一样连底层编码都可能不同1267几乎必然出现。MySQL判断两个字符串能不能直接比较有个隐式规则字符集必须相同排序规则必须兼容。排序规则不兼容的意思就是MySQL没法自动确认哪个排序规则优先级更高于是放弃治疗直接抛错。1267里的Illegal mix翻译过来就是“非法混合”说的是元数据层面混了不是数据内容有错。1.2 什么操作最容易触发1267我遇到的1267案例里最常见的触发场景有这么几类JOIN关联两个不同表的字符串字段且两张表或两个字段的排序规则不同。WHERE a.name b.name这种等值比较两边字符集或排序规则不一致。UNION合并结果集时相连的两个SELECT语句里对应列的排序规则不同。CASE WHEN或IF里返回字符串表达式两边排序规则冲突。存储过程传入字符串参数时函数参数的collation和调用方表字段的collation不兼容。INSERT INTO ... SELECT从一张表复制字符串数据到另一张表源和目标排序规则不同。ORDER BY带字符串列时如果排序规则混合也可能触发虽然没等值比较那么常见。这几种情况在运维老项目时特别常见因为很多老库默认是latin1或utf8mb4_general_ci新接手的库是utf8mb4_unicode_ci两张表一join直接报错。2. 拿到报错后先做的三件事2.1 用一条SQL快速定位有冲突的列1267报错虽然会告诉你有冲突但往往只出现在执行时没告诉你具体是哪一列。更麻烦的是一个JOIN里可能涉及五六张表肉眼找不现实。我建议直接查information_schema一秒钟看到整个库的字符集和排序规则分布SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db_name AND CHARACTER_SET_NAME IS NOT NULL ORDER BY TABLE_NAME, COLUMN_NAME;把your_db_name换成你实际库名执行后你会看到一堆行。重点关注COLLATION_NAME列有差异的字段。比如某个字段是utf8mb4_general_ci另一个是同名的utf8mb4_unicode_ci这两个字段一旦关联就会出现1267。如果你想更快定位可以在查询结果里按COLLATION_NAME做分组看看整个库到底有几种排序规则SELECT CHARACTER_SET_NAME, COLLATION_NAME, COUNT(*) AS cnt FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db_name AND CHARACTER_SET_NAME IS NOT NULL GROUP BY CHARACTER_SET_NAME, COLLATION_NAME ORDER BY cnt DESC;如果结果只有一行说明你的库很干净1267多半是临时连接参数或SQL字面量导致的。如果结果有一堆不同组合那这个库的历史包袱就大了需要统一。2.2 查看库、表、列的字符集与排序规则定位到可疑表之后别急着改。先看清三层结构库、表、列。三层都可以分别设置字符集和排序规则优先级是列 表 库。如果你在建表时没显式指定列会继承表表没指定表继承库库没指定继承实例配置。查看库的默认设置SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME your_db_name;查看表结构SHOW CREATE TABLE your_table_name;SHOW CREATE TABLE会展示DDL直接能看到DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci之类的信息。如果你想批量看某张表每一列的情况SHOW FULL COLUMNS FROM your_table_name;注意这里必须加FULL关键字否则看不到字符集和排序规则列。执行结果中Collation一列会清清楚楚显示每一列用的是哪种规则。2.3 确认当前连接的排序规则有时候表结构完全没问题1267发生在SQL字面量或会话变量上。比如你拼接SQL时直接塞了一个中文常量WHERE name 张三。MySQL会把这个字面量的排序规则设为当前连接的collation_connection如果这个值和表字段的排序规则不兼容同样会报1267。查看当前连接设置SHOW VARIABLES LIKE collation_connection; SHOW VARIABLES LIKE character_set_connection; SHOW VARIABLES LIKE collation_database;如果你的应用程序是Java或者Python还要检查JDBC/驱动URL里有没有强制指定encoding。比如Java连接串里characterEncodingutf8只解决了客户端的字符集传递没有直接决定排序规则。如果你发现连接层的collation和其他表字段不一致可以单独设置连接排序规则后重新执行SQL验证SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;这条语句会同时改character_set_client、character_set_connection和character_set_results后面再执行相同SQL看1267是否消失。如果消失了说明问题在连接层不在表结构。3. 解法一用COLLATE让临时比较对齐3.1 在JOIN和WHERE里直接指定排序规则如果你只是写一个查询不想动表结构最直接的办法是在SQL里用COLLATE关键字显式指定比较双方的排序规则。语法很简单在要比较的其中一个字段后面加上COLLATE utf8mb4_unicode_ci让两边强制对齐。示例两张表分别用了utf8mb4_general_ci和utf8mb4_unicode_ci直接关联报错SELECT a.user_name, b.nick_name FROM user_account a JOIN user_profile b ON a.user_name b.nick_name;报1267后改成这样即可SELECT a.user_name, b.nick_name FROM user_account a JOIN user_profile b ON a.user_name b.nick_name COLLATE utf8mb4_unicode_ci;这里的关键是让优先级较低、或者你觉得不合理的那一侧加上COLLATE把比较基准统一到你指定的排序规则。两条经验如果不清楚哪边优先级高直接在两个字段上都加同一个排序规则绝对不会错。COLLATE字面量虽然写在某个字段后面但实际上是作用于整个比较操作不需要两个字段都写。除了JOINWHERE等值比较同理SELECT * FROM orders WHERE order_no SO20240301 COLLATE utf8mb4_unicode_ci;UNION也一样在需要对齐的列后面加COLLATESELECT name FROM employee UNION SELECT name FROM director COLLATE utf8mb4_unicode_ci;3.2 用CONVERT或CAST做显式转换COLLATE能解决排序规则冲突但前提是两个字段的字符集相同。如果字符集都不同比如一个是latin1一个是utf8mb4光用COLLATE是不够的因为utf8mb4_unicode_ci不能直接作用在latin1列上。这时需要用CONVERT把字符集也统一掉SELECT a.name, b.title FROM article a JOIN old_article b ON a.name CONVERT(b.title USING utf8mb4) COLLATE utf8mb4_unicode_ci;CONVERT(... USING ...)负责把字段从原来的字符集转成目标字符集后面的COLLATE再指定排序规则。这个操作会触发表达式计算所以如果字段上有索引索引会失效这一点后面会细说。也可以使用CAST写法略有不同效果类似SELECT * FROM t1 JOIN t2 ON t1.key CAST(t2.key AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci;我不太推荐写SQL时大量用这种写法因为可读性差后期维护的人看到一堆强转会崩溃。它更适合做临时应急排查确认问题之后还是应该用第4节的ALTER方案根治。3.3 关于排序规则选择的小建议临时解法里到底指定哪种排序规则不是随手选的。我建议遵循两个原则如果你完全不知道选哪个就用当前表里占据多数的那种排序规则改动面最小。如果你在建新库直接用utf8mb4_unicode_ci或utf8mb4_0900_ai_ci别再用general_ci了。utf8mb4_general_ci在MySQL 8.0里虽然还存在但官方已经明确不推荐新业务使用。性能上它可能略快一点点但排序严谨度和多语言支持都不如unicode_ci。而MySQL 8.0的utf8mb4_0900_ai_ci更是在性能和规则完备性上全面领先新项目直接选它是最稳的。不过要注意utf8mb4_0900_ai_ci是MySQL 8.0特有的排序规则如果你的主从环境或者上下游数据同步还在用5.7就别选这个否则同步任务可能直接失败。5.7能接受的最优选择是utf8mb4_unicode_ci。4. 解法二直接修改列、表、库的排序规则4.1 修改单列临时SQL只能救急如果同一张表经常参与跨库关联或JOIN建议直接修改字段的排序规则一劳永逸。修改单列的语法ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;注意MODIFY COLUMN必须带上完整的字段定义包括类型、长度、是否为空、默认值等。如果你只写类型和长度原来的NOT NULL、DEFAULT这些属性可能会丢失。我建议先SHOW FULL COLUMNS FROM your_table_name查看完整定义再照着填。假设原字段定义是user_name varchar(64) NOT NULL DEFAULT COMMENT 用户名完整的修改语句就应该是ALTER TABLE user_account MODIFY COLUMN user_name varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT COMMENT 用户名;如果只是单纯想改排序规则还可以用更简洁的方法先转换字符集再指定排序规则。但MODIFY COLUMN时如果同时写CHARACTER SET和COLLATEMySQL会为你做一次转换。4.2 修改整张表和整个库如果一张表里很多列都用了旧排序规则一列一列改太痛苦直接用CONVERT TO CHARACTER SET批量转换整张表ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这条命令会修改表默认字符集同时把表里所有字符串列都转换到新字符集和排序规则。注意它会改变列的数据存储如果某些列值里有特殊字符转换后可能发生变化最好在测试库先执行一次。整张表的索引也会自动重建所以如果表特别大比如上千万行这个操作耗时较长建议放在业务低峰期执行。另外一个风险是执行期间会持有元数据锁影响DML所以大表操作必须提前评估。如果要修改整个库的默认排序规则让以后新建的表和没显式指定排序规则的列都采用新规则ALTER DATABASE your_db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;但要注意这个命令只修改库的默认值已经存在的表的列不会自动改。所以你还需要对每张表再执行CONVERT TO CHARACTER SET或者单独改列。4.3 迁移中的坑我在处理跨版本迁移时被1267坑过一次。当时把一个MySQL 5.7的库导入到8.0导入时某些表是utf8mb4_unicode_ci某些还是老的utf8mb4_general_ci加上8.0默认排序规则变了导入后一关联查询就是1267。后来我总结了一套相对稳妥的迁移顺序导数据前先对源库执行统一的排序规则检查把不同排序规则的数量降到最低。在目标库执行ALTER DATABASE设置库级默认排序规则。导入数据后再对每张表执行CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci确保表级一致。最后检查视图、存储函数、触发器里有没有硬编码的排序规则或BINARY关键字。视图和存储过程里如果写了类似CAST(x AS CHAR CHARACTER SET gbk)的语句也要同步修改否则运行时照样报1267。5. 解法三从源头统一全局配置5.1 实例、库、表的默认值解决一次1267容易难的是让它别再出现。最彻底的办法是让整个MySQL实例在字符集和排序规则上保持统一。先看实例配置SHOW VARIABLES LIKE character_set_server; SHOW VARIABLES LIKE collation_server;这两个变量分别是服务器实例的默认字符集和默认排序规则。如果你的业务库统一使用utf8mb4_unicode_ci那实例层也应该改成它。在MySQL的配置文件Linux下通常是/etc/my.cnfWindows下是my.ini的[mysqld]段里加[mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci改完重启MySQL服务或SET GLOBAL临时生效。SET GLOBAL语法SET GLOBAL character_set_server utf8mb4; SET GLOBAL collation_server utf8mb4_unicode_ci;但SET GLOBAL只对后续新连接生效而且重启后丢失配置文件才是永久方案。建库时也顺手明确指定CREATE DATABASE your_db_name DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;哪怕该设置和实例默认值一样也建议写上。这样这个库的语义自包含别人接手时一眼就能看出业务意图。5.2 连接与客户端的设置1267还有一个隐蔽来源是客户端连接参数。尤其是Java应用如果连接串里指定了characterEncoding但MySQL服务端的初始化参数不一致JDBC驱动可能使用一套排序规则服务端使用另一套。我见过最典型的是jdbc:mysql://localhost:3306/db?useUnicodetruecharacterEncodingutf8这种老式写法在MySQL驱动5.x时代常见但MySQL 8.0驱动默认使用utf8mb4如果数据库表是utf8mb4_general_ci驱动和服务端隐式协商就可能出问题。建议连接串改成jdbc:mysql://localhost:3306/db?characterEncodingutf8mb4connectionCollationutf8mb4_unicode_ciconnectionCollation这个参数能显式指定连接层排序规则。如果你用的是ODBC、Python的pymysql或Go的go-sql-driver同样要找对等的字符集参数。另外提醒一点连接池里的旧连接往往不会自动应用新的SET NAMES改完配置后最好重启应用或等连接池老化后重建。5.3 离线数据导入时的处理从外部文件导入数据比如LOAD DATA INFILE或source执行SQL脚本也经常踩1267。原因很好理解脚本文件本身是UTF-8编码但当前连接的character_set_client是旧值MySQL把导入内容按照错误编码解释出现乱码还报错。正确姿势SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;然后在同一个会话里执行导入。如果是通过mysql命令行导入还可以指定默认字符集mysql -u root -p --default-character-setutf8mb4 your_db_name dump.sql执行前先检查dump文件里的字符集声明。我见过有些老dump文件开头写着SET NAMES latin1如果你目标库是utf8mb4这个SET NAMES就会在会话里强制切到latin1后续所有导入的表都会继承latin1。这种情况直接在导入前把dump文件里的SET NAMES latin1改成SET NAMES utf8mb4或者在导入后统一执行一次转换。6. 排查中的心得和实用技巧6.1 用EXPLAIN提前发现隐患1267报错通常在执行阶段才暴露测试环境数据量小、SQL简单可能没触发生产环境数据量大、SQL复杂一下子就崩了。我建议所有新上线SQL都过一遍EXPLAIN重点看Extra列里有没有Using temporary或Using filesort。虽然EXPLAIN不会直接显示字符集冲突但当你写出一个可能冲突的JOIN时优化器会选择使用临时表或文件排序这时候你就要警惕了。把EXPLAIN输出和SHOW FULL COLUMNS结果放到一起看能提前抓到大部分1267隐患。6.2 批量生成修改列的SQL如果你发现一个库里有几十张表、上百个字段的排序规则不一致手写ALTER TABLE不现实。我一般用一条查询把ALTER语句批量生成出来SELECT CONCAT(ALTER TABLE , TABLE_NAME, MODIFY COLUMN , COLUMN_NAME, , COLUMN_TYPE, CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, IF(IS_NULLABLE YES, NULL, NOT NULL), IF(COLUMN_DEFAULT IS NULL AND IS_NULLABLE NO AND EXTRA NOT LIKE %auto_increment%, DEFAULT , IF(COLUMN_DEFAULT IS NOT NULL AND EXTRA NOT LIKE %auto_increment%, CONCAT( DEFAULT , COLUMN_DEFAULT, ), )), IF(EXTRA ! , CONCAT( , EXTRA), ), ;) AS alter_sql FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db_name AND CHARACTER_SET_NAME latin1 AND TABLE_NAME IN (SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db_name);注意这类生成脚本只能当参考因为COLUMN_DEFAULT里如果有特殊字符、表达式默认值如CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP拼接可能出错。生成后逐条核对再执行别盲目复制粘贴。更稳妥的办法是先导成文本人工review一遍去掉明显不合理的语句再分批执行。6.3 大表修改的在线与离线选择大表ALTER在MySQL 5.7和8.0里可能有锁表问题。虽然8.0.12以后INSTANT算法支持部分操作在线完成但修改字符集、排序规则这类需要重建数据和索引的DDL通常还是要COPY或INPLACE耗时较长且占用额外空间。如果你必须在大表上改排序规则我建议采取分阶段方案先在测试环境用pt-online-schema-changePercona Toolkit在模拟生产数据规模下跑一遍观察耗时和锁情况。生产环境选低峰期先备份再执行在线DDL工具。如果库实在太大也可以考虑新建一张新结构的表用INSERT INTO ... SELECT迁移数据迁移完成后再原子重命名。实际项目里我在一个亿级表上直接ALTER TABLE ... CONVERT TO CHARACTER SET跑了将近25分钟业务在低峰期能扛过去但如果是高峰期肯定会有人来找你。所以大表操作前必须评估业务容忍窗口。6.4 彻底避免1267的建表习惯最后分享几个我坚持了很多年的习惯如果能从一开始就遵守1267根本不会出现在你面前建库时显式指定DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci不依赖实例默认值。建表时在ENGINEInnoDB后面跟上DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci。字符串列不指定CHARACTER SET让它继承表级设置减少列级差异化。所有应用连接串统一带上字符集和collation参数不靠服务端猜。跨库JOIN前先看information_schema确认两边排序规则一致。新增表、新增列纳入规范的SQL review范围用自动化脚本扫描DDL里的字符集差异。这几点看着简单但很多团队都栽在“刚开始没人管后面一堆债”的循环里。数据库的字符集和排序规则一旦形成历史包袱改起来成本非常高所以我宁可前三个月多花几分钟写全DDL也不想一年后熬夜处理1267。个人体会1267这类问题最磨人的不是你不会改而是你查不出到底哪两个字段在打架。只要把information_schema用熟、把SHOW FULL COLUMNS和SHOW CREATE TABLE当成日常排查工具绝大多数1267在五分钟内就能定位。如果这篇文章能帮你少走一次弯路那我花在这些字上的时间就值了。