作者：傅同學

愛可生研發部成員，主要負責中間件產品開發，熱衷技術原理。

本文來源：原創投稿

*愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。

本公眾號之前發表了一些關於MySQL字符集的文章: 從utf8轉換成utf8mb4 、 字符集相關概念 、 有關SQL 語句 、 字符集註意事項 、 亂碼問題.

近日, 在為 愛可生開源數據傳輸工具dtle 增加UTF-32字符集支持過程中，筆者又瞭解了一下 MySQL 字符集機制。下面補充説明一些內容：

1 字符集

• ASCII 是最基礎的字符集，每個 ASCII 字符佔用1字節，ASCII 僅利用了8位編碼能力的一半，最高位恆為 0 。

  • Latin1 字符集利用剩下的一半編碼了西歐常用字母。

• UCS-2 使用2字節編碼，容量為65536 。

  • UTF-16 在這基礎上, 增加了臨時使用2個2字節，編碼特殊字符的能力。
• UTF-32 使用定長4字節編碼。

• GB 系列、UTF-8 等字符集，0~127編碼和 ASCII 一樣，使用單字節。在最高位不為0時，額外使用1~3字節編碼。

  • 即它們是變長編碼，每個字符佔用1~4字節

只表達 ASCII 0~127字符的話，Latin1、GB系列、UTF8 等編碼是完全相同的. 而 UTF16 和 UTF32 則會有額外的空白。

mysql> select
         hex(convert("sql" using latin1)) as a,
         hex(convert("sql" using gbk)) as b,
         hex(convert("sql" using utf8mb4)) as c,
         hex(convert("sql" using utf16)) as d,
         hex(convert("sql" using utf32)) as e;

| a      | b      | c      | d            | e                        |
+--------+--------+--------+--------------+--------------------------+
| 73716C | 73716C | 73716C | 00730071006C | 00000073000000710000006C |

使用如下 SQL 語句可以看到當前 MySQL 實例支持的字符集(和collation)。

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME, ID FROM INFORMATION_SCHEMA.COLLATIONS
         ORDER BY CHARACTER_SET_NAME, ID;
...
| gb18030            | gb18030_chinese_ci       | 248 |
| gb18030            | gb18030_bin              | 249 |
| gb18030            | gb18030_unicode_520_ci   | 250 |
...
| utf8mb4            | utf8mb4_general_ci       |  45 |
| utf8mb4            | utf8mb4_bin              |  46 |
| utf8mb4            | utf8mb4_unicode_ci       | 224 |
...

題外：什麼是 collation

A collation is a set of rules that defines how to compare and sort character strings.

MySQL文檔: collation是進行字符串比較或排序時使用的規則. 例如:

• 大小寫是否敏感(<u>c</u>ase <u>s</u>ensitive / <u>i</u>nsensitive)
• 特殊的相等規則: ij = ij

2 MySQL 系列參數

執行 SQL show variables like 'character%' 可以獲得MySQL中關於字符集的幾個參數。

character_set_client
character_set_connection
character_set_results
character_set_server
-- 注: 下面3個參數一般無需關心
character_set_filesystem
character_set_database
character_set_system

第06期：梳理 MySQL 字符集的相關概念 中“ 六、字符集系統參數”一節進行了解釋，但稍有誤差。

2.1 參數 character_set_client

• character_set_client 並不是客户端使用的字符集.
• 用户輸入什麼數據, 客户端就發送什麼數據. 字符集由用户輸入決定.
• character_set_client 是服務端的參數.
• 服務端認為客户端發來的數據是以 character_set_client 編碼的.

character_set_client不能被設置為以下幾個字符集 (參考鏈接):

ucs2
utf16 / utf16le
utf32

不難發現其共同點為: 都是多字節編碼. 至於限制的原因本文不作展開。

2.2 參數 character_set_connection

根據MySQL文檔

(after receiving them) The server converts statements sent by the client from character_set_client to character_set_connection. Exception: For string literals that have an introducer such as _utf8mb4 or _latin2...

即收到客户端發來的語句後，服務端(mysqld)會將語句從character_set_client轉換為character_set_connection。(除了指明字符集的字符串字面量, 如 _utf8mb4"中文字符串".)

