前段時(shí)間公司內(nèi)部博客上凱哥分享了一篇關(guān)于mysql字符集編碼的文章,之前我對mysql字符集一塊基本沒有深究過,看到凱哥文章后有些地方有點(diǎn)疑惑,遂自己去看了mysql的官方文檔,并參考了凱哥的文章,總結(jié)了這篇博文.本文主要是對mysql常見的字符集問題進(jìn)行整理,如有錯(cuò)誤,請大家指正.

1.MySQL字符集編碼簡介

談到字符集,總會跟編碼扯上關(guān)系,有關(guān)字符集和編碼的理論知識請參見我之前的文章.MySQL內(nèi)部是支持多種字符集的,這里就不再嚴(yán)格區(qū)分字符集和編碼的概念了.同時(shí),MySQL中不同層次有不同的字符集編碼格式,主要有四個(gè)層次:服務(wù)器,數(shù)據(jù)庫,表和列.字符集編碼不僅影響數(shù)據(jù)存儲,還影響客戶端程序和數(shù)據(jù)庫之間的交互.在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中的字符集都對應(yīng)著一個(gè)默認(rèn)的校對規(guī)則(COLLATION),當(dāng)然一個(gè)字符集也可能對應(yīng)多個(gè)校對規(guī)則,但是兩個(gè)不同的字符集不能對應(yīng)同一個(gè)規(guī)則。校對規(guī)則不指定就是使用默認(rèn)的，比如utf8字符集對應(yīng)的默認(rèn)校對規(guī)則就是utf8_general_ci。校對規(guī)則后綴如_cs,_ci,_bin分別表示是大小寫相關(guān)/大小寫無關(guān)/以字符串編碼的二進(jìn)制值來比較大小。如果比較的兩個(gè)字符集不同，則mysql在比較前會先將其轉(zhuǎn)換到同一個(gè)字符集再比較，如果兩個(gè)字符集不兼容，則會報(bào)錯(cuò)Illegal mix of collations。

需要注意的是，校對規(guī)則可能會影響查詢。比如數(shù)據(jù)表的一個(gè)字段本身設(shè)置的校對規(guī)則為utf8_general_ci，且在title字段有索引，而你查詢的時(shí)候使用了SELECT xx FROM test ORDER BY title COLLATE utf8_bin來用另外校對規(guī)則進(jìn)行排序，則此時(shí)就用不了索引，轉(zhuǎn)而使用filesort。在實(shí)際項(xiàng)目中，一般不去顯示指定的校對規(guī)則。

下面來看看上面命令列出的字符集相關(guān)變量的含義

• character_set_client:服務(wù)器解析客戶端sql語句的字符集.(The character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when the client connects to the server).
• character_set_connection:字符串字面值（literal strings）的字符集.
• character_set_results:服務(wù)器返回給客戶端的查詢結(jié)果或者錯(cuò)誤提示的字符集編碼.(The character set used for returning query results such as result sets or error messages to the client)
• character_set_system:這是mysql服務(wù)器用來存儲元數(shù)據(jù)的編碼,通常就是utf8,不要去修改它.
• character_sets_dir:這是mysql字符集編碼存儲目錄.
• character_set_filesystem:這是文件系統(tǒng)字符集編碼,主要用于解析用于文件名的字符串字面值,如LOAD DATA INFILE和SELECT ...INTO OUTFILE等語句以及LOAD_FILE()函數(shù).在打開文件之前,文件名會從character_set_client轉(zhuǎn)換為character_set_filesystem指定的編碼.默認(rèn)值為binary,也就是說不會進(jìn)行轉(zhuǎn)換.例如我們設(shè)置的character_set_client=GBK,而character_set_filesystem為默認(rèn)值的話,則采用SELECT...INTO OUTFILE "文件名",文件名為GBK編碼.反之,如果我們設(shè)置了character_set_filesystem=UTF8,則導(dǎo)出的文件名為UTF8編碼.
  例如:我的終端編碼是UTF8,系統(tǒng)默認(rèn)語言和編碼為zh_CN.UTF8.我有一個(gè)數(shù)據(jù)庫名為test,test中有個(gè)表名為t1,編碼為latin1,另外,我在mysql客戶端執(zhí)行了SET NAMES GBK,如果我不修改character_set_filesystem的值,執(zhí)行SELECT * FROM t1 INTO OUTFILE '文件1', 可以發(fā)現(xiàn)對應(yīng)的目錄下面生成了一個(gè)名為"文件1"的文件,那文件名編碼是什么呢?其實(shí)這里有幾個(gè)地方需要注意,首先,我們的sql語句里面的"文件1"原生編碼就是終端編碼UTF8,也就是'\xe6\x96\x87\xe4\xbb\xb61',而導(dǎo)出數(shù)據(jù)的語句SELECT * FROM t1 INTO OUTFILE '文件1',按照前面的說法,因?yàn)閏haracter_set_filesystem為binary,因此'\xe6\x96\x87\xe4\xbb\xb61'不會轉(zhuǎn)換編碼,這樣最終還是'\xe6\x96\x87\xe4\xbb\xb61',這樣在zh_CN.UTF8的系統(tǒng)中文件名不會亂碼.而如果我們設(shè)置了character_set_filesystem=UTF8,則原生的'\xe6\x96\x87\xe4\xbb\xb61'會先按照GBK解碼,然后用UTF8編碼,最后的結(jié)果是"\xe9\x8f\x82\xe5\x9b\xa6\xe6\xac\xa21",這樣文件名就會亂碼了.所以這個(gè)變量也最好不要修改,用默認(rèn)值就OK.
