MySQL字元集GBK轉換到UTF8

MySQL字元集GBK轉換到UTF8

在生產環境中，MySQL資料庫字元集因為各種原因需要升級，比如為了支援漢字，從latin1字元集升級到GBK，後面為了支援多個語言文字，需要將GBK升級到UTF8等。遷移過程網上有很多，我今天主要想講下字元集轉換後，可能對業務產生的影響，我以GBK轉換到UTF8為例說明。

主要有兩點：

1.漢字在GBK編碼中佔2個位元組，在UTF8編碼中佔3個位元組，而mysql的索引要求總長度不超過767個位元組，因此索引字元數會被縮短(383->255)，特別的，對於唯一索引，要求索引欄位長度小於256個字元。

2.編碼轉換後，導致欄位排序發生變化。

這篇文章主要為了說明編碼轉換後，欄位排序如何受影響，會結合mysql原始碼給出原因和分析。首先看測試案例，假設cmp_t(GBK編碼)和cmp_t2(UTF8編碼)分別是遷移前後的表。

測試案例：

操作	cmp_t(GBK)	cmp_t2(UTF8)
1	GBK表： select c1,hex(c1) from cmp_t;   UTF8表： select c1,hex(c1) from cmp_t2;	+------+---------+ | c1   | hex(c1) | +------+---------+ | 一  | D2BB    | 二  | B6FE | 三  | C8FD    | a    | 61      | 1    | 31 +------+---------+	+------+---------+ | c1   | hex(c1) | +------+---------+ | 一  | E4B880  | 二  | E4BA8C  | 三  | E4B889  | a    | 61      | 1    | 31      +------+---------+
2	GBK表： select c1,hex(c1) from cmp_t where c1>’a’ order by c1;   UTF8表： select c1,hex(c1) from cmp_t2 where c1>’a’ order by c1;	+------+---------+ | c1   | hex(c1) | +------+---------+ | 二  | B6FE    | | 三  | C8FD    | 一  | D2BB    +------+---------+	+------+---------+ | c1   | hex(c1) | +------+---------+ | 一  | E4B880  | 三  | E4B889  | 二  | E4BA8C  +------+---------+

從上面操作返回的結果我們可以得到以下幾點資訊：

1. 漢字在GBK編碼中佔2個位元組，在UTF8編碼中佔3個位元組
2. 數字和英文字元在GBK和UTF8編碼中都只佔一個位元組
3. 漢字在UTF8編碼和GBK編碼不同，排序後順序變化了。

原理分析：

Mysql利用sortcmp函數對字串進行比較，對於GBK的字串和UTF8的字串分別採用介面my_strnncollsp_gbk和my_strnncollsp_utf8比較，這兩個函數分別在ctype-gbk.c和ctype-utf8.c中實現，兩個函數實現邏輯類似，只是各有自己一套比較大小的規則，下面我主要描述下my_strnncollsp_utf8的比較邏輯和比較大小的規則。

比較邏輯：

1. 擷取字串的第一個位元組
2. 根據UTF8的編碼規則(附1)，確定字元由幾個位元組組成
3. 根據一定的演算法算出字元的加權值(附2)，比較大小；若不符合UTF8編碼規範，認為是亂碼，採用二進位比較(附3)
4. 跳過步驟2返回的位元組數，比較下一個字元。

附1：【介面: my_utf8_uni】

根據UTF8編碼規則，符合編碼規範的字元佔用1-6個位元組。

取字串第一個位元組 s

if s<0x80

     表示字元佔1個位元組

elif s<0xe0

    表示字元佔2個位元組

elif s<0xf0 

    表示字元佔3個位元組

else s<0xf8

    表示字元佔4個位元組

elif s<0xfc

   表示字元佔5個位元組

elif s<0xfe

   表示字元佔6個位元組 

英文字元和數字字元編碼相容ASCII，編碼值小於0x80，因此都只佔1個位元組；漢字的utf8編碼的首位元組都在[0xe0，0xf0]之間，所以佔3個位元組。

附2：utf8編碼比較大小規則

value = ((s[0] & 0x0f) << 12)| ((s[1] ^ 0x80) << 6) | (s[2] ^ 0x80)

s[0],s[1],s[2]表示組成漢字的三個位元組，對參與比較的漢子字元進行計算得到value1和value2，通過比較value1和value2的大小，判斷字元大小。

附3：二進位比較【介面: bincmp】

memcmp函數比較，即逐位元組比較。

因此，如果業務上面需要依賴漢字比較的情境，需要考慮字元集升級(GBK->UTF8)的風險，主要是索引或主鍵中包含字串欄位需要特別關注，如果字串中確定只包含有數字和字元，則不會存在問題。

本文永久更新連結地址：