lq920320/blogs

2018/05/12--【Mysql(SQL语句)】当两个表的字符集不同时的关联方法

lq920320 opened this issue · 0 comments

某天,在我执行某句关联查询的SQL的时候,突然报了一个如下错误,然后找到了解决的方案,在这里记录一下。

【执行的SQL】SELECT a.id, a.user_num, b.name FROM tablaA a LEFT JOIN tableB b on a.user_num = b.user_id;

【错误信息】Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

【错误分析】错误信息显示由于非法的字符排序规则(collations )混用而不能进行'='操作,那么就应该是一张表的collations是‘utf8_unicode_ci’,而另一张表的collations是‘utf8_general_ci’,那么要做的就是统一两张表的collations。

【更改后的SQL】SELECT a.id, a.user_num, b.name FROM tablaA a LEFT JOIN tableB b on a.user_num COLLATE utf8_general_ci = b.user_id COLLATE utf8_general_ci;,在SQL关联的字段上加上统一的collations即可,比如COLLATE utf8_general_ci

【注】utf8_general_ci和utf8_unicode_ci的区别,前者校对速度快,但准确度稍差;后者准确度高,但校对速度稍慢。

参考:
https://stackoverflow.com/questions/45621178/illegal-mix-of-collations-utf8-unicode-ci-implicit-and-utf8-general-ci-implic?rq=1

https://stackoverflow.com/questions/11770074/illegal-mix-of-collations-utf8-unicode-ci-implicit-and-utf8-general-ci-implic

https://dba.stackexchange.com/questions/24587/mysql-illegal-mix-of-collations

https://blog.csdn.net/abandonship/article/details/46859043