MySQL collation 不一致导致 JOIN 报错怎么统一

两个表的编码都是中文，JOIN 却报 Illegal mix of collations，通常不是数据本身坏了，而是比较两侧的排序规则无法自动选出共同规则。先查字段级定义，再在查询级临时统一；确认业务语义后，最后把字段改成同一组 CHARACTER SET 和 COLLATE。只在连接条件里盲目加转换，可能让索引失效，也可能把大小写敏感的业务规则改掉。

先确认到底是哪一侧的 collation 不一致

先看参与连接的真实字段定义，不要只看数据库默认值。字段可能在建表时继承过旧表默认值，后来数据库默认规则已经变化。

-- 查看字段级排序规则、类型和索引信息
SHOW FULL COLUMNS FROM customer;
SHOW FULL COLUMNS FROM order_customer;

-- 只筛选本次 JOIN 需要的两列，便于对照
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
       CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND ((TABLE_NAME = 'customer' AND COLUMN_NAME = 'customer_code')
    OR (TABLE_NAME = 'order_customer' AND COLUMN_NAME = 'customer_code'));

如果两列的字符集不同，问题不只是 collation 名称不同；一个字符集只能使用与它关联的排序规则。即使都是 utf8mb4，utf8mb4_unicode_ci 与 utf8mb4_0900_ai_ci 的比较规则仍可能不同。先把差异写下来，再决定统一方向。

查询级修复：让 JOIN 两侧使用同一规则

如果线上查询需要先恢复，可以把同一个排序规则写在比较表达式两侧。下面用 utf8mb4_unicode_ci 作为示例；生产环境应换成两列都支持、并且符合业务比较语义的规则。

-- 临时统一比较规则；两列已经是 utf8mb4 时可直接使用
SELECT o.order_id, c.customer_name
FROM order_customer AS o
JOIN customer AS c
  ON o.customer_code COLLATE utf8mb4_unicode_ci
   = c.customer_code COLLATE utf8mb4_unicode_ci;

-- 字符集也不一致时，先转换字符集，再指定对应 collation
SELECT o.order_id, c.customer_name
FROM order_customer AS o
JOIN customer AS c
  ON CONVERT(o.customer_code USING utf8mb4) COLLATE utf8mb4_unicode_ci
   = CONVERT(c.customer_code USING utf8mb4) COLLATE utf8mb4_unicode_ci;

第一种写法适合两列字符集已经一致、只是排序规则不同的情况。第二种写法能处理字符集不一致，但表达式包住列后，优化器未必能直接使用原索引。用 EXPLAIN 看访问类型和实际扫描量，不要因为“不报错”就认为已经适合长期使用。

长期修复：统一字段定义而不是重复写 COLLATE

先选定规范。例如系统已全面使用 utf8mb4，并且业务不要求区分大小写，可以将两列统一到同一排序规则。变更前确认列长度、索引前缀、数据长度和锁表影响。

-- 先确认目标 collation 在当前实例可用
SHOW COLLATION LIKE 'utf8mb4_unicode_ci';

-- 低峰期分别统一两张表的连接字段
ALTER TABLE order_customer
  MODIFY customer_code VARCHAR(64)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE customer
  MODIFY customer_code VARCHAR(64)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

不要直接照抄 NOT NULL、长度或默认值；它们必须和原字段定义一致，否则这次只是修 collation，却顺手改变了可空性或长度。对于大表，先查看变更策略和维护窗口，再分批迁移或使用经过验证的在线变更方案。

用回归查询确认语义没有被改掉

统一完成后，至少检查四类数据：普通相等值、大小写只差一个字母的值、包含重音的值、末尾带空格的值。不同 collation 对这些边界的处理并不相同，不能只拿一条中文样例判断修复成功。

-- 对比统一前后可能受影响的键，避免静默增加匹配行
SELECT customer_code, COUNT(*) AS row_count
FROM customer
GROUP BY customer_code COLLATE utf8mb4_unicode_ci
HAVING COUNT(*) > 1;

-- 确认连接会话使用的字符集与排序规则
SELECT @@character_set_connection,
       @@collation_connection;

如果应用使用连接池，连接参数也要和表结构规范一致。数据库默认值、表默认值和列默认值不是同一层级；只改数据库默认值，不会自动重写已经存在的列。

相关问题

只给一侧写 COLLATE 可以吗？

可以，MySQL 会按表达式规则处理，但为排障和代码审查清晰起见，JOIN 两侧显式写同一个目标规则更直观。长期方案仍是统一列定义。

utf8mb4_unicode_ci 和 utf8mb4_0900_ai_ci 该选哪个？

不能只按名称选择。先确认服务器版本与可用列表，再按大小写、重音和排序需求用代表性数据比较；还要考虑旧实例是否支持目标规则。

改完 collation 后索引会自动恢复吗？

字段定义一致有利于优化器直接比较列，但变更可能重建索引，查询仍需用 EXPLAIN 验证。查询里继续对索引列做转换，仍可能带来额外扫描。