案例 Illegal mix of collations（1267/1271）：用 COLLATION() 定位比较两侧的真实来源

2026-10-17 00:03:36

Illegal mix of collations（1267/1271）：用 COLLATION() 定位比较两侧的真实来源

报错长什么样

ERROR 1267 (HY000): Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='

MySQL 8.0 的视图场景里，常见的是 utf8mb4_general_ci 撞上 utf8mb4_0900_ai_ci：

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

MariaDB 走的是另一个错误码 1271，多出现在 UNION 两表同名列 collation 不一致时：

Error 1271 ER_CANT_AGGREGATE_NCOLLATIONS: Illegal mix of collations for operation '%s'

连接与表 collation 不同同样会触发，例如 Matomo + MariaDB 11.5.2 升级后的这条：

Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE)

character set 与 collation

character set 是可用字符的“字母表”，collation 决定字符的排序与相等关系。同一字符集下的不同 collation，会影响 ORDER BY、GROUP BY 与等值检索的结果，比较时也需要先把两侧的规则消解成一个。

MySQL 用 coercibility 消解冲突

MySQL 靠 coercibility（可强制性）解析歧义：

取 coercibility 值最小的一方
两侧 coercibility 相同时：若双方都是 Unicode 或都不是 Unicode → 报错；若一方 Unicode、一方非 Unicode → Unicode 一方胜出，非 Unicode 侧自动转换
Illegal mix of collations 就是两侧 collation 不同、coercibility 相等、规则无法消解的结果

coercibility 赋值规则：

显式 COLLATE 子句 = 0（完全不可强制）
两个不同 collation 字符串的拼接（CONCAT）= 1
列 / 存储过程参数 / 局部变量 = 2
系统常量（USER() / VERSION() 返回值）= 3
字面量 = 4
NULL 或由 NULL 派生的表达式 = 5

报错文本里的 IMPLICIT 表示该 collation 来自列元数据之类的隐式来源，EXPLICIT 表示来自 COLLATE 子句。

MariaDB 的 derivation 等级

MDEV-25829 计划把 Unicode 默认 collation 从 xxx_general_ci 换成 uca1400_ai_ci，同时把 derivation 细化：

DERIVATION_IGNORABLE=7（显式 NULL）
DERIVATION_COERCIBLE=5（字面量、字符串用户变量）
DERIVATION_CAST=4（CAST(expr AS CHAR)、CONVERT(... USING cs)，从 IMPLICIT 调整而来）
DERIVATION_SYSCONST=3（元数据函数如 user() / database()）
DERIVATION_IMPLICIT=2（表列、SP 变量）
DERIVATION_NONE=1（两个不同 collation 混合，如 CONCAT）
DERIVATION_EXPLICIT=0（显式 COLLATE 子句）

默认值从 10.6/11.4 的 general_ci 切到 uca1400_ai_ci 之后，会多出两类新冲突：非默认 collation 的列与 CAST/CONVERT 结果比较，以及非默认的列与字符串用户变量比较。INFORMATION_SCHEMA 的列用的是 utf8mb3_general_ci。MDEV-35041 记录的就是“即使使用默认服务端设置，简单比较也会触发 Illegal mix of collations”。

服务端变量 @@character_set_collations（11.2 引入）可以显式设置，例如 'utf8mb3=uca1400_ai_ci'。

排查：先确定两侧是什么

1) 用 COLLATION() 看比较两侧的表达式

SELECT COLLATION(column_name) FROM Table_Name LIMIT 1;
SELECT COLLATION('AAA');
SELECT COLLATION(convert(date_format(curdate(),'%Y%m%d') using utf8mb4));

2) 查列的字符集与 collation

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('customer_entity','vendor_mapping') AND COLUMN_NAME IN ('email','external_email');

3) 查连接层

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SELECT @@character_set_connection, @@collation_connection;

CLI 客户端、PHP PDO、复制任务可能各用一套连接字符集。只在 Web 请求报错、CLI 不报时，先对比两个连接，不要一上来就整库转换。

4) 表的默认 collation 只影响新列

已有列可能仍是旧值，SHOW CREATE TABLE 和 information_schema 都要看。

修法与代价

临时加 COLLATE 能让单条 SQL 通过：

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

但直接给表中字段做转换可能让索引失效，一般优先修改常量一侧：

SELECT ... WHERE (column_name = convert('AAAAA' using utf8mb4) collate utf8mb4_general_ci) ...

collation 必须属于对应字符集，否则报 ERROR 1253：

COLLATION 'latin1_general_cs' is not valid for CHARACTER SET 'utf8mb4'

字面量可以用字符集前缀指定，例如 _latin1'Herong' COLLATE latin1_general_cs。

改 collation 前先查等价冲突：

SELECT LOWER(email), COUNT(*) FROM customer_entity GROUP BY LOWER(email) HAVING COUNT(*)>1;

大小写/重音敏感性一旦变化，原本不同的值可能被判成重复，唯一索引会因此建不上，或已有数据被判重复。

ALTER TABLE ... CONVERT TO CHARACTER SET 会重建大表、持锁，并带来磁盘与复制压力，务必先在副本或预发布环境验证并设计回滚方案。

根治方向是让数据库默认值、现有列、应用连接与自定义 schema 使用一致的、受版本支持的 collation；同时修复写入 module declarative schema / patch 的地方，避免下一次部署重新建出冲突表。

另外，default_collation_for_utf8mb4 只影响 SHOW COLLATION / SHOW CHARACTER SET 的显示，不改变字符集默认排序规则，因此改它并不能修复 convert 产生的冲突（MySQL 8.0 场景）。

参考