因為一個字元校對問題，我的大廠面試挂了

摘要：字元集是一套符号和編碼。校對規則是在字元集内用于比較字元的一套規則。 

本文分享自華為雲社群《一個字元校對規則引發的血案》，原文作者：DRS技術快客 。

問題現場

我們先看一個建表語句

CREATE TABLE collate_test (
val1 char(32) COLLATE utf8mb4_general_ci,
val2 char(32)
) CHARACTER SET utf8mb4;      

當我們在MySQL5.7和MySQL8.0上建表，都能建成功，但是當建成功之後，我們都執行SQL：SELECT * FROM collate_test WHERE val1=val2的時候：

在5.7上執行

mysql> SELECT * FROM collate_test WHERE val1=val2;
Empty set (0.00 sec)      

在8.0上執行

mysql> SELECT * FROM collate_test WHERE val1=val2;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='      

很奇怪，為什麼會出現utf8mb4_0900_ai_ci呢？

我們檢視MySQL的資料https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html 發現，原來MySQL8.0在UTF8mb4字元集下面的預設排序規則為utf8mb4_0900_ai_ci

現場分析

然後我們再分别來看一下建表語句：SHOW CREATE TABLE collate_test

show create table collate_test;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                         |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
| collate_test | CREATE TABLE `collate_test` (
  `val1` char(32) DEFAULT NULL,
  `val2` char(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+      

show create table collate_test;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                         |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
| collate_test | CREATE TABLE `collate_test` (
  `val1` char(32) DEFAULT NULL,
  `val2` char(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+      

看出來差别了，8.0上建表的時候，被加上了collate屬性

在8.0執行

mysql> SHOW CHARACTER SET WHERE Charset="utf8mb4";
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci |      4 |
+---------+---------------+--------------------+--------+
1 row in set (0.01 sec)      

原來8.0中建表的時候，當指定字元集為utf8mb4的時候，它的預設collation就是utf8mb4_0900_ai_ci，而mysql不允許兩個互斥的校驗規則的資料做對比，而utf8mb4_0900_ai_ci與utf8mb4_general_ci是互斥的

擴充問題

這裡面問題比較簡單，因為一般我們不會對同一個表的不同字段設定相同字元集不同校對規則，但是在不同的表結構之前，我們有可能不經意之間就犯了這個錯誤，例如，聯表，觸發器等。

聯表查詢

比如下面兩個表

CREATE TABLE collate_general(
val1 char(32)
) COLLATE utf8mb4_general_ci;

CREATE TABLE collate_0900 (
val2 char(32)
) COLLATE utf8mb4_0900_ai_ci;      

當我們聯表查詢的時候

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

觸發器

比如我們先建一個表和觸發器(為舉例需要，觸發器并無實際意義)

CREATE TABLE collate_trigger(
val1 char(32)
) COLLATE utf8mb4_general_ci;

DELIMITER ||
CREATE TRIGGER trigger_0900 AFTER INSERT ON collate_trigger FOR EACH ROW
BEGIN
    DECLARE val2 VARCHAR(32);
    SET val2=new.val1;
    SELECT val1 into val2 from collate_trigger WHERE val1=val2;
END||
DELIMITER ;      

當我們向表中插入資料的時候

mysql> insert into collate_trigger values ('abc');
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='      

然後我們看一下建表語句

mysql> show create table collate_trigger;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                  |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| collate_trigger | CREATE TABLE `collate_trigger` (
  `val1` char(32) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+      

我們再看一下觸發器

mysql> show create trigger trigger_0900\G
*************************** 1. row ***************************
               Trigger: trigger_0900
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `trigger_0900` AFTER INSERT ON `collate_trigger` FOR EACH ROW BEGIN
    DECLARE val2 VARCHAR(32);
    SET val2=new.val1;
    SELECT val1 into val2 from collate_trigger WHERE val1=val2;
END
  character_set_client: gbk
  collation_connection: gbk_chinese_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2021-05-31 15:24:44.40      

發現沒有，觸發器的Database collation為utf8mb4_0900_ai_ci，在觸發器的比較語句中，val1為collate_trigger的字段，collation為utf8mb4_general_ci，val2為觸發器trigger_0900的自有字段，collation為utf8mb4_0900_ai_ci。

本文中舉例都比較簡單直接，客戶真實業務場景可能都比較複雜，但是所遇問題的原因都是一樣的。由此可見，在處理MySQL之前的版本更新到8.0版本的時候，字元集校驗規則一定要注意了

點選關注，第一時間了解華為雲新鮮技術~