当前位置：首页 > article >正文

表关联查询：utf8mb4_general_ci和utf8mb4_0900_ai_ci两种不同的校对规则在操作符‘=‘时混用了

article 2025/3/4 11:35:18

异常信息：

 Error querying database. Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' ###

### Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' ; uncategorized SQLException; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

解决方案：

校对规则（collation）决定了字符串比较和排序的方式。当两个字符串的校对规则不同时，MySQL可能无法正确比较它们，从而导致这样的错误。用户执行的是一个JOIN操作，涉及到多个表，尤其是tb_device和tb_region这两个表。根据错误提示，问题出现在某个ON子句中的列比较，可能是它们的字符集或校对规则不一致。

这个错误是由于在MySQL查询中，不同校对规则（collation）的列进行了比较导致的。以下是逐步解决方案：

1. 定位冲突的列

错误信息指出在=操作时混用了utf8mb4_general_ci和utf8mb4_0900_ai_ci两种校对规则。检查以下JOIN条件中的列：

cd.device_guid = cdb.device_guid       -- 检查 device_guid 列
cdb.business_id = cb.business_id       -- 检查 business_id 列
cdb.province = cr.ad_code              -- 检查 province 和 ad_code 列

2. 查看列的校对规则

对每个涉及的列执行以下SQL，确认其字符集和校对规则：

SHOW CREATE TABLE tb_device_base;
SHOW CREATE TABLE tb_region;

3. 统一校对规则

方法一：修改表结构（永久生效）

如果确认某些列的校对规则不一致，修改它们的定义：

ALTER TABLE tb_device_base 
MODIFY COLUMN province VARCHAR(255) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE tb_region 
MODIFY COLUMN ad_code VARCHAR(255) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

将冲突列的校对规则改为相同值（如统一为utf8mb4_0900_ai_ci）。

方法二：在查询中强制转换校对规则

无需修改表结构，直接在查询时指定校对规则：

SELECT ...
FROM tb_device_base cdb
LEFT JOIN tb_region cr ON 
  cdb.province = cr.ad_code COLLATE utf8mb4_0900_ai_ci  -- 强制统一校对规则
...

在ON子句中使用COLLATE关键字显式指定校对规则。

4. 检查数据库默认配置

确保相关表的默认字符集和校对规则一致：

-- 修改表的默认校对规则
ALTER TABLE tb_device_base 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;

-- 修改数据库默认校对规则
ALTER DATABASE your_database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci;