MySQL Character Set Problem 詳細解釋

http://www.codesoil.net/tag/charset

 

Character Set Problem in PHP + MySQL4.1+

和許多人一樣，我也是在轉移blog時才發現這個問題。雖然是一個很老的問題了，為避免沉痛教訓，這裡就把相關知識做一總結，以方便後人。

【現象】

本來我的blog是放在家裡的伺服器的，最近因為要遷到租用的主機上，就開始了搬家工作。首先是檔案的拷貝，一切順利；接下來是把資料庫從家裡的MySQL中匯出，然後匯入到主機供應商的MySQL上去。由於兩邊雖然MySQL版本不同（家裡是5.x，租用的主機那邊是4.1x），但是由雩都安裝有PMA（PHPMyAdmin），應該沒什麼問題。

這麼想著，等我在家裡的PMA裡執行“匯出”之後，就犯嘀咕了——怎麼開啟產生的sql檔案一看，wp_posts的貼子內容都是亂碼啊？通過查看了一下資料庫、各個表，發現collation一欄裡面寫的都是utf8_general_ci……雖然不大懂collation，但是應該字元編碼都是utf-8，怎麼會是亂碼呢？不管三七二十一，先匯入到租用的主機上再說！——結果，匯入到租用主機上，仍然是亂碼。

【預備知識】

為瞭解決問題，有必要學習和複習一下相關的基礎知識。

首先是MySQL裡面關於character set（字元集）和collation（整理？我認為翻譯成比較規則可能更貼切）的概念。

Character set顧名思義，就是字元、以及字元對應的編碼的集合。例如簡體中文字元集gb2312就包括簡體中文中的所有規定漢字，以及每個漢字對應的代碼。

Collation，是指比較字元的規則的集合。有了比較規則，才能夠將一組資料排序——例如按照英文字母順序排序、漢字按照拼音順序排序等等。顯然，針對同樣一組字元集可以有不同的排序標準、規則。例如漢字可以按照拼音排序，也可以按照筆畫多少排序。尤其是Unicode的字元集，由於其可以包含不同種類的語言，所以可以按照各種語言的排序方法排序。此外，完全按照字元在字元集裡的編碼進行比較的方式稱為binary比較。

到了這裡我們就容易理解了。舉例來說，MySQL支援的gb2312字元集中，有gb2312_bin和gb2312_general_ci兩種collation。很顯然前者是binary比較規則，後者是一般的中文字元比較規則。

每種字元集都有其預設的collation。對於utf8字元集來說，其預設collation是utf8_general_ci。要獲得MySQL裡面支援的字元集和預設collation列表，可以使用SHOW CHARACTER SET語句：

mysql> SHOW CHARACTER SET;+----------+-----------------------------+---------------------+| Charset  | Description                 | Default collation   |+----------+-----------------------------+---------------------+| big5     | Big5 Traditional Chinese    | big5_chinese_ci     || dec8     | DEC West European           | dec8_swedish_ci     || cp850    | DOS West European           | cp850_general_ci    |...

其次，是MySQL中，在哪些地方需要這些字元集和collation。總體上分，在MySQL的體系中有三處字元集和collation：伺服器（資料），串連，用戶端。乍一看體系清楚明了，其實並不是這樣。下面就一一介紹。

[1] 伺服器（資料）端的字元集和collation，可以分成四級逐層指定——server, database, table, column。當MySQL存取位於某一列（column）的資料時，如果column的字元集和collation沒有指定，就會向上追溯table的；如果table也沒有指定字元集和collation，就以database的字元集和collation作為預設值；如果database仍舊沒有指定，那麼就以伺服器的字元集和collation作為預設值。

那麼server的字元集和collation的預設值又是從哪裡來的呢？答案是，設定檔（my.ini）和mysqld（或者mysqld-nt）的命令列參數中都可以指定。如果不幸的，你根本沒有在my.ini或者命令列中指定，那麼MySQL就會使用編譯MySQL時指定的預設字元集——latin1。

但是，需要注意的是，如果安裝MySQL時選擇了多語言支援（一般用中文的都會選擇吧），安裝程式會自動在設定檔中設定default-character-set=utf8

這樣，所有建立的資料庫、表，除非明確指出使用其它字元集，都會預設的使用utf作為資料的字元集（同時使用utf8_general_ci作為預設collation，因為它是utf8的預設collation）。

相關係統變數

character_set_server：伺服器的字元集
collation_server：伺服器的collation
character_set_database：資料庫字元集
collation_database：資料庫的collation

 

[2] 用戶端。對於用戶端傳送來的literal string（例如INSERT，UPDATE語句當中的值），MySQL需要知道它們是什麼編碼。同時，MySQL返回給用戶端的值（例如SELECT語句的傳回值），也可以按照指定的編碼返回。

相關係統變數

character_set_client：用戶端發送過來文字的字元集
character_set_results：發送給用戶端的結果所使用的字元集

 

[3] 串連。用於串連的字元集和collation，是指MySQL在接受到用戶端發送來的文本之後，轉換成何種字元集，用什麼規則進行比較。需要注意的是，如果是將文本和資料庫中某個column的值比較，將優先使用該column的字元集和collation。