• character_set_server:服務(wù)器默認(rèn)字符集編碼,如果創(chuàng)建數(shù)據(jù)庫的時(shí)候沒有指定編碼,則采用character_set_server指定編碼.
• character_set_database:默認(rèn)數(shù)據(jù)庫的字符集編碼.如果沒有默認(rèn)數(shù)據(jù)庫,則該變量值與character_set_server相同.其實(shí)這個(gè)值代表的就是你當(dāng)前數(shù)據(jù)庫的編碼而已,比如使用"use test",而test數(shù)據(jù)庫的編碼為latin1的話,這個(gè)值就是latin1.而你切換的時(shí)候"use test2",則character_set_database的值就是數(shù)據(jù)庫test2的編碼.LOAD DATA INFILE的時(shí)候，數(shù)據(jù)庫總是將文件中的字符按照character_set_database解析，在5.0之后的版本中，可以在LOAD的時(shí)候用character set指定字符集。

2.MySQL字符集編碼層次

第一部分主要是歸納了MySQL文檔中關(guān)于字符集編碼的說明.這部分主要說明下MySQL字符集編碼層次:服務(wù)器-數(shù)據(jù)庫-表-字段.

簡單來說,服務(wù)器編碼就是character_set_server來指定的.當(dāng)我們創(chuàng)建數(shù)據(jù)庫的時(shí)候可以指定編碼,如果沒有指定,采用的就是character_set_server指定的編碼.例如:我們使用"create database t1 character set gbk",這里我們指定了數(shù)據(jù)庫t1的編碼為gbk,所以不會采用character_set_server指定的編碼.而如果我們使用"create database t2",則通過"show create database t2"可以看到t2的編碼為character_set_server定的編碼.

同理,mysql表也可以有自己獨(dú)立的編碼,在創(chuàng)建表的時(shí)候可以指定,如果沒有指定,則默認(rèn)采用數(shù)據(jù)庫的編碼.比如我們再之前的數(shù)據(jù)庫t1創(chuàng)建表t11,create table t11(i int) character set utf8,則表t11的編碼為utf8,如果不指定編碼則編碼為數(shù)據(jù)庫t1的編碼gbk.

此外,mysql表中的字段也可以有自己的編碼,如果不指定字段編碼,則字段編碼與表的編碼一致.

3.MySQL連接字符集

前面談到的編碼內(nèi)容基本都不會產(chǎn)生亂碼問題,mysql中容易產(chǎn)生亂碼的地方在character_set_client, character_set_connection, character_set_results這三個(gè)變量的設(shè)定.可以簡單的通過set names utf8或者charset utf8命令來一次設(shè)置這三個(gè)參數(shù).

剛剛接觸這幾個(gè)變量的時(shí)候我完全沒有看懂,后來查找了不少資料,姑且算是理解了一點(diǎn),如有錯(cuò)誤，請大家指正。

