问题描述
在测试一个刚写好的rest controller时,后台mybatis报出了一个错误,错误消息如下:
### Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
问题分析
非法混用的collation!将sql语句弄到MySQL Workbench客户端工具里跑了下,结果报出同样的错误,看来是SQL语句的问题。看到utf8mb4这个关键字,想到是字符集出了问题,于是根据SQL语句分别查看了两个表的字符集。
简略的SQL语句如下:
select ja.pay_type as ja_act_pay_status,ja.act_name as ja_act_name
from jo_activity_member bt
left join jo_wechat_pay jwp on bt.prepay_id = jwp.id_
分别查看两个表的结构(右击表 -> Copy to Clipboard -> Create Statement),发现=号所连接的两个字段字符集有所差别:
-- jo_activity_member的prepay_id字段定义为
`prepay_id` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
-- jo_wechat_pay表的id_字段定义为
`id_` varchar(32) NOT NULL
prepay_id字段的collate被指定为了utf8mb4_unicode_ci,而id_列没有指定任何collate,猜测应该默认为utf8mb4_general_ci,字符集不同所以比较时发生错误。
为证实这一点,我在本地建立了两个表,字段的字符集不同,尝试使用=号相连时,报出了同样的错误:
![字符集不一致引发的mybatis错误](http://p2.ttnews.xyz/loading.gif)
解决方案
既然需要相同的字符集,那么改掉其中一个是不是应该就可以了?
ALTER TABLE jo_activity_member CHANGE prepay_id prepay_id varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
将prepay_id字段的collate由utf8mb4_unicode_ci改为了utf8mb4_general_ci,理由是这个字段值没有特殊符号,不存在精准比较的问题,使用general_ci比较排序较快,id_作为主键,关联的情况可能性比prepay_id更大,改动的话可能引发别的地方报错。
再次测试SQL语句,发现一切正常,看来这个方法是可行的。
后续
使用如下SQL语句发现数据库的字符集很乱,utf8,utf8mb4,general_ci,unicode_ci等
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
![字符集不一致引发的mybatis错误](http://p2.ttnews.xyz/loading.gif)
混乱的字符集
历史遗留问题,如果彻底解决字符集,需要停止SQL服务,备份数据,重新建库建表,统一字符集。这也让我们反思在设计之初就应该考虑好字符集这种基础问题,越晚改动成本也越大。
閱讀更多 京京肚肚擼代碼 的文章