一文帶你瞭解MySQL字元集和比較規則

2022-12-31 14:02:32

1. MySQL中的utf8和utf8mb4

在MySQL中，我經常會看見utf8和utf8mb4這兩種編碼格式，但是這兩種格式我並不瞭解，一般建立資料庫的時候，如果讓我選，我預設會選擇utf8，但是這個和UTF-8長得最像。

但是實際上，這兩種是有區別的。

utf8：utf8實際上是utf8mb3的別名，它是割版的UTF-8字元集，只使用1-3位元組表示字元。
utf8mb4：這個才是正宗的UTF-8字元集，使用1-4位元組表示字元。

所以，如果想要在程式中儲存emoji表情的話，是需要使用utf8mb4字元集的，因為emoji表情是需要使用4位元組編碼一個字元。

在MySQL8.0中，已經極大程度優化了utf8mb4字元集的效能，並將其設定為預設字元集

2. MySQL中的比較規則

比較規則是指對於某種字元集來說，制定用來比較字元大小的多種規則，一種字元集多種規則，一個預設規則。

在MySQL中，可以通過以下命令檢視比較規則

show collation [like 匹配的模式];

這些命名是有規律的。

比較規則的名稱與其關聯的字元集的名稱開頭，比如上述比較規則有些以utf8mb3開頭
後面緊接著該比較規則所應用的語言。比如utf8mb3_roman_ci表示羅馬語的比較規則
名稱的字尾意味這該比較規則是否區分語言中的重音、大小寫等。

字尾	描述
_ai	不區分重音
_as	區分重音
_ci	不區分大小寫
_cs	區分大小寫
_bin	以二進位制方式進行比較

3. 各級別的字元集和比較規則

在MySQL中有四個級別的字元集和比較規則。

伺服器級別
資料庫級別
表級別
列級別

3.1 伺服器級別

MySQL提供了兩種系統變數表示伺服器級別的字元集和比較規則

系統變數	描述
character_set_server	伺服器級別的字元集
collation_server	伺服器級別的比較規則

可以通過SET語句來改變這兩個系統變數。

也可以通過修改組態檔來修改

3.2 資料庫級別

在建立和修改資料庫時候可以指定資料庫的字元集和比較規則

如果想要檢視當前資料庫的字元集和比較規則，可以檢視下面兩個系統變數的值

系統變數	描述
character_set_database	當前資料庫的字元集
collation_database	當前資料庫的比較規則

需要注意的是，這兩個系統變數並不同與描述伺服器級別的字元集和比較規則的那兩個比較規則，這兩個系統變數只是用來告訴使用者當前資料庫的字元集和比較規則是什麼，並不能通過修改兩個變數的值來修改檔期按資料庫的字元集和比較規則。

如果在建立資料庫的時候不指定字元集和比較規則，那麼將會使用伺服器級別的字元集和比較規則來作為資料庫的字元集和比較規則。

3.3 表級別

可以在建立和修改表的時候指定表的字元集和比較規則

如果建立表的語句沒有指定字元集和比較規則，則會使用該表所在資料庫的字元集和比較規則作為該表的字元集和比較規則。

3.4 列級別

可以在建立和修改列的時候指定該列的字元集和比較規則

對於某一列來說，如果建立和修改表的語句沒有指定字元集和比較規則，則會使用該列所在的表的字元集和比較規則作為其字元集和比較規則。

注意：如果在修改列的字元集的時候，如果儲存的資料不能使用修改後的字元集進行表示，則會報錯

4. 修改字元集或比較規則

字元集和比較規則之間是相互聯絡的，只修改其一需要遵循以下規則：

只修改字元集，則比較規則將變為修改後的字元集的預設比較規則。
只修改比較規則，則字元集將變為修改後的比較規則對應的字元集。

5. MySQL中的字元集轉換過程

在使用者端傳送請求的時候，一般來說，使用者端編碼請求字串時使用的字元集與作業系統當前使用的字元集一致。

但是如果MySQL使用者端設定了default-character-set啟動選項的時候，那麼伺服器就會忽視作業系統當前使用的字元集。

而在伺服器接收請求的時候，伺服器接收到的請求實際上就是一個位元組序列。伺服器會將這個位元組序列看作是使用系統變數character_set_client表示的字元集編碼的位元組序列。

注意的是，這個系統變數character_set_client是session級別的

使用者端傳送請求的字元集和伺服器接收請求的字元集是兩個獨立的字元集，一般來說，使用者端傳送請求所用字元集應和伺服器接收請求的字元集應保持一致，但是也會出現不一致的時候

如果伺服器的character_set_client對應的字元集不能解釋請求的位元組序列，那麼伺服器就會發出警告。

在伺服器處理請求的時候，會將伺服器接收請求的character_set_client對應的字元集進行編碼的位元組序列轉換成session級別的系統變數character_set_connection對應的字元集進行編碼的位元組序列。

對於character_set_connection我們可以通過SET命令單獨修改此係統變數，除了character_set_connection，還有一個與其對應的系統變數collation_connection，這個系統變數表示了這些字串應使用哪種比較規則。

同樣這個系統變數collation_connection可以通過SET命令進行修改。

如果使用者端傳送請求、伺服器接收請求和伺服器處理請求這一過程中，字元集和比較規則不一樣，會出現什麼情況呢？

就這個表來說

向用戶端傳送一條查詢語句

由於當前作業系統使用的是gbk字元集，所以字串‘我’是使用GBK字元集進行編碼的，比較規則為gbk_chinese_ci，而列C使用的是utf8字元集編碼的，比較規則為utf8_general_ci。

那麼在這種情況下，列的字元集和排序規則的優先順序更高，也就是說這裡需要將‘我’從GBK字元集轉換成utf8字元集，然後使用列c的比較規則進行比較。

當伺服器生成響應的時候，是不是直接將列c也就是utf8字元集進行編碼的結果傳送回使用者端呢？

答案是不一定，這取決與SESSION級別的系統變數character_set_results的值，結果會從utf8字元集編碼的轉換成

character_set_results對應的字元編碼後的位元組序列

系統變數	描述
character_set_client	伺服器認為請求是按照系統變數指定的字元集進行編碼的
character_set_connection	伺服器在處理請求時，會將請求字元序列從character_set_client轉換成character_set_connection
character_set_results	伺服器採用該系統變數指定的字元集對返回使用者端的字串進行編碼

由於三個變數均為SESSION級別，因此每個使用者端與伺服器建立連線的時候都會維護這三個變數。