问题现象

从 MySQL 5.7 迁移到 TiDB 8.5 以后，使用 Dbeaver 执行 SQL 的时候遇到 [HY000]: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

现象原因

执行 create database 时没有显式指定字符集，会使用默认的字符集，如果显式指定字符集(比如：create database <DB> CHARACTER SET utf8mb4)，会使用字符集默认的排序规则。创建表时，字符集和排序规则会继承自库（use <DB> 后的 collation_database 变量），表中未显式指定的列和表保持一致。当在同一个库的内进行比较或者关联，如果出现 Illegal mix of collations 的报错（或者用不上索引等问题），建议手工调整表结构后(比如用 sed 命令把所有的 collate 关键字和规则名都去掉)重新导入。

可以用 collation 函数来查看列的实际生效的 collation：

mysql> select COLLATION(status_id),status_id from t_order;
+----------------------+-----------+
| COLLATION(status_id) | status_id |
+----------------------+-----------+
| utf8mb4_general_ci   | 1         |
+----------------------+-----------+
1 row in set (0.00 sec)

语句中的列如果不是继承自表（比如临时的字符常量列），就有可能出现 collate 的不一致，通过显式地配置 collate 才能修复成一致，比如：

mysql> select  COLLATION(status_id),status_id,COLLATION(status_flag),'a' as status_flag from (select status_id, 'a' as status_flag from t_order) a;
+----------------------+-----------+------------------------+-------------+
| COLLATION(status_id) | status_id | COLLATION(status_flag) | status_flag |
+----------------------+-----------+------------------------+-------------+
| utf8mb4_general_ci   | 1         | utf8mb4_0900_ai_ci     | a           |
+----------------------+-----------+------------------------+-------------+
1 row in set (0.01 sec)
mysql> select  COLLATION(status_id),status_id,COLLATION(status_flag),'a' as status_flag from (select status_id, 'a' collate utf8mb4_general_ci as status_flag from t_order) a;
+----------------------+-----------+------------------------+-------------+
| COLLATION(status_id) | status_id | COLLATION(status_flag) | status_flag |
+----------------------+-----------+------------------------+-------------+
| utf8mb4_general_ci   | 1         | utf8mb4_general_ci     | a           |
+----------------------+-----------+------------------------+-------------+
1 row in set (0.00 sec)

表上 utf8mb4_general_ci 的 status_id 字段和临时的字符常量列进行关联时，就会出现 Illegal mix of collations 的报错。

mysql> WITH order_status_dict AS ( SELECT '1' AS status_id, '待支付' AS status_name UNION ALL SELECT '2', '已支付' ) SELECT  COLLATION(status_id),status_id FROM  order_status_dict;
+----------------------+----------------+
| COLLATION(status_id) | status_id      |
+----------------------+----------------+
| utf8mb4_0900_ai_ci   | 1              |
| utf8mb4_0900_ai_ci   | 2              |
+----------------------+----------------+
mysql> WITH order_status_dict AS (
    SELECT '1' AS status_id, '待支付' AS status_name
    UNION ALL
    SELECT '2', '已支付'
)
SELECT 
    o.order_id,
    d.status_name
FROM 
     t_order o
JOIN 
    order_status_dict d 
    ON o.status_id = d.status_id;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

之所以发生 collation 报错是，由于字符常量临时构建出来的表列对象所使用的 utf8mb4_0900_ai_ci collation 继承自 collation_connection 变量和实体表的列使用的 collation 不一致。

不同版本组合的 collation_connection 结果

collation_connection 变量在不同版本的驱动/客户端和 TiDB 服务端的组合下会有不同的结果。

TiDB 8.5 版本和 mysql 命令行客户端 8.0.43 版本组合场景，使用 utf8mb4_0900_ai_ci collation_connection。

$ mysql --version
mysql  Ver 8.0.43 for Linux on x86_64 (Source distribution)
mysql> show variables like "%connection%";
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_connection | utf8mb4            |
| collation_connection     | utf8mb4_0900_ai_ci |