相關係統變數

character_set_connection：用於串連的字元集
collation_connection：用於串連的collation

 

【問題的分析】

有了上面的預備知識，我們就開始分析最初的問題：本來是應該作為UTF-8字元儲存的資料，為什麼到了資料庫中就變成了“亂碼”？而且這些亂碼居然還能毫無問題地被wordpress顯示？為什麼一旦匯入到租用的主機那裡就不能正常顯示了呢？

首先讓我們來看一下，我家裡的伺服器上，MySQL的系統變數（System Variables）是如何設定的。

注意：因為一些系統變數是根據用戶端不同而不同的，所以用mysql命令列登陸所看到的和PHP下看到的並不相同。此外，似乎也不能用PMA查看——似乎在PMA中也已經更改了預設的系統變數。因此，要查看PHP作為用戶端時的預設系統變數，可以編寫一個類似下面的PHP小程式：

mysql_connect(localhost, $user, $pass);
$query="SHOW VARIABLES";
$result=mysql_query($query);

其中$result就包含著所有系統變數。在我家裡的伺服器上得到了如下結果（以下只列出跟字元集有關的系統變數）：

character_set_client  latin1
character_set_connection  latin1
character_set_database  utf8
character_set_filesystem  binary
character_set_results  latin1
character_set_server  utf8
character_set_system  utf8
collation_connection  latin1_swedish_ci
collation_database  utf8_general_ci
collation_server  utf8_general_ci

可見，預設的用戶端編碼、預設的串連編碼是latin1——這也就是說，雖然實際上wordpress傳遞給MySQL的文本都是用UTF-8編碼的，但是由於上述系統變數設定不當，這些UTF-8編碼的文本被MySQL當作是latin1編碼的，並且由於資料庫本身是utf8，因此把這些“latin1文本”又轉換成了utf8。這樣，一個漢字居然需要6bytes（一個漢字作為UTF-8是3bytes，被當作latin1進行了轉換，每個latin1字元轉換成2bytes的UTF-8編碼）。這就不難理解為什麼資料庫儲存的是“亂碼”了。

那麼為什麼這些“亂碼”在wordpress顯示時沒問題呢？這是因為，character_set_result也是latin1，也就是說MySQL在取出資料交給wordpress時，把這些資料從utf8轉換回latin1，然後wordpress將這些latin1又當作了utf8——正好是上面的逆過程。

那麼，為什麼到另一台伺服器上面就無法正常顯示了呢？請看看那台租用主機的系統變數設定：

character_set_client  ujis
character_set_connection  ujis
character_set_database  ujis
character_set_results  ujis
character_set_server  ujis
character_set_system  utf8
collation_connection  ujis_japanese_ci
collation_database  ujis_japanese_ci
collation_server  ujis_japanese_ci

可見，其預設的用戶端編碼是ujis。也就是說，MySQL把utf8資料取出後，將會轉換成ujis並傳遞給wordpress。這經曆了latin1 - utf8 - ujis轉換的原本是utf8的字元，早已面目全非了……

【解決方案】

解決方案在很多論壇、網頁上已經有提到了，在wordpress的trac也已經有人提出過。

但是在解決問題之前，我卻很想知道一個問題的答案，那就是：這到底是MySQL的問題，還是PHP（特別是php_mysql extension）的問題，還是wordpress的問題？甚至是使用者配置的問題？我傾向於認為這是一個wordpress的問題。因為無論MySQL還是PHP都不知道wordpress使用了什麼字元編碼，所以無法更改用戶端字元集；而作為一般的wordpress使用者，要求他們設定字元編碼——可以，但是必須要提供一個使用者介面，而不是直接修改來源程式。

那麼解決方案（或者說只是一個workaround）就是，修改wordpress的\wp-uncludes\wp-db.php。在第40多行的function wpdb中，在$this->select($dbname);之前添加一句

$this->query("SET NAMES latin1");

SET NAMES語句的功能就是，執行了SET NAMES 'x'相當於下面三條語句的功能。

SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

這樣，在預設用戶端字元集是ujis的租用主機上，匯入的wordpress文章也能正常顯示了。當然，這不是徹底的解決方案——這隻是“將錯就錯”，反正資料庫裡面儲存的已經是被當作latin1而轉換成utf8的utf8了，那麼就將其轉換回所謂的latin1就是了。這樣做將使其他程式無法讀取wordpress的資料，並且更重要的是，資料庫中儲存的“utf8資料”無法真正按照utf8應有的定序來排序。

那麼最徹底的做法，就是在安裝wordpress時就添加上面所說的SET NAMES語句，並且設定用戶端的字元集為utf8：

$this->query("SET NAMES utf8");

但是這樣做的話，已經被當作latin1寫到資料庫裡面的文章就會無法正常顯示了。要讓他們正常顯示，必須經過utf8 - latin1的轉換。如果數量較多，可以考慮編寫一個程式進行轉換；數量較少的話……手動轉換吧。

BTW，國內高手們漢化的中文版的wordpress中已經添加好這一句了，上面的資訊只適用於那些使用英文wordpress的朋友，以及喜歡追根問底的朋友。

最後推薦一篇參考文章：Portable php-mysql connection charset fix