记录一次sql语句执行错误:
left join表会出现 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_croatian_ci,IMPLICIT) for operation '='错误.
这是由于表的编码格式不一致 例如 某个表的字符编码为utf8_general_ci而关联表的字符编码为utf8_croatian_ci导致当前错误!
永久的
解决方案: 让关联表的字符编码一致
1.修改表中所有字符编码
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8_general_ci;
2…修改表的字符编码
ALTER TABLE 表名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci;
此接口的
ON 后面加上 COLLATE utf8mb4_general_ci
解决方案实际应用
LEFT JOIN ( SELECT * FROM ( SELECT * FROM biz_task_queue ORDER BY create_date DESC LIMIT 10000 ) a GROUP BY relevance_id ) b ON a.id COLLATE utf8mb4_general_ci = b.relevance_id COLLATE utf8mb4_general_ci