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";

显示结果为

mysql 错误:1267 - Illegal mix of collations
然后,我想看看我查询所用到的表的字符集和数据库的字符集是否一致,我使用语句

show table status like 'xxxx'

mysql 错误:1267 - Illegal mix of collations

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

SHOW FULL COLUMNS FROM a

mysql 错误:1267 - Illegal mix of collations

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

SHOW FULL COLUMNS FROM b

mysql 错误:1267 - Illegal mix of collations
如上所示,现在问题找到了,就是字段的字符集编码不一致.

解决办法

原因找到后,就开始解决了。我找到了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 协议》,转载必须注明作者和本文链接

人生的意义在于承担人生无意义的勇气

讨论数量: 1

直接执行DDL由于MDL锁的存在可能会有坑。

3周前 评论
阿隆索逃跑了 (楼主) 3周前

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!