Debian mariadb 连接提示Caused by: java.sql.SQLException: Illegal mix of collations (utf8mb3_unicode_ci..

问题表现

Caused by: java.sql.SQLException: Illegal mix of collations (utf8mb3_unicode_ci,IMPLICIT) and (utf8mb3_general_ci,IMPLICIT) for operation '='

应用连接数据库时出现这个报错。

问题原因

出现这个错误的原因一般是数据库字符集、排序规则不一致导致的。对查询涉及到的数据库进行检查，不仅仅是表格的字符集、排序规则，还要检查字符串的字符集、排序规则。

处理思路

 调整数据库、表格、字段的字符集都保持一致，排序规则尽量不要使用utf8_general_ci。

处理步骤

1、检查数据库、数据表 排列规则等的字符集情况

SHOW VARIABLES LIKE 'character_set_server';查看整个MySQL服务器的默认字符集

查看database的字符编码 :show create database databasename;

SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA  WHERE SCHEMA_NAME = 'database_name';

查看特定数据库的字符集：

show variables like ‘collation%'; 

show variables like "%character%";
查看整个msyql的字符集情况：

修改字符集

1、修改character_set_client  ：

SET character_set_client = 'utf8mb4';

2、修改 character_set_connection：

SET character_set_connection = 'utf8mb4';

3、修改 character_set_results：

SET character_set_results = 'utf8mb4';

在修改完上述字符集之后，重启mariadb,然后 再重新启动应用，可以看到还是在报错：
Caused by: java.sql.SQLException: Illegal mix of collations (utf8mb3_unicode_ci,IMPLICIT) and (utf8mb3_general_ci,IMPLICIT) for operation '='
 

重新查询字符集情况发现：

改过的 character_set_client 、character_set_results、character_set_connection 还是变成了utf8bm3的，这说明设置的还是不对。说明在连接应用的时候  应用改变了这几个属性。

检查应用数据库连接属性：

这连接属性里 大大的 characterEncoding=UTF-8 。。。。 

尝试把这个characterEncoding属性改成utf8mb4，然后再试试看。 

经测：单纯修改 这个属性，再连接数据库 还是一样的报错。

前面修改了：

MariaDB [(none)]> SET character_set_client = 'utf8mb4';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET character_set_connection = 'utf8mb4';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET character_set_results = 'utf8mb4';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)
 

剩下了 character_set_system  这个属性没有修改。  这个属性的修改 不能像上述属性一样动态sql语句修改，需要修改配置文件。 

4、修改 character_set_system  属性

参考 mysql 设置character_set_system  属性的方法： 在/etc/my.cnf文件的【mysqld】段设定：

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

lower_case_table_names=1
 

等内容，在上一篇博文Debian mariadb 10.11设定表名 大小写不敏感方法-CSDN博客我提到，mariadb 10.11的版本配置文件已经与mysql有区别，mysqld段人配置不再my.cnf中，而是在：/etc/mysql/mariadb.conf.d/下 ：

尝试在50-server.cnf 在  [mysqld] 段中添加，为避免错误，在修改配置文件之前，先备份一下 ：

sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf  /etc/mysql/mariadb.conf.d/50-server.cnfbak

在Debian环境中，有一个比vi \vim好用的工具：nano :

cd /etc/mysql/mariadb.conf.d/
 sudo nano 50-server.cnf

然经过实践发现：除了 lower_case_table_names=1 这个属性在这个/etc/mysql/mariadb.conf.d/50-server.cnf  有用之外，其他都无效。 将此配置文件还原：

尝试 设置50-client.cnf ,先备份，避免无法还原的情况：

sudo cp 50-client.cnf 50-client.cnfbak
 sudo nano 50-client.cnf

在 50-client.cnf 的client段中设定： default-character-set=utf8mb4

在  50-server.cnf 的server段 设定 ：
collation-server=utf8mb4_unicode_ci

然后重启 查看字符集情况：

show variables like "%collation%";
show variables like "%character%";

最终 还是没设置character_set_system   属性成功，但是经过重新生成数据库以及数据表，全部使用utf8mb4以及utf8_general_ci 排序方式之后， 重启应用，不再报错：Illegal mix of collations (utf8mb3_unicode_ci,IMPLICIT) and (utf8mb3_general_ci,IMPLICIT) for operation '='

。

总结： Illegal mix of collations (utf8mb3_unicode_ci,IMPLICIT) and (utf8mb3_general_ci,IMPLICIT) for operation '=' 问题处理要求数据库、表等的字符集以及排列顺序都是一样的。