MySQL 5.7升级8.0后出现排序规则问题的解决方案汇总

华而必实

比较操作中使用不同的字符集或排序规则通常会触发此问题，MySQL 8.0 默认 COLLATE 为

1. utf8mb4_0900_ai_ci

复制代码

和 对应列 COLLATE 的

1. utf8mb4_general_ci

复制代码

不匹配。

问题现象

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'

问题原因

比较操作中使用不同的字符集或排序规则通常会触发此问题，MySQL 8.0 默认 COLLATE 为

1. utf8mb4_0900_ai_ci

复制代码

和 对应列 COLLATE 的

1. utf8mb4_general_ci

复制代码

不匹配。

问题重现过程

创建测试表。

1. CREATE TABLE `t01` (
   
2.   `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
   
3.   `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
   
4.   `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
   
5.   PRIMARY KEY (`ID`) USING BTREE
   
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

复制代码

执行查询语句。

1. SELECT AAA.* FROM(
   
2. SELECT
   
3. @xxx AS _xxx,
   
4. ( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
   
5. FROM
   
6. t01,( SELECT @xxx := 'xxx') b
   
7. WHERE @xxx IS NOT NULL) ID,t01 AAA
   
8. WHERE
   
9. FIND_IN_SET( AAA.A_CODE, ID._xxx )
   
10. 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'

问题分析

查看默认排序规则。

1. mysql> show collation like 'utf8mb4_0900_ai_ci';
   
2. +--------------------+---------+-----+---------+----------+---------+---------------+
   
3. | Collation          | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
   
4. +--------------------+---------+-----+---------+----------+---------+---------------+
   
5. | utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
   
6. +--------------------+---------+-----+---------+----------+---------+---------------+
   
7. 1 row in set (0.00 sec)
   
8. 
   
9. mysql> show collation like 'utf8mb4_general_ci';
   
10. +--------------------+---------+----+---------+----------+---------+---------------+
    
11. | Collation          | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
    
12. +--------------------+---------+----+---------+----------+---------+---------------+
    
13. | utf8mb4_general_ci | utf8mb4 | 45 |         | Yes      |       1 | PAD SPACE     |
    
14. +--------------------+---------+----+---------+----------+---------+---------------+
    
15. 1 row in set (0.00 sec)
    
16. 
    
17. mysql> SELECT * FROM INFORMATION_SCHEMA.COLLATIONS WHERE IS_DEFAULT='Yes' and CHARACTER_SET_NAME='utf8mb4';
    
18. +--------------------+--------------------+-----+------------+-------------+---------+---------------+
    
19. | COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
    
20. +--------------------+--------------------+-----+------------+-------------+---------+---------------+
    
21. | utf8mb4_0900_ai_ci | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
    
22. +--------------------+--------------------+-----+------------+-------------+---------+---------------+
    
23. 1 row in set (0.00 sec)

复制代码

查看相关参数。

1. mysql> show variables like '%collation%';
   
2. +-------------------------------+--------------------+
   
3. | Variable_name                 | Value              |
   
4. +-------------------------------+--------------------+
   
5. | collation_connection          | utf8mb4_0900_ai_ci |
   
6. | collation_database            | utf8mb4_general_ci |
   
7. | collation_server              | utf8mb4_general_ci |
   
8. | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
   
9. +-------------------------------+--------------------+
   
10. 4 rows in set (0.00 sec)
    
11. 
    
12. 其中:
    
13. mysql> show global variables like '%collation%';
    
14. +-------------------------------+--------------------+
    
15. | Variable_name                 | Value              |
    
16. +-------------------------------+--------------------+
    
17. | collation_connection          | utf8mb4_general_ci |
    
18. | collation_database            | utf8mb4_general_ci |
    
19. | collation_server              | utf8mb4_general_ci |
    
20. | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
    
21. +-------------------------------+--------------------+
    
22. 4 rows in set (0.00 sec)

复制代码

查看配置文件参数。

1. mysql@CJC-DB-01:/home/mysql$cat /etc/my.cnf
   
2. ......
   
3. [mysqld]
   
4. collation_server = utf8mb4_general_ci

复制代码

可以看到，客户端局部会话变量 collation_connection 的值为

1. utf8mb4_0900_ai_ci

复制代码

，而全局变量值为

1. utf8mb4_general_ci

复制代码

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

1. utf8mb4_0900_ai_ci

复制代码

。

解决方案

• 修改参数
  
• 修改表 COLLATE
  
• 修改 SQL 语句
  

1. 修改参数

参数

1. collation_connection

复制代码

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

1. --character-set-client-handshake
   
2. Command-Line Format:--character-set-client-handshake[={OFF|ON}]
   
3. Deprecated:8.0.35
   
4. Type:Boolean
   
5. Default Value:ON

复制代码

参数说明

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

  复制代码

此选项在 MySQL 8.0.35 及更高版本的 MySQL 8.0 中已被弃用。在该版本中，无论何时使用此选项，都会发出警告，并将在未来版本的 MySQL 中删除。
依赖此选项的应用程序应该尽快开始迁移。
添加 my.cnf 参数。

1. [mysqld]
   
2. skip-character-set-client-handshake

复制代码

重启 MySQL。

1. mysqladmin -uroot -p****** shutdown
   
2. mysqld --defaults-file=/etc/my.cnf --user=mysql &
   
3. 
   
4. 登录
   
5. 
   
6. mysql -uroot -p cjc
   
7. 
   
8. 查看参数，collation_connection 参数值修改成功
   
9. 
   
10. mysql> show global variables like '%collation%';
    
11. +-------------------------------+--------------------+
    
12. | Variable_name                 | Value              |
    
13. +-------------------------------+--------------------+
    
14. | collation_connection          | utf8mb4_general_ci |
    
15. | collation_database            | utf8mb4_general_ci |
    
16. | collation_server              | utf8mb4_general_ci |
    
17. | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
    
18. +-------------------------------+--------------------+
    
19. 4 rows in set (0.00 sec)
    
20. 
    
21. mysql> show variables like '%collation%';
    
22. +-------------------------------+--------------------+
    
23. | Variable_name                 | Value              |
    
24. +-------------------------------+--------------------+
    
25. | collation_connection          | utf8mb4_general_ci |
    
26. | collation_database            | utf8mb4_general_ci |
    
27. | collation_server              | utf8mb4_general_ci |
    
28. | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
    
29. +-------------------------------+--------------------+
    
30. 4 rows in set (0.01 sec)

复制代码

再次执行，问题解决。

1. SELECT AAA.* FROM(
   
2. SELECT
   
3. @xxx AS _xxx,
   
4. ( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
   
5. FROM
   
6. t01,( SELECT @xxx := 'xxx') b
   
7. WHERE @xxx IS NOT NULL) ID,t01 AAA
   
8. WHERE
   
9. FIND_IN_SET( AAA.A_CODE, ID._xxx )
   
10. order by A_CODE;Empty set, 2 warnings (0.00 sec)

复制代码

2. 修改表 COLLATE

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

1. SELECT AAA.* FROM(
   
2. SELECT
   
3. @xxx AS _xxx,
   
4. ( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
   
5. FROM
   
6. t01,( SELECT @xxx := 'xxx') b
   
7. WHERE @xxx IS NOT NULL) ID,t01 AAA
   
8. WHERE
   
9. FIND_IN_SET( AAA.A_CODE, ID._xxx )
   
10. 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'

复制代码

修改表排序规则。

1. mysql> show create table t01\G;
   
2. *************************** 1. row ***************************
   
3.        Table: t01
   
4. Create Table: CREATE TABLE `t01` (
   
5.   `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
   
6.   `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
   
7.   `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
   
8.   PRIMARY KEY (`ID`) USING BTREE
   
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC
   
10. 1 row in set (0.00 sec)
    
11. 
    
12. ERROR:
    
13. No query specified

复制代码

修改所有列 COLLATE，实际上只修改

1. A_CODE

复制代码

、

1. B_CODE

复制代码

列 COLLATE 也可解决此问题。

1. ALTER TABLE cjc.t01 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
   
2. ALTER TABLE cjc.t01 MODIFY COLUMN `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
   
3. ALTER TABLE cjc.t01 MODIFY COLUMN `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL;
   
4. ALTER TABLE cjc.t01 MODIFY COLUMN `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL;

复制代码

再次执行，问题解决。

1. SELECT AAA.* FROM(
   
2. SELECT
   
3. @xxx AS _xxx,
   
4. ( SELECT @xxx := GROUP_CONCAT( A_CODE ) FROM t01 WHERE FIND_IN_SET( B_CODE, @xxx ) ) AS cxxx
   
5. FROM
   
6. t01,( SELECT @xxx := 'xxx') b
   
7. WHERE @xxx IS NOT NULL) ID,t01 AAA
   
8. WHERE
   
9. FIND_IN_SET( AAA.A_CODE, ID._xxx )
   
10. order by A_CODE;Empty set, 2 warnings (0.00 sec)

复制代码

查看表结构。

1. mysql> show create table t01\G;
   
2. *************************** 1. row ***************************
   
3.        Table: t01
   
4. Create Table: CREATE TABLE `t01` (
   
5.   `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
   
6.   `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
   
7.   `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
   
8.   PRIMARY KEY (`ID`) USING BTREE
   
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
   
10. 1 row in set (0.00 sec)

复制代码

3. 修改 SQL 语句

将

1. A_CODE

复制代码

，

1. B_CODE

复制代码

列的 COLLATE 在 SQL 语句中转换为

1. utf8mb4_0900_ai_ci

复制代码

。
改写后的SQL如下：

1. SELECT AAA.* FROM(
   
2. SELECT
   
3. @xxx AS _xxx,
   
4. ( 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
   
5. FROM
   
6. t01,( SELECT @xxx := 'xxx') b
   
7. WHERE @xxx IS NOT NULL) ID,t01 AAA
   
8. WHERE
   
9. FIND_IN_SET( AAA.A_CODE COLLATE utf8mb4_0900_ai_ci, ID._xxx )
   
10. order by A_CODE;

复制代码

总结

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

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

以上就是MySQL 5.7升级8.0后出现排序规则问题的解决方案汇总的详细内容，更多关于MySQL 5.7升级8.0排序规则问题的资料请关注脚本之家其它相关文章！

来源:https://www.jb51.net/database/323411jng.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！