Mysql字符集排序规则不一致造成的关联查询错误-Illegal mix of collations

问题描述

如果mysql两个表的两个字段定义的字符集排序规则不一致，如下:

一个默认：utf8mb4_general_ci，一个指定：utf8mb4_unicode_ci

当进行两个表这两个字段关联查询时，如下：

SELECT DISTINCT t2.id FROM a t1 JOIN b t2 ON t1.group_id = t2.group_id
WHERE t1.id = 1;

会报如下错误：

[Err] 1267 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

原因就是不同字符集排序规则不能进行join查询。

解决办法

1、sql中统一排序规则，如：

SELECT DISTINCT t2.id FROM a t1 JOIN b t2 ON t1.group_id = t2.group_id COLLATE utf8mb4_unicode_ci
WHERE t1.id = 1;

但是这样会导致该字段上的索引失效（慎用）

2、修改排序规则，使两个表的字段排序规则一样

如：

ALTER TABLE a MODIFY `group_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL;