之前內部博客上凱哥分享了一篇關於mysql字符集的文章,之前我對mysql字符集一塊基本沒有深究過,看到凱哥文章後有些地方有點疑惑,遂自己去看了mysql的官方文檔,並參考了凱哥的文章,總結了這篇博文.本文主要是對mysql常見的字符集問題進行整理,如有錯誤,請大家指正.
談到字符集,總會跟編碼扯上關系,有關字符集和編碼的理論知識請參見我之前的文章.MySQL內部是支持多種字符集的,這裡就不再嚴格區分字符集和編碼的概念了.同時,MySQL中不同層次有不同的字符集編碼格式,主要有四個層次:服務器,數據庫,表和列.字符集編碼不僅影響數據存儲,還影響客戶端程序和數據庫之間的交互.在mysql中輸入命令show session variables like '%character%'可以看到如下一些字符集:
+--------------------------+--------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.6.15-osx10.7-x86_64/share/charsets/
mysql中的字符集都對應著一個默認的校對規則(COLLATION),當然一個字符集也可能對應多個校對規則,但是兩個不同的字符集不能對應同一個規則.由於我平時都用的默認校對規則,所以就忽略不談了,後續有新的發現我會補上.
下面來看看上面命令列出的字符集相關變量的含義
第一部分主要是歸納了MySQL文檔中關於字符集編碼的說明.這部分主要說明下MySQL字符集編碼層次:服務器-數據庫-表-字段.
簡單來說,服務器編碼就是character_set_server來指定的.當我們創建數據庫的時候可以指定編碼,如果沒有指定,采用的就是character_set_server指定的編碼.例如:我們使用"create database t1 character set gbk",這裡我們指定了數據庫t1的編碼為gbk,所以不會采用character_set_server指定的編碼.而如果我們使用"create database t2",則通過"show create database t2"可以看到t2的編碼為character_set_server定的編碼.
同理,mysql表也可以有自己獨立的編碼,在創建表的時候可以指定,如果沒有指定,則默認采用數據庫的編碼.比如我們再之前的數據庫t1創建表t11,"create table t11(i int) character set utf8",則表t11的編碼為utf8,如果不指定編碼則編碼為數據庫t1的編碼gbk.
此外,mysql表中的字段也可以有自己的編碼,如果不指定字段編碼,則字段編碼與表的編碼一致.
前面談到的編碼內容基本都不會產生亂碼問題,mysql中容易產生亂碼的地方在character_set_client, character_set_connection, character_set_results這三個變量的設定.可以簡單的通過set names utf8或者charset utf8命令來一次設置這三個參數.
剛剛接觸這幾個變量的時候我完全沒有看懂,後來查找了不少資料,姑且算是理解了一點,當然也可能是錯的,因為沒有看過mysql源碼,具體的原理還是請大神們指教.
從文檔中的解釋來看,mysql連接字符集轉換主要包括下面三個步驟:
更加詳細的轉換過程如下:
Client program sends SQL statement
|
| Encoding: A, defined as "character_set_client"
v
MySQL server - Convertion from encoding A to encoding B
|
| Encoding: B, defined as "character_set_connection"
v
MySQL server - Execution to store data
MySQL server - Conversion from encoding B to encoding C
|
| Encoding: C, defined by text column encoding
v
MySQL server - Storage
...
MySQL server - Storage
|
| Encoding: C, defined by text column encoding
v
MySQL server - Execution to fetch data
MySQL server - Convertion from encoding C to encoding D
|
| Encoding: D, defined as "character_set_results"
v
Client program receives result set
接下來就實例分析下mysql可能亂碼的情況以及我認為的原因,不對之處請指出.
我們創建一個測試的數據庫db1,數據庫編碼為latin1,注意當前我的機器的終端編碼為zh_CN.UTF-8,數據庫的編碼設定如下所第1部分所示,然後中db1中創建一個表test,sql語句如下:
CREATE TABLE `test` (
`gbk` varchar(2) CHARACTER SET gbk DEFAULT NULL,
`utf8` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
`latin_utf8` varchar(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
注意到我們的表的編碼是latin1,而表中三個字段的編碼各不相同,分別為gbk編碼,utf8編碼以及latin1編碼.之所以這樣創建正是為了驗證mysql字符集編碼的轉換過程.好了,重點來了,現在我們在mysql客戶端執行:
mysql> insert into test values("中文", "中文", "中文");
Query OK, 1 row affected, 1 warning (0.00 sec)
安裝了mysql的筒子可以測試下,在mysql沒有開啟strict模式的時候,這個插入語句會報一個警告,內容如下:
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'latin_utf8' at row 1 |
+---------+------+-------------------------------------------------------------------------------------+
我們可以先select看看test表中的內容:
mysql> select * from test;
+--------+--------+------------+
| gbk | utf8 | latin_utf8 |
+--------+--------+------------+
| 中文 | 中文 | ?? |
+--------+--------+------------+
我們還可以查看下test表中實際存儲的內容:
mysql> select hex(gbk), hex(utf8), hex(latin_utf8) from test;
+----------+--------------+-----------------+
| hex(gbk) | hex(utf8) | hex(latin_utf8) |
+----------+--------------+-----------------+
| D6D0CEC4 | E4B8ADE69687 | 3F3F |
+----------+--------------+-----------------+
可以發現直接select查看的時候latin_utf8字段亂碼了,而通過hex函數查看發現原來latin_utf8字段存儲的內容有問題. 出現這個問題的原因就是編碼轉換過程出了錯,按照之前的原理來分析下整個編碼轉換過程:
這一小節就來說說4.1中的問題,根據上面的分析,為了表test中的latin_utf8字段能夠正常的插入內容,我們不重新設置character_set_client和character_set_connection的情況下,那麼有個好的方法就是加入introducer,關於introducer可以參見mysql官方文檔.那麼我們的插入語句改為
mysql> insert into test values("中文", "中文", _latin1"中文");
Query OK, 1 row affected (0.02 sec)
由於指定了latin_utf8字段的introducer為_latin1,這樣在第一次由character_set_client轉換為character_set_connection的時候會忽略latin_utf8的轉換,所以還是保持原來的utf8字符,接下來將其存入到latin1字段中,亦不會有問題,因為編碼相同,不需要轉換,所以latin_utf8字段實際存儲的還是\xE4\xB8\xAD\xE6\x96\x87.這點可以通過下面的命令來驗證:
mysql> select hex(gbk), hex(utf8), hex(latin_utf8) from test;
+----------+--------------+-----------------+
| hex(gbk) | hex(utf8) | hex(latin_utf8) |
+----------+--------------+-----------------+
| D6D0CEC4 | E4B8ADE69687 | 3F3F |
| D6D0CEC4 | E4B8ADE69687 | E4B8ADE69687 |
+----------+--------------+-----------------+
那麼我們如果直接select查詢,還會出錯麼呢?答案是會的,因為如前所說,查詢的時候會將字段編碼轉換為character_set_results編碼的,顯然gbk和utf8字段都沒有問題,但是對於latin_utf8字段,其值會通過s.decode('latin1').encode('gbk'),從而導致在查詢的時候會亂碼.
mysql> select * from test;
+--------+--------+----------------+
| gbk | utf8 | latin_utf8 |
+--------+--------+----------------+
| 中文 | 中文 | ?? |
| 中文 | 中文 | ??-?–? |
+--------+--------+----------------+
2 rows in set (0.01 sec)
那麼解決的方法也比較簡單,就是中select語句中的字段前面加上binary標識,表示該字段查詢結果不需要經過character_set_results的轉換.如下:
mysql> select gbk, utf8, binary latin_utf8 from test;
+--------+--------+-------------------+
| gbk | utf8 | binary latin_utf8 |
+--------+--------+-------------------+
| 中文 | 中文 | ?? |
| 中文 | 中文 | 中文 |
+--------+--------+-------------------+
2 rows in set (0.00 sec)
mysql編碼系統復雜,依照原理和測試的結果來看,character_set_client一定要與傳入的數據編碼一致,不然就會容易出現亂碼問題,character_set_connection可以與character_set_client不同,但是個人建議一樣最好,免得出現其他問題.此外,如果對結果編碼有要求,就設置下character_set_results編碼,當然我個人覺得這三個編碼一致是最省事的.此外,數據表字段編碼如果用latin1編碼,對於like搜索會有一些問題,最好大家依照自己需求來設定合理的字段編碼了.
我總結了這些地方,時間也很倉促,可能也有理解不到位的地方,還請大家指出.當然,最後要致謝凱哥,是凱哥最初的博客讓我去研究了下mysql的編碼,後續有新的認識我會再繼續更新該文章.
