MySQL执行NOT IN查询时出现非法排序规则混合错误（排序规则已一致）

这种情况我之前踩过坑，明明检查表和列的排序规则都一致，还是报这个错，别着急，咱们一步步来排查：

1. 先统一查询里的表名/字段名大小写 #

如果你的MySQL跑在Linux/Unix环境下，默认是区分表名大小写的（由lower_case_table_names参数控制）。你当前的查询里写了SELECT ID FROM XYZ，但实际表名是小写的xyz，这时候MySQL可能会在内部处理时出现隐性的元数据读取问题，甚至误创建临时表导致排序规则不匹配。

先把查询改成和实际表结构一致的大小写试试：

SELECT name FROM abc WHERE id NOT IN (SELECT id FROM xyz);

2. 确认id列的实际数据类型完全一致 #

虽然你说两张表的id都是INT(3)，但还是要实际验证——毕竟有时候表结构可能被修改过自己没注意到。执行这两条命令查看表结构：

DESCRIBE abc;
DESCRIBE xyz;

仔细对比id列的类型，如果其中一个是VARCHAR或者其他字符类型，MySQL在比较时会做隐性类型转换，这时候字符串类型的排序规则就会和INT类型的转换结果冲突，触发这个错误。

3. 检查列级别的排序规则（针对字符类型场景） #

INT类型本身是没有排序规则的，但如果你的id列实际是字符类型，一定要确认列的排序规则完全一致。执行以下命令查看列的具体排序规则：

SELECT COLUMN_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME IN ('abc', 'xyz') AND COLUMN_NAME = 'id';

如果是字符类型，必须保证两者的COLLATION_NAME都是utf8_unicode_ci。

4. 强制类型转换解决隐性冲突 #

如果上面的排查都没问题，那可以试试在子查询里显式转换id的类型，强制两边的比较类型一致：

-- 用CAST转换为无符号整数
SELECT name FROM abc WHERE id NOT IN (SELECT CAST(id AS UNSIGNED) FROM xyz);

-- 或者用CONVERT函数
SELECT name FROM abc WHERE id NOT IN (SELECT CONVERT(id, UNSIGNED) FROM xyz);

5. 检查数据库级别的默认排序规则 #

有时候表的默认排序规则是对的，但数据库的默认排序规则不同，子查询生成临时表时可能会使用数据库的默认规则，导致冲突。执行这条命令查看数据库的默认排序规则：

SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMATA 
WHERE SCHEMA_NAME = '你的数据库名称';

如果不是utf8_unicode_ci，可以考虑修改数据库的默认排序规则（注意提前备份数据），或者在查询里显式指定排序规则。

内容的提问来源于stack exchange，提问作者Allan Fernandes