技术分享 | MySQL 5.7 升级 8.0 后，排序规则问题解决方案汇总

2024-09-14 17:19:33 浏览数 (1)

作者：陈举超，Oracle ACE，10年 数据库运维经验。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1500 字，预计阅读需要 5 分钟。

1问题现象

MySQL 5.7.34 升级到 8.0.32 后部分查询语句报错如下：

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'find_in_set'

2问题原因

比较操作中使用不同的字符集或排序规则通常会触发此问题，MySQL 8.0 默认 COLLATE 为 utf8mb4_0900_ai_ci 和 对应列 COLLATE 的 utf8mb4_general_ci 不匹配。

3问题重现过程

创建测试表。

代码语言：javascript复制
CREATE TABLE `t01` (
  `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

执行查询语句。

代码语言：javascript复制
SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE, ID._xxx )
order by A_CODE;

报错。

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'find_in_set'

4问题分析

查看默认排序规则。

代码语言：javascript复制
mysql> show collation like 'utf8mb4_0900_ai_ci';
 -------------------- --------- ----- --------- ---------- --------- --------------- 
| Collation          | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
 -------------------- --------- ----- --------- ---------- --------- --------------- 
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
 -------------------- --------- ----- --------- ---------- --------- --------------- 
1 row in set (0.00 sec)

mysql> show collation like 'utf8mb4_general_ci';
 -------------------- --------- ---- --------- ---------- --------- --------------- 
| Collation          | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
 -------------------- --------- ---- --------- ---------- --------- --------------- 
| utf8mb4_general_ci | utf8mb4 | 45 |         | Yes      |       1 | PAD SPACE     |
 -------------------- --------- ---- --------- ---------- --------- --------------- 
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.COLLATIONS WHERE IS_DEFAULT='Yes' and CHARACTER_SET_NAME='utf8mb4';
 -------------------- -------------------- ----- ------------ ------------- --------- --------------- 
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
 -------------------- -------------------- ----- ------------ ------------- --------- --------------- 
| utf8mb4_0900_ai_ci | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
 -------------------- -------------------- ----- ------------ ------------- --------- --------------- 
1 row in set (0.00 sec)

查看相关参数。

代码语言：javascript复制
mysql> show variables like '%collation%';
 ------------------------------- -------------------- 
| Variable_name                 | Value              |
 ------------------------------- -------------------- 
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
 ------------------------------- -------------------- 
4 rows in set (0.00 sec)

其中:
mysql> show global variables like '%collation%';
 ------------------------------- -------------------- 
| Variable_name                 | Value              |
 ------------------------------- -------------------- 
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
 ------------------------------- -------------------- 
4 rows in set (0.00 sec)

查看配置文件参数。

代码语言：javascript复制
mysql@CJC-DB-01:/home/mysql$cat /etc/my.cnf 
......
[mysqld]
collation_server = utf8mb4_general_ci

可以看到，客户端局部会话变量 collation_connection 的值为 utf8mb4_0900_ai_ci，而全局变量值为 utf8mb4_general_ci，两者不一致。

这是由于服务端在客户端连接时，获取了客户端对字符集和排序规则的缺省设置，也就是 utf8mb4_0900_ai_ci。

5解决方案

  1. 修改参数
  2. 修改表 COLLATE
  3. 修改 SQL 语句

1. 修改参数

参数collation_connection 在客户端局部变量值和全局变量值不一致，如何改成一致？官网参考材料[1]

代码语言：javascript复制
--character-set-client-handshake
Command-Line Format:--character-set-client-handshake[={OFF|ON}]
Deprecated:8.0.35
Type:Boolean
Default Value:ON

参数说明

  • 不忽略客户端发送的字符集信息
  • 为了忽略客户端信息并使用默认的服务器字符集
  • 使用参数：--skip-character-set-client-handshake

此选项在 MySQL 8.0.35 及更高版本的 MySQL 8.0 中已被弃用。在该版本中，无论何时使用此选项，都会发出警告，并将在未来版本的 MySQL 中删除。

依赖此选项的应用程序应该尽快开始迁移。

添加 my.cnf 参数。

代码语言：javascript复制
[mysqld]
skip-character-set-client-handshake

重启 MySQL。

代码语言：javascript复制
mysqladmin -uroot -p****** shutdown
mysqld --defaults-file=/etc/my.cnf --user=mysql &

登录

mysql -uroot -p cjc

查看参数，collation_connection 参数值修改成功

mysql> show global variables like '%collation%';
 ------------------------------- -------------------- 
| Variable_name                 | Value              |
 ------------------------------- -------------------- 
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
 ------------------------------- -------------------- 
4 rows in set (0.00 sec)

mysql> show variables like '%collation%';
 ------------------------------- -------------------- 
| Variable_name                 | Value              |
 ------------------------------- -------------------- 
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
 ------------------------------- -------------------- 
4 rows in set (0.01 sec)

再次执行，问题解决。

代码语言：javascript复制
SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE, ID._xxx )
order by A_CODE;
Empty set, 2 warnings (0.00 sec)

2. 修改表 COLLATE

先改回原参数，查询报错。

代码语言：javascript复制
SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE, ID._xxx )
order by A_CODE;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'find_in_set'

修改表排序规则。

代码语言：javascript复制
mysql> show create table t01G;
*************************** 1. row ***************************
       Table: t01
Create Table: CREATE TABLE `t01` (
  `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

ERROR: 
No query specified

修改所有列 COLLATE，实际上只修改 A_CODE、B_CODE 列 COLLATE 也可解决此问题。

代码语言：javascript复制
ALTER TABLE cjc.t01 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE cjc.t01 MODIFY COLUMN `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
ALTER TABLE cjc.t01 MODIFY COLUMN `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL;
ALTER TABLE cjc.t01 MODIFY COLUMN `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL;

再次执行，问题解决。

代码语言：javascript复制
SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE, ID._xxx )
order by A_CODE;

Empty set, 2 warnings (0.00 sec)

查看表结构。

代码语言：javascript复制
mysql> show create table t01G;
*************************** 1. row ***************************
       Table: t01
Create Table: CREATE TABLE `t01` (
  `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

3. 修改 SQL 语句

将 A_CODE，B_CODE 列的 COLLATE 在 SQL 语句中转换为 utf8mb4_0900_ai_ci。

改写后的SQL如下：

代码语言：javascript复制
SELECT AAA.* FROM(
SELECT 
@xxx AS _xxx,
( SELECT @xxx := GROUP_CONCAT( A_CODE COLLATE utf8mb4_0900_ai_ci ) FROM t01 WHERE FIND_IN_SET( B_CODE COLLATE utf8mb4_0900_ai_ci, @xxx ) ) AS cxxx
FROM
t01,( SELECT @xxx := 'xxx') b
WHERE @xxx IS NOT NULL) ID,t01 AAA
WHERE
FIND_IN_SET( AAA.A_CODE COLLATE utf8mb4_0900_ai_ci, ID._xxx )
order by A_CODE;

6总结

比较三种解决方案，每种解决方案适用场景不同，请根据实际情况选择解决方案。

  1. 修改参数 适用于数据库是从 5.7 或更低版本升级到 8.0，并且表数量较多、数据量加大。不适用于批量修改所有表、列字符集和排序规则。
  2. 修改表 COLLATE 适用于修改过程会锁表，数据量越大时间越长，使用于数据量小的场景，建议将所有表、列字符集和排序规则改成 8.0 默认值，后续新增表时不指定字符集和排序规则。
  3. 修改 SQL 语句 适用于临时查询，改SQL影响最小。

参考资料

[1]

修改字符集变量: https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_character-set-client-handshake

本文关键字：#MySQL# #字符集# #升级# #排序规则#

mysql 解决方案 客户端 排序 ci

0 人点赞