從文檔中的解釋來看,mysql連接字符集轉(zhuǎn)換主要包括下面三個(gè)步驟:

• 1.character_set_client是客戶端發(fā)送過來的sql語句的編碼,因?yàn)榉?wù)端本身并不知道客戶端的sql語句的編碼是什么,所以是以這個(gè)變量作為客戶端sql語句的初始編碼.而服務(wù)端接收到sql語句后,則會將sql語句從character_set_client轉(zhuǎn)換為character_set_connection指定的編碼(注意,對于字面值字符串,如果前面有introducer標(biāo)記如_latin1或_utf8,則不會進(jìn)行這一步轉(zhuǎn)換).轉(zhuǎn)換完成,才會真正執(zhí)行sql語句.

• 2.進(jìn)行內(nèi)部操作前將sql語句中的數(shù)據(jù)從character_set_connection轉(zhuǎn)換為數(shù)據(jù)表中相應(yīng)字段的編碼.

• 3.將操作結(jié)果從內(nèi)部字符集編碼轉(zhuǎn)換為character_set_results編碼.

更加詳細(xì)的轉(zhuǎn)換過程如下:

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

接下來就實(shí)例分析下mysql可能亂碼的情況以及我認(rèn)為的原因,不對之處請指出.

4.MySQL亂碼實(shí)例分析

4.1 問題實(shí)例

這個(gè)測試?yán)痈鷦P哥的一樣，我們創(chuàng)建一個(gè)測試的數(shù)據(jù)庫db1,數(shù)據(jù)庫編碼為latin1,注意當(dāng)前我的機(jī)器的終端編碼為zh_CN.UTF-8,數(shù)據(jù)庫的編碼設(shè)定如第1部分所示，然后在db1中創(chuàng)建一個(gè)表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,而表中三個(gè)字段的編碼各不相同,分別為gbk編碼,utf8編碼以及l(fā)atin1編碼.之所以這樣創(chuàng)建正是為了驗(yàn)證mysql字符集編碼的轉(zhuǎn)換過程.好了,重點(diǎn)來了,現(xiàn)在我們在mysql客戶端執(zhí)行:

mysql> insert into test values("中文", "中文", "中文");
Query OK, 1 row affected, 1 warning (0.00 sec)

安裝了mysql的筒子可以測試下,在mysql沒有開啟strict模式的時(shí)候,這個(gè)插入語句會報(bào)一個(gè)警告,內(nèi)容如下:

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表中的內(nèi)容:

mysql> select * from test;
+--------+--------+------------+
| gbk    | utf8   | latin_utf8 |
+--------+--------+------------+
| 中文   | 中文   | ??         |
+--------+--------+------------+

我們還可以查看下test表中實(shí)際存儲的內(nèi)容:

mysql> select hex(gbk), hex(utf8), hex(latin_utf8) from test;
+----------+--------------+-----------------+
| hex(gbk) | hex(utf8)    | hex(latin_utf8) |
+----------+--------------+-----------------+
| D6D0CEC4 | E4B8ADE69687 | 3F3F            |
+----------+--------------+-----------------+

可以發(fā)現(xiàn)直接select查看的時(shí)候latin_utf8字段亂碼了,而通過hex函數(shù)查看發(fā)現(xiàn)原來latin_utf8字段存儲的內(nèi)容有問題. 出現(xiàn)這個(gè)問題的原因就是編碼轉(zhuǎn)換過程出了錯(cuò),按照之前的原理來分析下整個(gè)編碼轉(zhuǎn)換過程:

• 首先我們mysql客戶端發(fā)送插入語句insert into test values("中文", "中文", "中文");,注意到"中文"的編碼是跟我們的環(huán)境相關(guān)的,我這里是zh_CN.UTF-8,因此"中文"字節(jié)表示為\\xE4\\xB8\\xAD\\xE6\\x96\\x87.

• 服務(wù)器端接收到該語句會當(dāng)作utf8編碼,因?yàn)閏haracter_set_client=utf8,接下來是會進(jìn)行第一步轉(zhuǎn)換,即將語句從character_set_client轉(zhuǎn)成character_set_connection的編碼,由于我們這里這2個(gè)編碼相同,實(shí)際就不會轉(zhuǎn)換(此外,如果插入的數(shù)據(jù)前面有_latin1或者_(dá)utf8等introducer標(biāo)記,也不會轉(zhuǎn)換,因?yàn)閕ntroducer標(biāo)記已經(jīng)指明了字面值字符的編碼).

