欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL 5.7升級(jí)8.0后出現(xiàn)排序規(guī)則問(wèn)題的解決方案匯總

 更新時(shí)間：2024年06月28日 08:57:20   作者：愛(ài)可生開(kāi)源社區(qū)  
MySQL 5.7.34 升級(jí)到 8.0.32 后部分查詢(xún)語(yǔ)句報(bào)錯(cuò)如下,ERROR 1267 (HY000),比較操作中使用不同的字符集或排序規(guī)則通常會(huì)觸發(fā)此問(wèn)題,所以本文給大家介紹了MySQL 5.7升級(jí)8.0后出現(xiàn)排序規(guī)則問(wèn)題的解決方案匯總,需要的朋友可以參考下

比較操作中使用不同的字符集或排序規(guī)則通常會(huì)觸發(fā)此問(wèn)題，MySQL 8.0 默認(rèn) COLLATE 為 utf8mb4_0900_ai_ci 和 對(duì)應(yīng)列 COLLATE 的 utf8mb4_general_ci 不匹配。

問(wèn)題現(xiàn)象

MySQL 5.7.34 升級(jí)到 8.0.32 后部分查詢(xún)語(yǔ)句報(bào)錯(cuò)如下：

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

問(wèn)題原因

比較操作中使用不同的字符集或排序規(guī)則通常會(huì)觸發(fā)此問(wèn)題，MySQL 8.0 默認(rèn) COLLATE 為 utf8mb4_0900_ai_ci 和 對(duì)應(yīng)列 COLLATE 的 utf8mb4_general_ci 不匹配。

問(wèn)題重現(xiàn)過(guò)程

創(chuàng)建測(cè)試表。

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;

執(zhí)行查詢(xún)語(yǔ)句。

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;

報(bào)錯(cuò)。

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

問(wèn)題分析

查看默認(rèn)排序規(guī)則。

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)

查看相關(guān)參數(shù)。

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)

查看配置文件參數(shù)。

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

可以看到，客戶(hù)端局部會(huì)話變量 collation_connection 的值為 utf8mb4_0900_ai_ci，而全局變量值為 utf8mb4_general_ci，兩者不一致。

這是由于服務(wù)端在客戶(hù)端連接時(shí)，獲取了客戶(hù)端對(duì)字符集和排序規(guī)則的缺省設(shè)置，也就是 utf8mb4_0900_ai_ci。

解決方案

  • 修改參數(shù)
  • 修改表 COLLATE
  • 修改 SQL 語(yǔ)句

1. 修改參數(shù)

參數(shù)collation_connection 在客戶(hù)端局部變量值和全局變量值不一致，如何改成一致？官網(wǎng)參考材料

--character-set-client-handshake
Command-Line Format:--character-set-client-handshake[={OFF|ON}]
Deprecated:8.0.35
Type:Boolean
Default Value:ON

參數(shù)說(shuō)明

  • 不忽略客戶(hù)端發(fā)送的字符集信息
  • 為了忽略客戶(hù)端信息并使用默認(rèn)的服務(wù)器字符集
  • 使用參數(shù)：--skip-character-set-client-handshake

此選項(xiàng)在 MySQL 8.0.35 及更高版本的 MySQL 8.0 中已被棄用。在該版本中，無(wú)論何時(shí)使用此選項(xiàng)，都會(huì)發(fā)出警告，并將在未來(lái)版本的 MySQL 中刪除。

依賴(lài)此選項(xiàng)的應(yīng)用程序應(yīng)該盡快開(kāi)始遷移。

添加 my.cnf 參數(shù)。

[mysqld]
skip-character-set-client-handshake

重啟 MySQL。

mysqladmin -uroot -p****** shutdown
mysqld --defaults-file=/etc/my.cnf --user=mysql &

登錄

mysql -uroot -p cjc

查看參數(shù)，collation_connection 參數(shù)值修改成功

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)

再次執(zhí)行，問(wèn)題解決。

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

先改回原參數(shù)，查詢(xún)報(bào)錯(cuò)。

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'

修改表排序規(guī)則。

mysql> show create table t01\G;
*************************** 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，實(shí)際上只修改 A_CODE、B_CODE 列 COLLATE 也可解決此問(wèn)題。

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;

再次執(zhí)行，問(wèn)題解決。

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)

查看表結(jié)構(gòu)。

mysql> show create table t01\G;
*************************** 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 語(yǔ)句

將 A_CODE，B_CODE 列的 COLLATE 在 SQL 語(yǔ)句中轉(zhuǎn)換為 utf8mb4_0900_ai_ci。

改寫(xiě)后的SQL如下：

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;

總結(jié)

