MySQL 1267报错排查：字符集与排序规则冲突的根治指南-育师

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_ci`	MySQL 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 CHARSET=utf8mb4 COLLATE=utf8mb4_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连接串里characterEncoding=utf8只解决了客户端的字符集传递，没有直接决定排序规则。如果你发现连接层的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字面量虽然写在某个字段后面，但实际上是作用于整个比较操作，不需要两个字段都写。

除了JOIN，WHERE等值比较同理：

SELECT * FROM orders WHERE order_no = 'SO20240301' COLLATE utf8mb4_unicode_ci;

UNION也一样，在需要对齐的列后面加COLLATE：

SELECT 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和COLLATE，MySQL会为你做一次转换。

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.cnf，Windows下是my.ini）的[mysqld]段里加：

[mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_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?useUnicode=true&characterEncoding=utf8

这种老式写法在MySQL驱动5.x时代常见，但MySQL 8.0驱动默认使用utf8mb4，如果数据库表是utf8mb4_general_ci，驱动和服务端隐式协商就可能出问题。建议连接串改成：

jdbc:mysql://localhost:3306/db?characterEncoding=utf8mb4&connectionCollation=utf8mb4_unicode_ci

connectionCollation这个参数能显式指定连接层排序规则。如果你用的是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-set=utf8mb4 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-change（Percona Toolkit）在模拟生产数据规模下跑一遍，观察耗时和锁情况。
生产环境选低峰期，先备份，再执行在线DDL工具。
如果库实在太大，也可以考虑新建一张新结构的表，用INSERT INTO ... SELECT迁移数据，迁移完成后再原子重命名。

实际项目里，我在一个亿级表上直接ALTER TABLE ... CONVERT TO CHARACTER SET跑了将近25分钟，业务在低峰期能扛过去，但如果是高峰期，肯定会有人来找你。所以大表操作前，必须评估业务容忍窗口。

6.4 彻底避免1267的建表习惯

最后分享几个我坚持了很多年的习惯，如果能从一开始就遵守，1267根本不会出现在你面前：

建库时显式指定DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci，不依赖实例默认值。
建表时在ENGINE=InnoDB后面跟上DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci。
字符串列不指定CHARACTER SET，让它继承表级设置，减少列级差异化。
所有应用连接串统一带上字符集和collation参数，不靠服务端猜。
跨库JOIN前先看information_schema确认两边排序规则一致。
新增表、新增列纳入规范的SQL review范围，用自动化脚本扫描DDL里的字符集差异。

这几点看着简单，但很多团队都栽在“刚开始没人管，后面一堆债”的循环里。数据库的字符集和排序规则一旦形成历史包袱，改起来成本非常高，所以我宁可前三个月多花几分钟写全DDL，也不想一年后熬夜处理1267。

个人体会，1267这类问题最磨人的不是你不会改，而是你查不出到底哪两个字段在打架。只要把information_schema用熟、把SHOW FULL COLUMNS和SHOW CREATE TABLE当成日常排查工具，绝大多数1267在五分钟内就能定位。如果这篇文章能帮你少走一次弯路，那我花在这些字上的时间就值了。