• 接下來,數(shù)據(jù)要存儲到數(shù)據(jù)庫了,這個(gè)時(shí)候?qū)嶋H要插入的三個(gè)字段的編碼都是原始編碼\\xE4\\xB8\\xAD\\xE6\\x96\\x87,這個(gè)時(shí)候發(fā)生第二次編碼轉(zhuǎn)換,即由character_set_connection編碼轉(zhuǎn)換為數(shù)據(jù)表字段指定的編碼.那么接下來,我們可以看到,由本身的UTF8編碼與字段utf8相同,不需要進(jìn)行轉(zhuǎn)換.接下來看gbk字段,它的編碼是gbk,這時(shí)會將原始編碼s="\\xE4\\xB8\\xAD\\xE6\\x96\\x87"按照utf8編碼轉(zhuǎn)換為GBK編碼,即執(zhí)行s.decode('utf8').encode('gbk'),所以存儲的是D6D0CEC4,也沒有問題. 最后,看latin_utf8字段,同樣需要轉(zhuǎn)換編碼,由于latin1表示不了utf8編碼的范圍,所以s.decode('utf8').encode('latin1')這個(gè)轉(zhuǎn)換過程會出錯(cuò),導(dǎo)致的結(jié)果就是latin_utf8字段存儲的是??,即3F3F.

• 最后就是select語句返回的結(jié)果分析,這是第三個(gè)需要轉(zhuǎn)換編碼的地方,即將字段從字段編碼轉(zhuǎn)換為character_set_results指定的編碼.這也是我們上面為什么gbk字段和utf8字段都能正常顯示中文的原因,因?yàn)樵诜祷亟Y(jié)果的時(shí)候,gbk字段會經(jīng)過'\xD6\xD0\xCE\xC4'.decode('gbk').encode('utf8')返回,這樣我們在utf8編碼的mysql客戶端能夠正常顯示gbk字段.同理,由于utf8字段本身與character_set_results,所以不會發(fā)生編碼轉(zhuǎn)換,原樣返回\\xE4\\xB8\\xAD\\xE6\\x96\\x87,因此也是能正常顯示的.而latin_utf8字段本身存儲的就是3F3F,再經(jīng)過編碼轉(zhuǎn)換,雖然utf8編碼能夠兼容latin1,但是本身的編碼是3F3F,所以最終結(jié)果就是"??".

4.2 解決方案

這一小節(jié)就來說說4.1中的問題,根據(jù)上面的分析,為了表test中的latin_utf8字段能夠正常的插入內(nèi)容,我們不重新設(shè)置character_set_client和character_set_connection的情況下,那么有個(gè)好的方法就是加入introducer,關(guān)于introducer可以參見mysql官方文檔.那么我們的插入語句改為

mysql> insert into test values("中文", "中文", _latin1"中文");
Query OK, 1 row affected (0.02 sec)

由于指定了latin_utf8字段的introducer為_latin1,這樣在第一次由character_set_client轉(zhuǎn)換為character_set_connection的時(shí)候會忽略latin_utf8的轉(zhuǎn)換,所以還是保持原來的utf8字符,接下來將其存入到latin1字段中,亦不會有問題,因?yàn)榫幋a相同,不需要轉(zhuǎn)換,所以latin_utf8字段實(shí)際存儲的還是\\xE4\\xB8\\xAD\\xE6\\x96\\x87.這點(diǎn)可以通過下面的命令來驗(yàn)證:

mysql> select hex(gbk), hex(utf8), hex(latin_utf8) from test;
+----------+--------------+-----------------+
| hex(gbk) | hex(utf8)    | hex(latin_utf8) |
+----------+--------------+-----------------+
| D6D0CEC4 | E4B8ADE69687 | 3F3F            |
| D6D0CEC4 | E4B8ADE69687 | E4B8ADE69687    |
+----------+--------------+-----------------+