比較三種解決方案，每種解決方案適用場(chǎng)景不同，請(qǐng)根據(jù)實(shí)際情況選擇解決方案。

  • 修改參數(shù)

    適用于數(shù)據(jù)庫(kù)是從 5.7 或更低版本升級(jí)到 8.0，并且表數(shù)量較多、數(shù)據(jù)量加大。不適用于批量修改所有表、列字符集和排序規(guī)則。

  • 修改表 COLLATE

    適用于修改過(guò)程會(huì)鎖表，數(shù)據(jù)量越大時(shí)間越長(zhǎng)，使用于數(shù)據(jù)量小的場(chǎng)景，建議將所有表、列字符集和排序規(guī)則改成 8.0 默認(rèn)值，后續(xù)新增表時(shí)不指定字符集和排序規(guī)則。

  • 修改 SQL 語(yǔ)句

    適用于臨時(shí)查詢(xún)，改SQL影響最小。

以上就是MySQL 5.7升級(jí)8.0后出現(xiàn)排序規(guī)則問(wèn)題的解決方案匯總的詳細(xì)內(nèi)容，更多關(guān)于MySQL 5.7升級(jí)8.0排序規(guī)則問(wèn)題的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

相關(guān)文章

  • DBeaver連接MySQL提示"Public Key Retrieval is not allowed"問(wèn)題解決方式

    DBeaver連接MySQL提示"Public Key Retrieval is

    dbeaver數(shù)據(jù)庫(kù)連接工具,可以支持幾乎所有的主流數(shù)據(jù)庫(kù).mysql,oracle.sqlserver,db2 等等,這篇文章主要給大家介紹了關(guān)于DBeaver連接MySQL提示"Public Key Retrieval is not allowed"問(wèn)題的解決方式,需要的朋友可以參考下
    2023-10-10
  • MySQL中的驅(qū)動(dòng)表與被驅(qū)動(dòng)表及含義

    MySQL中的驅(qū)動(dòng)表與被驅(qū)動(dòng)表及含義

    使用join連接查詢(xún)時(shí)如果有where條件，則MySQL執(zhí)行器會(huì)根據(jù)查詢(xún)條件過(guò)濾后的結(jié)果自動(dòng)選擇驅(qū)動(dòng)表或被驅(qū)動(dòng)表，這篇文章主要介紹了MySQL的驅(qū)動(dòng)表與被驅(qū)動(dòng)表,需要的朋友可以參考下
    2023-10-10
  • Mysql字符集utf8和utf8mb4詳解

    Mysql字符集utf8和utf8mb4詳解

    文章介紹了MySQL中utf8和utf8mb4兩種字符集的區(qū)別,包括編碼方式、存儲(chǔ)空間、索引長(zhǎng)度以及支持的Unicode字符范圍,同時(shí),通過(guò)創(chuàng)建兩個(gè)表并插入數(shù)據(jù)進(jìn)行存儲(chǔ)長(zhǎng)度的比較,驗(yàn)證了上述理論
    2024-12-12
  • MySQL鎖等待與死鎖問(wèn)題分析

    MySQL鎖等待與死鎖問(wèn)題分析

    這篇文章主要介紹了MySQL鎖等待與死鎖問(wèn)題分析，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下
    2021-03-03
  • mysql 8.0.17 安裝與使用教程圖解

    mysql 8.0.17 安裝與使用教程圖解

    這篇文章主要介紹了mysql 8.0.17 安裝與使用教程圖解，本文圖文并茂給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2019-08-08
  • MySQL 8.0.18 穩(wěn)定版發(fā)布! Hash Join如期而至

    MySQL 8.0.18 穩(wěn)定版發(fā)布! Hash Join如期而至

    MySQL 8.0.18 穩(wěn)定版發(fā)布! Hash Join 如期而至，這篇文章帶大家快速瀏覽一下MySQL 8.0.18 穩(wěn)定版的各個(gè)亮點(diǎn)，感興趣的小伙伴們可以學(xué)習(xí)參考一下
    2019-10-10
  • 解決mybatis查詢(xún)結(jié)果為null時(shí),值被默認(rèn)值替換問(wèn)題

    解決mybatis查詢(xún)結(jié)果為null時(shí),值被默認(rèn)值替換問(wèn)題

    這篇文章主要介紹了解決mybatis查詢(xún)結(jié)果為null時(shí),值被默認(rèn)值替換問(wèn)題。具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
    2022-07-07
  • 超出MySQL最大連接數(shù)問(wèn)題及解決

    超出MySQL最大連接數(shù)問(wèn)題及解決

    這篇文章主要介紹了超出MySQL最大連接數(shù)問(wèn)題及解決方案，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
    2023-05-05
  • mysql 常用命令集錦[絕對(duì)精華]

    mysql 常用命令集錦[絕對(duì)精華]

    測(cè)試環(huán)境：mysql 5.0.45 【注：可以在mysql中通過(guò)mysql> SELECT VERSION();來(lái)查看數(shù)據(jù)庫(kù)版本】
    2009-06-06
  • MYSQL主從不同步延遲原理分析及解決方案

    MYSQL主從不同步延遲原理分析及解決方案

    這篇文章介紹了MYSQL主從不同步延遲原理分析及解決方案，有需要的朋友可以參考一下
    2013-09-09

最新評(píng)論