MySQL · 答疑解惑 · set names 都做了什么 · 數據庫內核月報

## 背景最近有同事問，set names 時會同時設置了3個session變量 ~~~ SET character_set_client = charset_name; SET character_set_results = charset_name; SET character_set_connection = charset_name; ~~~ 就從變量名字來看，character_set_client 是設置客戶端相關的字符集，character_set_results 是設置返回結果相關的字符集，character_set_connection 這個就有點不太明白了，這個有啥用呢？ ## 概念說明通過[官方文檔](http://dev.mysql.com/doc/refman/5.6/en/charset-connection.html)來看: 1. character_set_client 是指客戶端發送過來的語句的編碼; 2. character_set_connection 是指mysqld收到客戶端的語句后，要轉換到的編碼； 3. 而 character_set_results 是指server執行語句后，返回給客戶端的數據的編碼。對人來說，能夠理解的是各種各樣的符號，而對計算機來說，只能理解二進制，二進制和符號之間的對應關系就是編碼。不同地域國家都有自己的一套符號集合，每個都各自用一組二進制數字表示，從而形成了不同的編碼，字符集就可以看作是編碼和符號的對應關系集合。同一個二進制數在不同的字符集下可能對應完全不一樣的字符，如在GBK字符集中，`C4E3`?對應的是`你`，而在big5字符集中對應的是`斕`，而?`你`在unicode中的編碼是`4F60`，在[Collation-Charts](http://collation-charts.org/)這個網站有字符集和編碼對應關系圖，可以非常直觀地看到不同編碼下二進制數和符號的對應關系。 set names 設置的3個變量就是設置mysqld和客戶端通信時，mysqld應該如何解讀client發來的字符，以及返回給客戶端什么樣的編碼。 ## 實驗測試環境如下： ~~~ mysql> show variables like 'character%'; +--------------------------+-------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | ~~~ server端的3個編碼設置都是utf8。另外，客戶端是標準 mysql client，使用的編碼是utf8，和sever端編碼是一致的。建一張表作為測試 ~~~ CREATE TABLE t1(id INT, name VARCHAR(200) CHARSET utf8) engine=InnoDB; INSERT INTO t1 VALUES(0, '你好'); mysql> SELECT id, name, hex(name) FROM t1; +------+--------+--------------+ | id | name | hex(name) | +------+--------+--------------+ | 0 | 你好 | E4BDA0E5A5BD | +------+--------+--------------+ ~~~ 下面我們分別改變這3個值，來看下結果會有什么變化 Case 1 只改變 character_set_client ~~~ SET character_set_client=gbk; INSERT INTO t1 VALUES(1, '你好'); mysql> SELECT id, name, hex(name) FROM t1; +------+-----------+--------------------+ | id | name | hex(name) | +------+-----------+--------------------+ | 0 | 你好 | E4BDA0E5A5BD | | 1 | 浣犲ソ | E6B5A3E78AB2E382BD | +------+-----------+--------------------+ 2 rows in set (0.00 sec) ~~~ 可以看到返回的數據已經亂碼了，并且數據庫里存的確實和第一條記錄不一樣。 case 2 只改變 character_set_connection ~~~ SET names utf8; SET character_set_connection = gbk; INSERT INTO t1 VALUES(2, '你好'); mysql> SELECT id, name, hex(name) FROM t1; +------+-----------+--------------------+ | id | name | hex(name) | +------+-----------+--------------------+ | 0 | 你好 | E4BDA0E5A5BD | | 1 | 浣犲ソ | E6B5A3E78AB2E382BD | | 2 | 你好 | E4BDA0E5A5BD | +------+-----------+--------------------+ 3 rows in set (0.00 sec) ~~~ case 3 只改變 character_set_results ~~~ SET names utf8; SET character_set_results = gbk; INSERT INTO t1 VALUES(3, '你好'); mysql> select id, name, hex(name) from t1; +------+--------+--------------------+ | id | name | hex(name) | +------+--------+--------------------+ | 0 | | E4BDA0E5A5BD | | 1 | 你好 | E6B5A3E78AB2E382BD | | 2 | | E4BDA0E5A5BD | | 3 | | E4BDA0E5A5BD | +------+--------+--------------------+ 4 rows in set (0.00 sec) ~~~ 再改回原樣，看下結果 ~~~ SET names utf8; mysql> SELECT id, name, hex(name) FROM t1; +------+-----------+--------------------+ | id | name | hex(name) | +------+-----------+--------------------+ | 0 | 你好 | E4BDA0E5A5BD | | 1 | 浣犲ソ | E6B5A3E78AB2E382BD | | 2 | 你好 | E4BDA0E5A5BD | | 3 | 你好 | E4BDA0E5A5BD | +------+-----------+--------------------+ 4 rows in set (0.00 sec) ~~~ ## 分析我們先理下字符集在整個過程中是怎樣變化的，然后再分析上面的case 客戶發送請求時： ~~~ A1 客戶端發送出語句(總是以utf8)------> A2 sever收到語句解析(按character_set_client指定編碼) | v A4 數據進入mysqld內部存儲<--------- A3 sever判斷是否需要轉換編碼(以character_set_connection 目標編碼) ~~~ server返回結果時： ~~~ B1 server返回結果(按character_set_results 指定編碼) ----->B2客戶端解析編碼顯示(總是以utf8) ~~~ A3步是否需要轉換編碼，代碼中的邏輯是這樣的，在sql_yacc.yy文件中： ~~~ LEX_STRING tmp; THD *thd= YYTHD; const CHARSET_INFO *cs_con= thd->variables.collation_connection; const CHARSET_INFO *cs_cli= thd->variables.character_set_client; uint repertoire= thd->lex->text_string_is_7bit && my_charset_is_ascii_based(cs_cli) ? MY_REPERTOIRE_ASCII : MY_REPERTOIRE_UNICODE30; if (thd->charset_is_collation_connection || (repertoire == MY_REPERTOIRE_ASCII && my_charset_is_ascii_based(cs_con))) tmp= $1; else { if (thd->convert_string(&tmp, cs_con, $1.str, $1.length, cs_cli)) MYSQL_YYABORT; } $$= new (thd->mem_root) Item_string(tmp.str, tmp.length, cs_con, DERIVATION_COERCIBLE, repertoire); if ($$ == NULL) MYSQL_YYABORT; ~~~ 如果?`character_set_client`?和?`character_set_connection`?一樣，或者當前的字符編碼是和ASCII兼容，并且都是ASCII范圍內的，就不轉換，其它情況就轉。對于case1 實際上客戶端發過來是UTF8的，但A2步驟server認為客戶端的編碼是GBK的，就按GBK來解析，同時滿足A3步驟的轉換條件，所以就誤將UTF8編碼認為是GBK，然后又給轉成了UTF8。 `你好`的UTF8編碼是?`E4BDA0E5A5BD`?6個字節，每個字符3個字節，按GBK來解析的話，因為GBK是固定2個字節，就認為有3個字符，然后轉成UTF8，雖然UTF8是變長的，但是這里的3個GBK字符按值都是要占3個字節的，轉出來一共9個字節。所以case1看到的實際存儲的值一共9個字節，比原來的大。在返回時，是按UTF8返回的，因為存了3個UTF8字符，所以客戶端看到的就是3個。對于case2 A2步驟沒問題，問題是出在A3，按照轉換邏輯，此時需要把UTF8轉成GBK，這里因為`character_set_client`是正確的，所以轉換的源不會識別錯，轉換成GBK自然也不會錯，后面存儲成UTF8時，再從GBK轉成UTF8，也沒錯，因為UTF8和GBK字符集里都包含 ‘你’和’好’，所以相互轉換也不會出錯，只是多了2次轉換。對于case3 錯在返回字符集設置的和客戶端不匹配，在返回時，server將所有字符轉成GBK的，結果客戶端一根筋的認為是UTF8，就解析錯了。比較有意思的是第二條記錄，即case1錯誤插進去的，顯示出來是對的。為什么呢，因為在case1中存的時候，是按?`UTF8->強制解析為GBK->然后轉為UTF8`?這個邏輯存下去的，而返回的時候，因為server會將存的UTF8又給轉回GBK，然后客戶端又拿著這個GBK誤以為是UTF8解析，實際上是case1的逆向過程，雖然2個方向都是錯的，最終顯示是好的，所謂的負負得正吧，哈哈。對于case2 ，數據從客戶端進入server的時候，多做了2次轉換，最終顯示還是對的，但不是所有場景都是這樣，如下面這種 ~~~ set names utf8; set character_set_connection = latin1; INSERT INTO t1 VALUES(4, '你好'); set names utf8; mysql> SELECT id, name, hex(name) FROM t1; +------+-----------+--------------------+ | id | name | hex(name) | +------+-----------+--------------------+ | 0 | 你好 | E4BDA0E5A5BD | | 1 | 浣犲ソ | E6B5A3E78AB2E382BD | | 2 | 你好 | E4BDA0E5A5BD | | 3 | 你好 | E4BDA0E5A5BD | | 4 | ?? | 3F3F | +------+-----------+--------------------+ 5 rows in set (0.00 sec) ~~~ 為什么呢，因為在 UTF8轉latin1時，信息丟失了，latin1字符編碼所能表達的字符集是遠小于utf8的，`你`?和?`好`就不在其中，這2個字符在轉換中被轉成了?`?`?和?`?`，之后存儲轉換成UTF8時，`?`只有一個字節`3F`，還原回去還是?`3F`。 ## 總結 `character_set_client`?和?`character_set_results`?是一定要和客戶端一致，不要依賴于負負得正，`character_set_connection`?設置和`character_set_client`?不一致，有丟失數據的風險，所以盡量也一致，總之這3個值就是要一樣，還要和客戶端一致，所以才有了 set names 這個快捷命令。關于為啥要有?`character_set_connection`?這一步轉換，筆者目前還沒看出來，以后理解了再更新，如果讀者朋友知道的話，請不吝賜教。