关于SQLException: Illegal mix of collations (`utf8mb4_general_ci,IMPLICIT`)...错误

希望文章能给到你启发和灵感～
如果觉得文章对你有帮助的话，点赞 + 关注+ 收藏 支持一下博主吧～

开篇说明

记录一个查询错误
场景：数据库之间某表复制移动；复制源数据库能正常使用，复制后的数据库在进行联表查询时出现的关于数据库排序规则相关的异常报错；

一、基础环境说明

考虑环境因素，大家适当的对比自己的软硬件环境情况分析～请仔细阅读硬件、软件环境

1.1 硬件环境

MacOS Monterey 版本 12.6.8 Apple M1

1.2 软件环境

开发工具：Idea 2021，navicat 15
编译环境：Java 1.8.0_171,mybatis 3.0+
数据库：mysql8.0

二、报错信息

Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation ‘=’
; uncategorized SQLException for SQL []; 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 ‘=’

从异常错误信息，能定位到问题出错的点在于sql查询，错误相关是排序规则的问题；
【1】首先确认我们的数据库两边这块的配置是否是一致的；

【2】定位到我们的查询语句是哪句SQL；例如我这里是涉及多张表的联表查询；

SELECTa.id AS "id",a.create_by AS "createBy.id",a.create_date AS "createDate",a.update_by AS "updateBy.id",a.update_date AS "updateDate",a.remarks AS "remarks",a.del_flag AS "delFlag",a.materialId AS "materialId",m.model AS "model",a.machineNo AS "machineNo",m.materialname AS "materialName",m.materialcode AS "materialCode",m.voltage AS "voltage",m.unit AS "unit",s.status AS "status",a.isWaste AS "isWaste",a.maintenance_time AS "maintenanceTime",a.maintenance_content AS "maintenanceContent"FROM ossc_maintenance aINNER JOIN ossc_material m ON (m.id = a.materialId)INNER JOIN ossc_stock_detail s ON (a.machineNo = s.machine_no)

通过命令查询两个数据库的相关表是否有存在数据排序的规则的冲突和不同；然后我们主要用命令修改有区别的表的排序规则即可；

// 查询所有与排序规则（collation）相关的系统变量
show variables like 'collation_%';
// 查看某个表（假设表名为table_name）的创建语句，包括其字符集（character set）和排序规则（collation）等详细信息
show create table ossc_stock_detail;
// 修改了表的默认字符集和校对规则，并转换了表中所有列和数据的字符集和校对规则
ALTER TABLE ossc_material convert to CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

三、最后

【1】show variables like ‘collation_%’; 该命令在不同版本mysql或配置中返回的可能不同；如我这里是返回：

collation_connection：代表当前连接的排序规则。
collation_database：代表当前数据库的默认排序规则。
collation_server：代表服务器的默认排序规则。

其他可能的返回值：
character_set_connection：代表当前连接的字符集。
character_set_database：代表当前数据库的默认字符集。
character_set_results：代表查询结果的字符集。
character_set_server：代表服务器的默认字符集。
character_set_system：代表系统元数据的字符集（通常是utf8，且不可更改）。
collation_client：代表客户端的排序规则，这通常与character_set_connection和collation_connection相对应

【2】在做数据库的复制移动时，要注意两个库之间的排序规则和字符集；