那么我們?nèi)绻苯觭elect查詢,還會出錯(cuò)么呢?答案是會的,因?yàn)槿缜八f,查詢的時(shí)候會將字段編碼轉(zhuǎn)換為character_set_results編碼的,顯然gbk和utf8字段都沒有問題,但是對于latin_utf8字段,其值會通過s.decode('latin1').encode('utf8'),從而導(dǎo)致在查詢的時(shí)候會亂碼。

mysql> select * from test;
+--------+--------+----------------+
| gbk    | utf8   | latin_utf8     |
+--------+--------+----------------+
| 中文   | 中文   | ??             |
| 中文   | 中文   | ??-?–?         |
+--------+--------+----------------+
2 rows in set (0.01 sec)

那么解決的方法也比較簡單,就是中select語句中的字段前面加上binary標(biāo)識,表示該字段查詢結(jié)果不需要經(jīng)過character_set_results的轉(zhuǎn)換.如下:

mysql> select gbk, utf8, binary latin_utf8 from test;
+--------+--------+-------------------+
| gbk    | utf8   | binary latin_utf8 |
+--------+--------+-------------------+
| 中文   | 中文   | ??                |
| 中文   | 中文   | 中文              |
+--------+--------+-------------------+
2 rows in set (0.00 sec)

5.總結(jié)

mysql編碼系統(tǒng)復(fù)雜,依照原理和測試的結(jié)果來看,character_set_client一定要與傳入的數(shù)據(jù)編碼一致,不然就會容易出現(xiàn)亂碼問題,character_set_connection可以與character_set_client不同,但是個(gè)人建議一樣最好,免得出現(xiàn)其他問題.此外,如果對結(jié)果編碼有要求,就設(shè)置下character_set_results編碼,當(dāng)然我個(gè)人覺得這三個(gè)編碼一致是最省事的.此外,數(shù)據(jù)表字段編碼如果用latin1編碼,對于like搜索會有一些問題。最好是utf8編碼省時(shí)省力，如果用gbk一定要注意寬字節(jié)注入問題。

UPDATED：
之前有個(gè)疑問是為什么有了character_set_client了還要加上character_set_connection，多出來的這次轉(zhuǎn)換的意義在哪里?？垂俜轿臋n描述：

• What character set should the server translate a statement to after receiving it?

  For this, the server uses the character_set_connection
  and collation_connection system variables. It converts statements sent by the client from character_set_client
  to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection
  is important for comparisons of literal strings. For comparisons of strings with column values,collation_connection
  does not matter because columns have their own collation, which has a higher collation precedence.

  Every character string literal has a character set and a collation.
  For the simple statement SELECT 'string', the string has the connection default character set and collation defined by the character_set_connection and collation_connection system variables.

也就是說，character_set_connection的應(yīng)用情況基本就是不帶introducer的字符串字面值，或者更確切的說這是mysql代碼內(nèi)部所用編碼（不是數(shù)據(jù)存儲編碼，數(shù)據(jù)存儲編碼由表的定義指定）。在前面的試驗(yàn)中，如果將character_set_connection設(shè)置為GBK，則執(zhí)行 SELECT length('中文')返回為4，而如果設(shè)置character_set_connection為UTF8，則執(zhí)行結(jié)果為6。這里的中文編碼就是依據(jù)的character_set_connection。

此外，比如字符串比較SELECT '中' > '哈’（中的GBK編碼為D6D0，UTF8編碼為E4B8AD，哈的GBK編碼為B9FE，UTF8編碼為E59388），在兩者都是字面值的字符串的情況下，比較的時(shí)候字符集是以character_set_connection為準(zhǔn)的，當(dāng)該值為GBK時(shí)，我們發(fā)現(xiàn)結(jié)果為1;而如果設(shè)置character_set_connection為UTF8時(shí)，則結(jié)果為0。而如果查詢語句是跟列的值相比的， SELECT * from test WHERE gbk>'哈'，則此時(shí)會將字面值字符串'哈'轉(zhuǎn)換為該列對應(yīng)的編碼GBK進(jìn)行比較。

我總結(jié)了這些地方,時(shí)間也很倉促,可能也有理解不到位的地方,還請大家指出。

6.參考資料

• mysql character set
• 深入mysql字符集設(shè)置
• PHP-MySQL-Transmitting-Non-ASCII-Characters
• Viewing-Character-Set-Variables