1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) ...

目录1.现象2.错误原因3.解决方案3.1.SQL 内临时统一字符集不用改表结构优先临时修复3.3.永久修改字段字符集根治问题3.3.修改数据库 / 连接字符集1.现象mysql数据库编写sql语句查询某项业务的时候发生报错错误如下描述为1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation find_in_set原始查询语句MySql中各种功能用sql语句实现总结( select 1 AS enterPower, 1 AS queryPower, 1 AS modifyPower, 1 AS deletePower, 1 AS exportPower from usertable where usernamebbb and rank IN (Administrator, GeneralAdmin) ) union ( select if (FIND_IN_SET(models, enterPower), 1, 0) AS enterPower, if (FIND_IN_SET(models, queryPower), 1, 0) AS queryPower, if (FIND_IN_SET(models, modifyPower), 1, 0) AS modifyPower, if (FIND_IN_SET(models, deletePower), 1, 0) AS deletePower, if (FIND_IN_SET(models, exportPower), 1, 0) AS exportPower, from usertable where usernamebbb and rank NOT IN (Administrator, GeneralAdmin) )2.错误原因1.参与FIND_IN_SET()运算的两个字段 / 字符串字符集与排序规则不一致一边字符集utf8mb4_0900_ai_ci另一边字符集latin1_swedish_ci2.MySQL 无法对两种不同校对规则的文本直接做比较运算因此抛出非法混合校对集异常。 常见场景一个字段是 utf8mb4另一个字段是 latin1或是传入的常量字符串和字段字符集不匹配。3.解决方案3.1.SQL 内临时统一字符集不用改表结构优先临时修复使用CONVERT( ... USING charset)强制把其中一边转为和另一边一致的字符集。 示例针对 FIND_IN_SETFIND_IN_SET( CONVERT(待匹配字符串 USING utf8mb4), 目标字段 )完整写法示例WHERE FIND_IN_SET(CONVERT(xxx USING utf8mb4), t.col)3.3.永久修改字段字符集根治问题把 latin1 字段统一改成 utf8mb4ALTER TABLE 表名 MODIFY 字段名 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;或用数据库表编辑软件修改即可。3.3.修改数据库 / 连接字符集连接数据库时指定字符集jdbc:mysql://xxx:3306/db?useUnicodetruecharacterEncodingutf8mb4补充说明COERCIBLE/IMPLICIT是 MySQL 字符集优先级标记只要两边校对规则不一样就会触发此报错。所有参与字符串对比、FIND_IN_SET、LIKE、的字段必须保证字符集与 collation 完全一致。