Illegal mix of collations (XXX) and (XXX) for operation ‘XXX’

问题描述：

mybatis使用INSTR的时候报错（数据库为MySQL）—— Illegal mix of collations (XXX) and (XXX) for operation 'XXX’
– Cause: java.sql.SQLException: Illegal mix of collations (utf8_bin,NONE) and (utf8mb4_general_ci,COERCIBLE) for operation ‘instr’
– uncategorized SQLException; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_bin,NONE) and (utf8mb4_general_ci,COERCIBLE) for operation ‘instr’; nested exception is java.sql.SQLException: Illegal mix of collations (utf8_bin,NONE) and (utf8mb4_general_ci,COERCIBLE) for operation ‘instr’

---

原因分析：

排序规则不同导致的

---

解决方案：

1、查询SQL语句中使用到的数据表和字段的排序规则

-- 查询数据表的排序规则
select table_name, table_collation
from information_schema.tables
where table_schema = database()
and table_name in ('表名1', '表名2');

-- 查询字段的排序规则
select table_name, column_name, collation_name
from information_schema.columns
where table_schema = database()
and collation_name is not null
and table_name in ('表名1', '表名2');

2、然后修改不一样的数据表的排序规则（如果能定位是哪两个表起冲突了的话就直接改对应表就好，不知道的话就只能全改了）

-- 修改排序规则
-- 这里和编码规则一起修改了，编码规则和排序规则好像还有对应关系
-- 排序规则（collate）必须以编码规则（set）开头
-- 修改数据表，表中的字段也会一起被修改
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- 修改某个字段
ALTER TABLE 表名 MODIFY 字段名 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

---

注意：

那个查询表的排序规则的时候，排序规则要和其他大多数数据表的排序规则保持一致，要不然其他功能会出问题。

-- 查询数据库中全部数据表的排序规则
select table_name, table_collation
from information_schema.tables
where table_schema = database();