MySQL 错误：1267 - Illegal mix of collations

事情起因

今天在一个统计功能时候，因为用到联表查询语句时候mysql报错了，错误提示为

1267 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

根据错误提示，推断可能是由于两个表的字符集不一致导致的，接下来我开始了一步步的追踪

分析过程

1.首先，我希望查看一下数据库的字符集，我使用了语句

show variables like "collation_database";

显示结果为

然后，我想看看我查询所用到的表的字符集和数据库的字符集是否一致，我使用语句

show table status like 'xxxx'

这样看来表和数据库字符集编码应该是相同的。
2.既然表和数据库字符编码一致，那么应该是联合查询的表的字段字符集不一样吧，带着着这样的疑问我继续追踪。
使用sql语句查询一下其中一个表a的所有字段

SHOW FULL COLUMNS FROM a

在查询另一个表b的所有字段

SHOW FULL COLUMNS FROM b

如上所示，现在问题找到了，就是字段的字符集编码不一致.

解决办法

原因找到后，就开始解决了。我找到了3种办法解决
1.改变表字段的编码，使两个表的字段编码保持一致。（如果是联合查询一般更改语句 on xx.sn=xxx.sn 中 sn 字段）
2.如果没有权限更改表那么可以考虑用 CONVERT 如下

CONVERT(xxx.sn USING utf8) COLLATE   utf8_general_ci=xxxx.sn

3.也可以把两个字段转为二进制用

BINARY xxx.sn=BINARY xxxx.sn

方法点评

方法1 最为直接。（这就要求我们创建数据库和表的时候要注意，保持字符集编码一致真的很重要！！）
方法2 对于数据量较大的表不太适用，因为索引有可能没法用到，导致查询速度很慢。
方法3 对于字符类型的字段很好用，但是如果字段是数字类型字符串（比如98765432334当做字符串来存到表的字段）则有可能得不到想要的结果。

我按照第1中方法改的（本人集运维开发与一身，公司穷，请不起运维），如果有更好的办法，欢迎大家和我交流！

本作品采用《CC 协议》，转载必须注明作者和本文链接