TiDB 8.5 版本和 mysql 命令行客户端 5.7.44 版本组合场景，默认使用 utf8 和 utf8_general_ci，显式声明使用 utf8mb4 字符集后，使用 utf8mb4_bin。

/home/tidb/mysql-5.7.44-linux-glibc2.12-x86_64/bin/mysql -V
./mysql  Ver 14.14 Distrib 5.7.44, for linux-glibc2.12 (x86_64) using  EditLine wrapper
/home/tidb/mysql-5.7.44-linux-glibc2.12-x86_64/bin/mysql -h 192.168.2.24 -P 4000 -uroot
mysql> show variables like "%connection%";
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
mysql> WITH order_status_dict AS ( SELECT '1' AS status_id, '待支付' AS status_name UNION ALL SELECT '2', '已支付' ) SELECT  COLLATION(status_id),status_id AS name_collation FROM  order_status_dict;
+----------------------+----------------+
| COLLATION(status_id) | name_collation |
+----------------------+----------------+
| utf8_general_ci      | 1              |
| utf8_general_ci      | 2              |
+----------------------+----------------+
mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%connection%";
+--------------------------+-------------+
| Variable_name            | Value       |
+--------------------------+-------------+
| character_set_connection | utf8mb4     |
| collation_connection     | utf8mb4_bin |

TiDB 7.1 版本和 mysql 命令行客户端 8.0.43 版本组合场景，使用 utf8mb4_bin collation_connection。

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v7.1.5 |
+--------------------+
mysql> show variables like "%connection%";
+--------------------------+-------------+
| Variable_name            | Value       |
+--------------------------+-------------+
| character_set_connection | utf8mb4     |
| collation_connection     | utf8mb4_bin |

TiDB 7.1 版本和 mysql 命令行客户端 5.7.44 版本组合场景并显式配置使用 utf8mb4 字符集后，默认使用 utf8mb4_general_ci。

/home/tidb/mysql-5.7.44-linux-glibc2.12-x86_64/bin/mysql -h 192.168.2.24 -P 14000 -uroot
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v7.1.5 |
+--------------------+
1 row in set (0.00 sec)

mysql> show variables like "%connection%";
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%connection%";
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| character_set_connection | utf8     |
| collation_connection     | utf8_bin |

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%connection%";
+--------------------------+-------------+
| Variable_name            | Value       |
+--------------------------+-------------+
| character_set_connection | utf8mb4     |
| collation_connection     | utf8mb4_bin |

不同 JDBC 驱动版本对 collation_connection 影响的变迁，可以参考 https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-session.html

characterEncoding 默认字符集

For Connector/J 8.0.25 and earlier, the driver will try to use the server's default character set;

For Connector/J 8.0.26 and later, the driver will use "utf8mb4".

connectionCollation 默认 collation

For Connector/J 8.0.25 and earlier, the driver will try to use the server's default character set;

For Connector/J 8.0.26 and later, the driver will use utf8mb4's default collation.（即 utf8mb4_0900_ai_ci）

自 v7.4.0 起，TiDB 已经兼容 MySQL 8.0 的主要功能，函数 version() 将返回以 8.0.11 为前缀的版本信息。使用以下的 Java 代码进行验证。

        config.setJdbcUrl("jdbc:mysql://<TiDB-IP>:<TiDB-Port>/dbutf8?characterEncoding=UTF-8&useSSL=false");
...
    DatabaseMetaData metaData = connection.getMetaData();
            System.out.println("JDBC 驱动版本: " + metaData.getDriverVersion());
    String versionQuery = "SELECT VERSION() AS mysql_version";
...
                    String mysqlVersion = rs.getString("mysql_version");
                    System.out.println("MySQL 版本号: " + mysqlVersion);
...
    String collationQuery = "show variables like 'collation_connection'";
