Mysql字元集和排序規則詳解

2022-09-30 14:02:43

前言

計算機儲存資料的方式都是二進位制資料，但是我們在mysql中儲存的是字串資料，那麼這些資料到底在Mysql中如何儲存呢？這就涉及到字元集的概念。

什麼是字元集

舉例如下，假設存在編碼集test，只能識別a,b,A,B這幾項，同時編碼規則如下：

a---->00000001（0x01）
b---->00000010（0x02）
A---->00000011（0x03）
B---->00000100（0x04）

那麼字串'aAB'可以對映為 000000010000001100000100（十六進位制 0x10304）

而ac將無法轉換，因為超出了識別範圍。

上面提到的將字串根據字元集對映為二進位制的過程稱之為編碼，反之就為解碼。

什麼是比較規則

那什麼是比較規則呢？其實就是字串的比較規則，如上舉例而言，可以考慮比較二進位制編碼的大小，如字元'a'編碼值的十六進位製為0x01，字元'b'編碼值的十六進位製為0x02，那麼可以確定a<b，這就是二進位制比較規則，但是顯然這種規則適用範圍有限，如果遇到需要忽略大小寫的場景，顯然二進位制編碼比較規則並不適用，一個字元集可能對應多種比較規則。

常用字元集

根據上面的舉例我們知道了test字元集描述的就是一個字元範圍的界定，我們常用的字元集還有很多如下所示

ASCII字元集

這是一個最簡單的字元集包含128個字元包含大小寫字母，空格，數位，標點符號，特殊字元等，因為總共只有128個字元，那麼可以只採用一個位元組編碼。

ISO8859-1

在ASCII字元集上擴充了128個西歐常用字元，總共包含256個字元，這個字元集又被稱為latin1。

GB2312

收錄漢字以及拉丁字母，希臘字母等，這種字元集相容ASCII字元，在編碼規則如下：

如果是ASCII編碼範圍的字元，採用一個位元組編碼
否則採用兩個位元組編碼

這種情況編碼長度不確定的被稱為變長編碼。

GBK字元集

GBK對GB2312的字元範圍進行的擴充，相容GB2312。

Unicode字元集

UniCode收錄了地球上的所有字元，是所有國家通用的字元集，由此又被稱為萬國碼，UniCode的編碼規則包含UTF8、UTF16、UTF32這幾種編碼方案，其中UTF8採用1~4個位元組編碼一個字元，UTF16使用了2個位元組或者4個位元組編碼一個字元，UTF32採用4個位元組編碼一個字元。

不過需要注意的是Mysql中沒有區分編碼方案和字元集，所以後續的uft8可以看作字元集。

注意點

在mysql中有兩個特殊的字元集：

utf8mb3：是UniCode字元集編碼方案中utf8的閹割版本，採用的是1~3個位元組編碼一個字元，它的另外一個名字就是utf8也就是說mysql中使用的就是閹割版本的utf8字元集。
**utf8mb4：**採用4個位元組編碼一個字元，最常見的就是儲存emoji表情。

Mysql中查詢字元集和比較規則

查詢字元集

查詢字元集命令可以採用show character set;或者show charset;都可以實現字元集檢視，可以支援字元集模糊查詢如show character set like '%utf8%';

##### mysql5.7.26版本中，包含41個字元集，下面列出常用的幾個
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
........
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

查詢比較規則

每個字元集都會對應一些比較規則，查詢所有字元集的比較規則顯然是太多了，所以這裡以utf8字元集為例查詢比較規則。

#### mysql5.7.26版本中utf8字元集對應27個比較規則，其餘省略
mysql> show collation like '%utf8_%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 |
.........
+--------------------------+---------+-----+---------+----------+---------+
27 rows in set (0.00 sec)

Default列為YES表明該比較規則是預設的，一般比較規則中包含general代表通用比較規則。

比較規則一般命名如規律如下：