MySQL中utf8mb4排序規(guī)則示例

更新時(shí)間：2021年07月29日 14:34:00 作者：TeyGao

本文主要介紹了MySQL中utf8mb4排序規(guī)則，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

在MySQL中常見(jiàn)的utf8mb4排序規(guī)則有：

utf8mb4_0900_ai_ci
utf8mb4_unicode_ci
utf8mb4_general_ci

當(dāng)設(shè)置表的默認(rèn)字符集為utf8mb4字符集但未明確指定排序規(guī)則時(shí)：

在MySQL 5.7版本中，默認(rèn)排序規(guī)則為utf8mb4_general_ci。
在MySQL 8.0版本中，默認(rèn)排序規(guī)則為utf8mb4_0900_ai_ci。

由于utf8mb4_0900_ai_ci排序規(guī)則時(shí)MySQL 8.0引入的排序規(guī)則，因此將MySQL 8.0版本的表導(dǎo)入到MySQL 5.7或MySQL 5.6版本時(shí)，會(huì)存在字符集無(wú)法識(shí)別的問(wèn)題。

[Err] 1273 - Unknown collation: 'utf8mb4_0900_ai_ci'

參數(shù)控制

在MySQL 5.6版本中，參數(shù)collation_server用于設(shè)置服務(wù)器級(jí)別的默認(rèn)排序規(guī)則。

如果服務(wù)啟動(dòng)時(shí)未指定參數(shù)collation_database的值，則默認(rèn)繼承參數(shù)collation_server的值。
如果創(chuàng)建數(shù)據(jù)庫(kù)時(shí)未指定排序規(guī)則，則默認(rèn)使用參數(shù)collation_database的值。

參數(shù)character_set_database和collation_database在MySQL 5.7版本中被遺棄并將在后續(xù)版本中移除。
MySQL新增參數(shù)default_collation_for_utf8mb4用于控制使用utf8mb4字符集時(shí)的默認(rèn)排序規(guī)則，取值為utf8mb4_0900_ai_ci或utf8mb4_general_ci
參數(shù)default_collation_for_utf8mb4在下列條件中生效：

使用SHOW COLLATION and SHOW CHARACTER SET 命令時(shí)。
在創(chuàng)建庫(kù)或修改庫(kù)指定utf8mb4但未指定編碼規(guī)則時(shí)。
在創(chuàng)建表或修改表指定utf8mb4但未指定編碼規(guī)則時(shí)。
在增加列或修改列指定utf8mb4但未指定編碼規(guī)則時(shí)。
其他使用utf8mb4但未指定編碼規(guī)則時(shí)。

utf8mb4_unicode_ci和utf8mb4_general_ci對(duì)比

1、準(zhǔn)確性

utf8mb4_unicode_ci排序規(guī)則基于標(biāo)準(zhǔn)unicode進(jìn)行排序和比較，能處理特殊的字符，能在各種語(yǔ)音中精確排序。
utf8mb4_general_ci排序規(guī)則沒(méi)有基于標(biāo)準(zhǔn)unicode，無(wú)法處理部分特殊字符。

2、性能

utf8mb4_general_ci排序規(guī)則在排序性能上相對(duì)較好
utf8mb4_unicode_ci排序規(guī)則為處理特殊字符實(shí)現(xiàn)復(fù)雜的排序算法，性能略差
在大部分場(chǎng)景下，兩者沒(méi)有明顯的性能差異

補(bǔ)：MySQL--utf8mb4與等值查詢測(cè)試

測(cè)試腳本

## 刪除測(cè)試表
DROP TABLE IF EXISTS tb2001;
DROP TABLE IF EXISTS tb2002;
DROP TABLE IF EXISTS tb2003;

## 創(chuàng)建測(cè)試表
CREATE TABLE tb2001(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(100) COLLATE utf8mb4_unicode_ci,
c2 VARCHAR(100) COLLATE utf8mb4_bin
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 ;

CREATE TABLE tb2002(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(100) COLLATE utf8mb4_general_ci,
c2 VARCHAR(100) COLLATE utf8mb4_bin
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tb2003(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(100) COLLATE utf8mb4_0900_ai_ci,
c2 VARCHAR(100) COLLATE utf8mb4_bin
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

## 插入測(cè)試數(shù)據(jù)
INSERT INTO tb2001(c1,c2)VALUES(0xF09F8D83,0xF09F8D83),(0xF09FA68A,0xF09FA68A),(0xF09F8CA0,0xF09F8CA0);
INSERT INTO tb2002(c1,c2)VALUES(0xF09F8D83,0xF09F8D83),(0xF09FA68A,0xF09FA68A),(0xF09F8CA0,0xF09F8CA0);
INSERT INTO tb2003(c1,c2)VALUES(0xF09F8D83,0xF09F8D83),(0xF09FA68A,0xF09FA68A),(0xF09F8CA0,0xF09F8CA0);

## 等值查詢測(cè)試
SELECT * FROM tb2001 WHERE c1=0xF09F8D83;
SELECT * FROM tb2002 WHERE c1=0xF09F8D83;
SELECT * FROM tb2003 WHERE c1=0xF09F8D83;

SELECT * FROM tb2001 WHERE c2=0xF09F8D83;
SELECT * FROM tb2002 WHERE c2=0xF09F8D83;
SELECT * FROM tb2003 WHERE c2=0xF09F8D83;

測(cè)試結(jié)果

mysql> SELECT * FROM tb2001 WHERE c1=0xF09F8D83;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | 🍃     | 🍃     |
|  2 | 🦊     | 🦊     |
|  3 | 🌠     | 🌠     |
+----+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tb2002 WHERE c1=0xF09F8D83;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | 🍃     | 🍃     |
|  2 | 🦊     | 🦊     |
|  3 | 🌠     | 🌠     |
+----+------+------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM tb2003 WHERE c1=0xF09F8D83;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | 🍃     | 🍃     |
+----+------+------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT * FROM tb2001 WHERE c2=0xF09F8D83;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | 🍃     | 🍃     |
+----+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb2002 WHERE c2=0xF09F8D83;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | 🍃     | 🍃     |
+----+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb2003 WHERE c2=0xF09F8D83;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | 🍃     | 🍃     |
+----+------+------+
1 row in set (0.00 sec)

測(cè)試總結(jié)