...
                    System.out.println(rs.getString(1) + ": " + rs.getString(2));
...
    String query = "WITH order_status_dict AS ( SELECT '1' AS status_id, '待支付' AS status_name UNION ALL SELECT '2', '已支付' ) SELECT  COLLATION(status_id) AS name_collation ,status_id FROM  order_status_dict";
...
            System.out.println("COLLATION(status_id): " + resultSet.getString(1) + 
                             ", status_id: " + resultSet.getString(2));

TiDB 8.5 版本和 JDBC 5.1.49 / 8.0.33 的组合结果如下

[tidb@host24 hikari]$ export CLASSPATH=./HikariCP-4.0.3.jar:./mysql-connector-j-8.0.33.jar:./slf4j-api-1.7.36.jar:./slf4j-simple-1.7.36.jar:.
[tidb@host24 hikari]$ java Main

JDBC 驱动版本: mysql-connector-j-8.0.33 (Revision: 7d6b0800528b6b25c68b52dc10d6c1c8429c100c)
MySQL 版本号: 8.0.11-TiDB-v7.1.8-5.3 // 即 TiDB 8.5.3
collation_connection: utf8mb4_0900_ai_ci
COLLATION(status_id): utf8mb4_0900_ai_ci, status_id: 1
COLLATION(status_id): utf8mb4_0900_ai_ci, status_id: 2

[tidb@host24 hikari]$ export CLASSPATH=./HikariCP-4.0.3.jar:./mysql-connector-java-5.1.49-bin.jar:./slf4j-api-1.7.36.jar:./slf4j-simple-1.7.36.jar:.
[tidb@host24 hikari]$ java Main

JDBC 驱动版本: mysql-connector-java-5.1.49 ( Revision: ad86f36e100e104cd926c6b81c8cab9565750116 )
MySQL 版本号: 8.0.11-TiDB-v7.1.8-5.3 // 即 TiDB 8.5.3
collation_connection: utf8mb4_bin
COLLATION(status_id): utf8mb4_bin, status_id: 1
COLLATION(status_id): utf8mb4_bin, status_id: 2

TiDB 7.1 版本的函数 version() 将返回以 5.7.25 为前缀的版本信息。TiDB 7.1 版本和 JDBC 5.1.49 / 8.0.33 的组合结果如下

[tidb@host24 hikari]$ export CLASSPATH=./HikariCP-4.0.3.jar:./mysql-connector-j-8.0.33.jar:./slf4j-api-1.7.36.jar:./slf4j-simple-1.7.36.jar:.
[tidb@host24 hikari]$ java Main

JDBC 驱动版本: mysql-connector-j-8.0.33 (Revision: 7d6b0800528b6b25c68b52dc10d6c1c8429c100c)
MySQL 版本号: 5.7.25-TiDB-v7.1.5
collation_connection: utf8mb4_general_ci
COLLATION(status_id): utf8mb4_general_ci, status_id: 1
COLLATION(status_id): utf8mb4_general_ci, status_id: 2

[tidb@host24 hikari]$ export CLASSPATH=./HikariCP-4.0.3.jar:./mysql-connector-java-5.1.49-bin.jar:./slf4j-api-1.7.36.jar:./slf4j-simple-1.7.36.jar:.
[tidb@host24 hikari]$ java Main

JDBC 驱动版本: mysql-connector-java-5.1.49 ( Revision: ad86f36e100e104cd926c6b81c8cab9565750116 )
MySQL 版本号: 5.7.25-TiDB-v7.1.5
collation_connection: utf8mb4_bin
COLLATION(status_id): utf8mb4_bin, status_id: 1
COLLATION(status_id): utf8mb4_bin, status_id: 2

组合结果展示如下：


	JDBC 8.0.25 and earlier	JDBC 8.0.26 and later