2.3 SET NAMES 語句

mysql> SET NAMES gb2312;

MySQL文檔: 該語句設定3個關於 character set 的變量：character_set_client, character_set_connection 和 character_set_results. 該語句不設置 character_set_server 。

注： MySQL客户端的--default-character-set參數效果和SET NAMES語句一樣。

$ mysql -h ... --default-character-set=gb2312

3 一個亂碼原因的勘誤

之前發表的 第09期：有關 MySQL 字符集的亂碼問題 “一、轉碼失敗”一節中有如下案例

-- 我的終端字符集是 utf8
...

-- 新建立一個連接，客户端這邊字符集為 gb2312
root@ytt-pc:/home/ytt# mysql --default-character-set=gb2312 ...

...
-- 表的字符集為 utf8
mysql> create database ytt_new10;
mysql> create table ytt_new10.t1(a1 varchar(100)) charset utf8mb4;

-- 插入一條數據，有兩條警告信息
mysql> insert into ytt_new10.t1 values ("病毒滾吧！");
Query OK, 1 row affected, 2 warnings (0.01 sec)
...

-- 那檢索出來看到，數據已經不可逆的亂碼了。
mysql> select * from ytt_new10.t1;
| a1        |
+-----------+
| ???▒??▒ |

該文認為亂碼原因是“客户端編碼設置成和表編碼不一致”。

但可以發現：即使客户端和表的編碼都是 gb2312 ，仍然會產生亂碼。

-- 在上述gb2312 MySQL session中
mysql> create table ytt_new10.t2(a1 varchar(100)) charset gb2312;
mysql> insert into ytt_new10.t2 values ("病毒滾吧！");
Query OK, 1 row affected, 2 warnings (0.02 sec)

mysql> select * from ytt_new10.t2;
+------+
| a1   |
+------+
|      |

事實上，MySQL 知道character_set_client = gb2312，也知道t1表編碼為utf8。寫入過程中，MySQL 會進行轉換，不應當發生亂碼。

發生亂碼的真正原因是: 我們發送給 MySQL 的數據 ，並不是以 gb2312 編碼的。是我們欺騙了 MySQL 。

insert ... "("病毒滾吧！")這個語句, 是以終端字符集 utf8 編碼的. MySQL 把 utf8 數據當作 gb2312 數據, 轉換成 utf8 數據, 自然產生了亂碼.

讓我們以 gb2312 發送數據:

# 終端編碼為UTF-8. 使用iconv轉換文件編碼.
$ echo 'insert into ytt_new10.t1 val("病毒滾吧！");' | iconv -f utf8 -t gb2312 > insert-gb.txt
$ cat insert-gb.txt # 終端無法正常顯示GB2312字符
insert into t1 val("???????ɣ?");
$ mysql -h ... --default-character-set=gb2312 < insert-gbk.txt

SELECT 時, MySQL 會把數據轉換成character_set_results再返回給客户端. 終端可能不支持非 UTF8 字符的顯示, 需要轉換.

$ mysql ... --default-character-set=utf8 -e 'select * from ytt_new10.t1'
病毒滾吧！
$ mysql ... --default-character-set=gb2312 -e 'select...' | iconv -f gb2312 -t utf8
病毒滾吧！

4 字符集 client 到 connection轉換的意義

收到客户端發來的語句後, 服務端會將語句從 character_set_client 轉換為 character_set_connection

這個步驟乍一看多此一舉

• 直接以client字符集執行, MySQL也會正常轉換到表字符集.
• 也可以讓客户端直接以connection字符集發送語句.

那麼，為什麼不將兩個參數合併呢? 搜索發現, 使用轉換層的潛在原因如下:

不同字符集的某些行為是完全不同的

mysql> set character_set_connection = 'utf8';
mysql> select length("hello");
|               5 |

mysql> set character_set_connection = 'utf32';
mysql> select length("hello");
|              20 |

由於character_set_client的限制，客户端不能直接以 UTF32 等字符集發送語句。如果我們就是想要 UTF32 下的行為（函數結果、排序規則等），就需要由 MySQL 進行一層轉換。