如何解决SQL关联查询时因为字符集不一致导致的JOIN失效？

MySQL JOIN性能骤降的主因是JOIN字段的collation_name不一致，导致优化器弃用索引，表现为EXPLAIN中key为NULL、type为ALL；须通过information_schema.COLUMNS比对并用ALTER TABLE MODIFY同时指定CHARACTER SET和COLLATE修正，且需统一连接层collation。

EXPLAIN 显示 key 为 NULL 且 type = ALL，基本就是 COLLATION 不一致

只要 JOIN 字段的 collation_name 不完全相同，MySQL 优化器就会放弃索引——不是性能差，是直接不走。典型表现：两表都有 INDEX，但 EXPLAIN 里被驱动表的 key 是 NULL，type 是 ALL 或 index，哪怕只查几万行也秒变分钟级。

别靠经验猜，直接查：

• 用 SHOW FULL COLUMNS FROM t1 LIKE 'user_id' 和 SHOW FULL COLUMNS FROM t2 LIKE 'user_id'，比对两行的 Collation 列值是否**一字不差**（比如 utf8mb4_0900_as_cs ≠ utf8mb4_unicode_ci）
• 更省事：查 information_schema.COLUMNS 一次性对比：

  SELECT table_name, column_name, character_set_name, collation_name FROM information_schema.COLUMNS WHERE table_name IN ('t1', 't2') AND column_name = 'user_id';

• 注意：即使 character_set_name 都是 utf8mb4，只要 collation_name 不同，照样失效

ALTER TABLE MODIFY 才是正解，ON 子句里加 COLLATE 没用

在 ON 里写 t1.name = t2.name COLLATE utf8mb4_0900_as_cs 或 CONVERT(t2.name USING utf8mb4)，语法能过，但索引照旧失效。因为这些是运行时转换，优化器无法下推，t2.name 上的索引根本不会被命中。

真正要改的是字段定义本身：

• 必须同时指定 CHARACTER SET 和 COLLATE，缺一不可：

  <pre class="brush:php;toolbar:false;">ALTER TABLE t2 MODIFY user_id VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

• 不能只写 ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4——它只改表默认值，不碰已有字段的 <code>COLLATE
• 改完立刻执行 SHOW CREATE TABLE t2 确认字段定义已更新，别信 SHOW FULL COLUMNS 的缓存显示
• 大表操作会锁表重建索引，MySQL 8.0+ 若满足条件可加 ALGORITHM=INPLACE 减少影响

连接层字符集不统一，改表也白搭

就算所有表字段都改成 utf8mb4_0900_as_cs，如果应用连上来时 @@collation_connection 是 utf8mb4_unicode_ci 或更糟的 latin1_swedish_ci，SQL 中的字面量（比如 '张三'）仍会被按错误规则解释，JOIN 条件在解析前就失真。

验证当前连接状态：

SELECT @@collation_connection, @@character_set_client;

应用侧必须显式对齐：

• JDBC 连接串加上：useUnicode=true&characterEncoding=utf8mb4&collationConnection=utf8mb4_0900_as_cs
• PHP mysqli 连接后执行：mysqli_set_charset($conn, 'utf8mb4'); 再执行 SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_cs;
• 验证：SELECT COLLATION('test'); 返回值应与字段 Collation 完全一致

外键、UNION、全文索引字段同样受影响

这些地方不显式出现在 JOIN 语句里，但底层照样做字符串比对：

• 外键约束字段 COLLATION 不一致，建表或插入时直接报 ERROR 1005: Can't create table
• UNION 结果集要求所有列的字符集和 COLLATION 完全一致，否则报 Illegal mix of collations
• 全文索引字段参与 MATCH ... AGAINST 时，COLLATION 不匹配会导致匹配失效或结果异常
• 这些字段得和 JOIN 字段一起批量检查、统一修改，漏一个就可能崩

真正容易被忽略的是：问题往往藏在“不常 JOIN”的字段里——比如历史遗留的外键列、冷门 UNION 子查询里的别名字段，它们没在慢查询日志里暴露，却在关键时刻拖垮整个链路。