TiDB 7.4 之前的版本 (version() 默认返回 MySQL 5.7)	遵循服务端的字符集配置，使用 utf8mb4_bin 作为排序规则。	遵循驱动程序，使用 utf8mb4_general_ci 作为排序规则。
TiDB 7.4 及之后版本 (version() 默认返回 MySQL 8.0)		遵循驱动程序，使用 `utf8mb4_0900_ai_ci` 作为排序规则

JDBC 8.0.25 and earlier

JDBC 8.0.26 and later

TiDB 7.4 之前的版本

(version() 默认返回 MySQL 5.7)

遵循服务端的字符集配置，

使用 utf8mb4_bin 作为排序规则。

遵循驱动程序，

使用 utf8mb4_general_ci 作为排序规则。

TiDB 7.4 及之后版本

(version() 默认返回 MySQL 8.0)

遵循驱动程序，

使用 utf8mb4_0900_ai_ci 作为排序规则

注意：如果 v7.4.0 之前版本的 TiDB 通过配置 server_version 配置项返回 MySQL 8.0，由于不支持 utf8mb4_0900_ai_ci 排序规则，将回退到使用默认的排序规则 utf8mb4_bin。

请参考文档：

https://docs.pingcap.com/zh/tidb/stable/sql-faq/#%E5%BD%93-jdbc-url-%E4%B8%AD%E6%9C%AA%E9%85%8D%E7%BD%AE-connectioncollation-%E6%97%B6jdbc-%E8%BF%9E%E6%8E%A5%E4%BD%BF%E7%94%A8%E4%BB%80%E4%B9%88%E6%8E%92%E5%BA%8F%E8%A7%84%E5%88%99

所以在使用高版本的 JDBC 驱动的场景下，当 TiDB 版本进行升级后，会话的 collation_connection 变量值可能出现不符合应用预期，会出现 Illegal mix of collations 的报错。

解决方案

如果是存量应用进行数据库版本升级（尤其是跨越 TiDB 7.4 的版本升级），推荐显式配置成预期的 collation_connection，回退使用 mysql-connector-java-5.1.49 驱动版本。

显式配置方法：

JDBC URL 的修改方法可以是，增加 connectionCollation 或者 sessionVariables 配置，比如 &connectionCollation=utf8mb4_0900_bin 或者 &sessionVariables=collation_connection='utf8mb4_0900_bin'。

DBeaver 的修改方法可以配置初始化语句。

默认 collation 的配置

用 MySQL> show collation; 查看 UTF8 相关的 TiDB 常用的排序规则，关键属性列举如下：


排序规则	字符集	是否默认	大小写	Pad_attribute 控制尾部空格处理
utf8mb4_0900_bin	utf8mb4	否	大小写敏感	NO PAD 原格式比较
utf8mb4_0900_ai_ci	utf8mb4	否	忽略大小写	原格式比较
utf8mb4_bin	utf8mb4	utf8mb4 默认	大小写敏感	PAD SPACE 空格对齐比较
utf8mb4_general_ci	utf8mb4	否	忽略大小写	空格对齐比较
utf8_bin	utf8	utf8 默认	大小写敏感	空格对齐比较
utf8_general_ci	utf8	否	忽略大小写	空格对齐比较

平常需要关注的是 Pad_attribute，使用 utf8mb4_0900 排序方案是 NO PAD 原格式比较。如果业务方希望字段末带和不带空格需要被当作不一致的情况处理，比如遇到以下的报错：

insert into t1 values ('aa');

insert into t2 values ('aa ');

ERROR 1062 (23000): Duplicate entry 'pk ' for key 't.PRIMARY'，

需要使用 utf8mb4_0900 排序方案，建议显式指定 connectionCollation。

default_collation_for_utf8mb4

MySQL 8.0 为了兼容旧版本升级上来的原来 5.7 的参数配置，设计 default_collation_for_utf8mb4 参数，目标是对原应用的升级进行兼容。

参数 default_collation_for_utf8mb4 的使用限制

