Mysql Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

MySQL字符串比较bug：

 

select * from table_a a left join table_b b on a.field_a = b.field_b

 

error：

 

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

 

cause：两表编码方式不一致。

 

resolve：将比较等式的一边进行字符串转换，如：“CONVERT(a.field_a USING utf8) COLLATE utf8_unicode_ci”

 

select * from table_a a left join table_b b on CONVERT(a.field_a USING utf8) COLLATE utf8_unicode_ci = b.field_b

 

注：utf8_general_ci：校对速度快，准确度差。

      utf8_unicode_ci：准确度高，校对速度慢。