应直接修改字段的字符集和排序规则,而非在JOIN的ON子句中使用CONVERT或COLLATE;因后者无法走索引、易静默丢数据,且优化器不识别。需用SHOW FULL COLUMNS或查询information_schema.COLUMNS比对两表关联字段的collation_name是否完全一致,并通过ALTER TABLE MODIFY同时指定CHARACTER SET和COLLATE一步到位。

直接改字段定义,别在ON里套CONVERT或COLLATE——后者几乎不走索引,只是看起来能跑通。
怎么一眼确认是字符集或校对规则(COLLATION)不一致?
别看数据库默认值,查字段本身定义:
- 用
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'; - EXPLAIN 显示被驱动表的
type是ALL、key是NULL,但单表查询飞快——八成就是这个原因
为什么在ON子句里写CONVERT或COLLATE没用?
它语法能过,但优化器根本不会用索引:
-
ON t1.name = CONVERT(t2.name USING utf8mb4):转换发生在执行时,t2.name上的索引无法命中 -
ON t1.name COLLATE utf8mb4_0900_as_cs = t2.name COLLATE utf8mb4_0900_as_cs:MySQL 会在解析阶段推导隐式规则,只要原始字段collation_name不同,就大概率拒绝走索引 - 这类写法还可能静默丢数据(比如
CONVERT遇到非法字节变成?),或让后续ORDER BY强制走Using filesort
真正该做的:ALTER TABLE MODIFY 同时指定 CHARACTER SET 和 COLLATE
必须一步到位,只改其中一个等于白干:
- 先确认目标
COLLATION(比如跟主表保持一致):utf8mb4_0900_as_cs - 执行:
ALTER TABLE t2 MODIFY user_id VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;
- 改完立刻用
SHOW CREATE TABLE t2确认字段定义已更新——SHOW FULL COLUMNS可能有缓存偏差 - 大表操作会锁表重建索引;MySQL 8.0+ 满足条件可加
ALGORITHM=INPLACE减少影响
容易被忽略的连接层和边界场景
就算表字段全统一了,照样可能崩:
- 检查当前连接的
@@collation_connection:如果它是latin1_swedish_ci或utf8mb4_unicode_ci,SQL 中的字面量(如'张三')仍按错误规则解释,JOIN 条件在解析前就失真 - 外键字段也得同步处理:否则建表或插入时直接报
ERROR 1005: Can't create table - UNION 所有列、全文索引字段、临时表默认字符集,都受同一套规则约束——这些地方往往不在日常 JOIN 语句里显式出现,但一触发就全线告警


