不适用于字符集是 utf8 的情况(部分早期的旧应用需要关注)。
该参数在 MySQL 8.0 / TiDB 8.5 可配置的参数项只允许 5.7 的 collation (utf8mb4_general_ci、utf8mb4_bin)和新版本的默认参数(utf8mb4_0900_ai_ci)，但是不允许设置为utf8mb4_0900_bin。

set global default_collation_for_utf8mb4='utf8mb4_0900_bin';
ERROR 3721 (HY000): Invalid default collation utf8mb4_0900_bin: utf8mb4_0900_ai_ci or utf8mb4_general_ci or utf8mb4_bin expected

需要注意这个参数配置项需要显式指定字符集（即语句中有 CHARACTER SET utf8mb4 时）才生效。

默认情况下的 collation_database 使用 utf8mb4_bin。

mysql> create database utf8_1;
Query OK, 0 rows affected (0.53 sec)

mysql> show create database  utf8_1;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| utf8_1   | CREATE DATABASE `utf8_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use utf8_1
Database changed
mysql> show variables like "%database%";
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| character_set_database | utf8mb4     |
| collation_database     | utf8mb4_bin |
| skip_show_database     | 0           |
+------------------------+-------------+
3 rows in set (0.00 sec)

注意：已经配置 default_collation_for_utf8mb4='utf8mb4_general_ci' 情况下，create database 没有显式指定的情况下，collation_database 使用 utf8mb4_bin。

mysql> set global default_collation_for_utf8mb4='utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> ^DBye
[tidb@host24 ~]$ mysql -h 192.168.2.24 -P 4000 -uroot  
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3766485080
Server version: 8.0.11-TiDB-v7.1.8-5.3 TiDB Server (Apache License 2.0) Enterprise Edition, MySQL 8.0 compatible


mysql> show variables like "%default_collation_for_utf8mb4%";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+
1 row in set (0.00 sec)

mysql> create database utf8_2;
Query OK, 0 rows affected (0.53 sec)

mysql> show create database  utf8_1;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| utf8_1   | CREATE DATABASE `utf8_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use utf8_2;
Database changed

mysql> show variables like "%database%";
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| character_set_database | utf8mb4     |
| collation_database     | utf8mb4_bin |
| skip_show_database     | 0           |
+------------------------+-------------+
3 rows in set (0.00 sec)

create database 显式指定 CHARACTER SET utf8mb4 的情况下，Database DDL 和 collation_database 使用 utf8mb4_general_ci。

mysql> create database t_utf8_3 CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.51 sec)

mysql> use t_utf8_3
Database changed
mysql> show variables like "%database%";
+------------------------+--------------------+
| Variable_name          | Value              |
+------------------------+--------------------+
| character_set_database | utf8mb4            |
| collation_database     | utf8mb4_general_ci |
| skip_show_database     | 0                  |
+------------------------+--------------------+
3 rows in set (0.00 sec)

mysql> show create database  t_utf8_3;
+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                 |
+----------+-------------------------------------------------------------------------------------------------+
| t_utf8_3 | CREATE DATABASE `t_utf8_3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ |
+----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

结论

在新建应用和还是旧应用升级兼容上，建议根据以上的组合，合理使用版本和 default_collation_for_utf8mb4 的配置。

新建应用建议 JDBC URL 的 connectionCollation 参数、创建 database 时的 collation 全部显式指定统一的参数，根据 Pad_attribute 的需求选择排序规则。
升级应用建议 JDBC URL 的 connectionCollation 参数和 default_collation_for_utf8mb4 参数，全部显式指定成已有表的 DDL 的 collate。

排序是 MySQL 比较灵活的一项配置，如果未进行正确的配置，可能会出现非预期的行为，建议在熟悉不同配置的影响下谨慎进行选择，并进行显式配置。

从 Illegal mix of collations 错误说明版本的 collation 变迁