故障分析 | MySQL convert 函式導致的字符集報錯處理

一、問題背景

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

二、問題模擬

mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `name1` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `name2` varchar(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> CREATE VIEW t3 as select * from t1,t2 where `t1`.`name1`= `t2`.`name2`;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t3;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

三、問題分析

透過檢視檢視定義，可以發現由於檢視中涉及到的兩張表字符集不同，所以建立檢視時 MySQL 會自動使用 convert 函式轉換字符集。

mysql> show create view t3\G;
*************************** 1. row ***************************
                View: t3
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t3` AS select `t1`.`name1` AS `name1`,`t2`.`name2` AS `name2` from (`t1` join `t2`) where (`t1`.`name1` = convert(`t2`.`name2` using utf8mb4))
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)

在 MySQL 8.0 中 utf8mb4 的預設排序規則為 utf8mb4_0900_ai_ci ，而在 t1 表的排序規則為 utf8mb4_general_ci ，那麼我們試著將排序規則相關的引數修改後再執行 SQL 看看，修改後的環境引數如下

mysql> show variables like '%collat%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_bin        |
| collation_server              | utf8mb4_bin        |
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+

再次執行 sql 發現還是會報一樣的錯。

mysql> select * from t1,t2 where `t1`.`name1`=convert(`t2`.`name2` using utf8mb4);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

透過 show collation 來檢視 utf8mb4 字符集對應的預設排序規則，輸出顯示預設規則為 utf8mb4_general_ci ，並不是 utf8mb4_0900_ai_ci 。

mysql> show collation like '%utf8mb4%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_general_ci         | utf8mb4 |  45 | Yes     | Yes      |       1 | PAD SPACE     |
+----------------------------+---------+-----+---------+----------+---------+---------------+

mysql> show character set like '%utf8mb4%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
1 row in set (0.00 sec)

繼續排查發現後設資料中的字符集預設排序規則如下，預設規則為 utf8mb4_0900_ai_ci 。

mysql>  select * from INFORMATION_SCHEMA.COLLATIONS where IS_DEFAULT='Yes' and CHARACTER_SET_NAME='utf8mb4'\G;
*************************** 1. row ***************************
    COLLATION_NAME: utf8mb4_0900_ai_ci
CHARACTER_SET_NAME: utf8mb4
                ID: 255
        IS_DEFAULT: Yes
       IS_COMPILED: Yes
           SORTLEN: 0
     PAD_ATTRIBUTE: NO PAD
1 row in set (0.00 sec)

檢查引數發現，後設資料資訊中 utf8mb4 字符集預設排序規則是 utf8mb4_0900_ai_ci ，show collation/show character 輸出的都是 utf8mb4_general_ci 。為什麼 show 顯示的結果和 INFORMATION_SCHEMA.COLLATIONS 表查到的資訊還不一樣呢？此處我們暫且按下不表，我們們先看看官方文件中 convert 函式用法，其中有下面這段原文：

If you specify CHARACTER SET charset_name as just shown, the character set and collation of the result are charset_name and the default collation of charset_name. If you omit CHARACTER SET charset_name, the character set and collation of the result are defined by the character_set_connection and collation_connection system variables that determine the default connection character set and collation (see Section 10.4, “Connection Character Sets and Collations”).

1. SHOW COLLATION and SHOW CHARACTER SET.
2. CREATE TABLE and ALTER TABLE having a CHARACTER SET utf8mb4 clause without a COLLATION clause, either for the table character set or for a column character set.
3. CREATE DATABASE and ALTER DATABASE having a CHARACTER SET utf8mb4 clause without a COLLATION clause.
4. Any statement containing a string literal of the form _utf8mb4'some text' without a COLLATE clause.

將 convert 函式指定為 t1.name1 欄位的排序規則後，sql 執行正常。

mysql> select * from t1,t2 where `t1`.`name1` = convert(`t2`.`name2` using utf8mb4) collate utf8mb4_general_ci;
+-------+-------+
| name1 | name2 |
+-------+-------+
| jack  | jack  |
+-------+-------+
1 row in set (0.00 sec)

另外，下面測試可以驗證 default_collation_for_utf8mb4 的第四個場景。

mysql> select * from INFORMATION_SCHEMA.COLLATIONS where IS_DEFAULT='Yes' and CHARACTER_SET_NAME='utf8mb4'\G;
*************************** 1. row ***************************
    COLLATION_NAME: utf8mb4_0900_ai_ci
CHARACTER_SET_NAME: utf8mb4
                ID: 255
        IS_DEFAULT: Yes
       IS_COMPILED: Yes
           SORTLEN: 0
     PAD_ATTRIBUTE: NO PAD
1 row in set (0.00 sec)

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

mysql> set @s1 = _utf8mb4 'jack',@s2 = _utf8mb4 'jack';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @s1 = @s2;
+-----------+
| @s1 = @s2 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
_utf8mb4宣告的@s1和@s2排序規則是default_collation_for_utf8mb4引數值，為utf8mb4_general_ci

mysql> SELECT @s1 = CONVERT(@s2 USING utf8mb4);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
此時，經過CONVERT函式處理的@s2排序規則是utf8mb4_0900_ai_ci，所以會報錯

mysql> SELECT @s1 = CONVERT(@s2 USING utf8mb4) collate utf8mb4_general_ci;
+-------------------------------------------------------------+
| @s1 = CONVERT(@s2 USING utf8mb4) collate utf8mb4_general_ci |
+-------------------------------------------------------------+
|                                                           1 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

四、問題總結

• 建立資料庫例項時需指定引數 character_set_database（預設值：utf8mb4），character_set_server（預設值：utf8mb4）。

• 當需要建立非預設字符集 database / table 時，需要在 sql 中明確指定字符集和排序規則。

• 使用 convert 函式轉換字符集時，當欄位排序規則不是轉換後字符集的預設排序規則，需要指定具體的排序規則。SELECT @s1 = CONVERT(@s2 USING utf8mb4) collate utf8mb4_general_ci

• MySQL 5.7 遷移至 MySQL 8.0 時，需注意 MySQL 5.7 版本中 utf8mb4 預設排序規則是 utf8mb4_general_ci ，MySQL 8.0 中 utf8mb4 預設排序規則是  utf8mb4_0900_ai_ci 。

參考