mysql的字符集分为库、表、字段3个层级，带来不少困扰。

一、问题描述

真是搞死了。因为字段的字符集不同，表连接进行比较会报错：

Error Code: 1267. Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

类似问题其实之前已经遇到过（​​mysql union语句报错：Error Code: 1271. Illegal mix of collations for operation ‘UNION‘​​​）。本以为这只会发生在使用union，不同的表、字段合并返回才会出现这种问题，但今天有同事反映，在表连接进行比较时也会报错。
就是这种SQL：

SELECT r.* ,b.NAME as clubName ,e.VTEXT,s.VTEXT
FROM work_credit_review r
left JOIN work_club b on(r.club_id = b.id)
LEFT JOIN sys_fieldvalue e on(e.VCODE = r.Hope_level)
LEFT JOIN sys_fieldvalue s on(s.VCODE = b.Credit_rating) -- 出错元凶，两个字段字符集不一致
where e.FKEY = 'creditRating' and s.FKEY = 'creditRating';

mysql的字符集分为库、表、字段3个层级。三者的字符集设置是继承关系，但又可以自己指定，自己指定的优先级较高。这本来没什么问题，但mysql的workbench在修改表结构时，有时字段的字符集显示为default，却并不一定代表继承了表设定。比如：

表的字符集设置：

mysql字符集问题_1267


字段的字符集设置显示为default：

mysql字符集问题_字段_02


实际上是这个字符集，与表不相符：

mysql字符集问题_collations_03

二、解决办法

方法一：修改字符集

ALTER TABLE sys_fieldvalue CHANGE VCODE VCODE varchar(20) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';

参考文章：
​​​mysql修改表、字段、库的字符集​​

方法二：比较时指定字符集

SELECT r.* ,b.NAME as clubName ,e.VTEXT,s.VTEXT
FROM work_credit_review r
left JOIN work_club b on(r.club_id = b.id)
LEFT JOIN sys_fieldvalue e on(e.VCODE = r.Hope_level)
LEFT JOIN sys_fieldvalue s on(s.VCODE = b.Credit_rating COLLATE utf8mb4_unicode_ci
) -- COLLATE utf8mb4_unicode_ci
where e.FKEY = 'creditRating' and s.FKEY = 'creditRating';

三、查看字符集

可以使用命令，也可以在mysql 的workbench里看。

右击表，选中 Table Maintenance…

mysql字符集问题_collations_04


双击指定表

mysql字符集问题_字段_05


columns

mysql字符集问题_mysql_06