MySQL 資料庫編碼轉換

tcsm | 08 五月, 2008 01:05

不論你是使用 vBulletin, PHPBB, Dicuz 或是其他論壇程式，也不論你在論壇程式上面做了什麼樣的設定，只要你沒有改過 MySQL 的系統設定檔，那麼 MySQL 預設使用的編碼(Character Set)就是 latin1。如果你很想確定自己正在使用的資料庫之編碼，你可以使用以下的方式進行確認：(在此假設你的資料庫名稱為 example)

檢查 MySQL 資料庫的編碼

一、使用 phpMyAdmin

登入後請點選 "SQL" 圖示以開啟 SQL 語法輸入視窗。

輸入 "SHOW CREATE DATABASE example;"，然後點選 "執行"。

接下來你應該會看到被截短的資訊：『CREATE DATABASE `example` /*!40100 DEFAULT CHARA...』，其中後半部有關 Character Set 的資訊被裁掉了。

請點選 "← T →" 這個符號以得到完整資訊：CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET latin1 */

重點就在紅色字體的部份，這裡註明了你的資料庫目前正在使用的編碼。

二、使用 mysql client program

登入後，請直接輸入："SHOW CREATE DATABASE example;"

即可得到如下的資訊：CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET latin1 */

重點就在紅色字體的部份，這裡註明了你的資料庫目前正在使用的編碼。

請注意，你的資料庫所使用的編碼不一定會和 MySQL Server 所使用的編碼相同。它們之間的關係是這樣的：若 MySQL Server 預設使用 latin 編碼，而你在建立資料庫時(CREATE DATABASE XXX...)若沒有指定要使用的編碼，那麼該資料庫就會預設使用與 MySQL 相同的編碼，在本例中就是 latin1。反之，雖然 MySQL Server 預設使用 latin 編碼，但若你在建立資料庫時有明確的指定所要使用的編碼(CREATE DATABASE XXX CHARACTER SET UTF8)，則該資料庫所使用的編碼就會是你所指定的編碼。

當你確認自己資料庫的編碼不是你原來打算使用的編碼，例如目前是 latin1 或 big5 等等，但你真正想要(或必須)使用的編碼是 UTF8，此時你就必須進行資料庫編碼轉換。接下來就開始介紹資料庫轉換的詳細步驟：(在此假設你的資料庫名稱為 example)

資料庫編碼轉換的詳細步驟

一、用 mysqldump 將原本的 latin1 資料庫備份成一般的文字檔案

引用:mysqldump -u root -p --default-character-set=latin1 example > example.sql

-u root: 表示使用 root 帳號登入

-p: 表示要輸入密碼，因此當你按下 ENTER 時，系統會要求你提供密碼

--default-character-set=latin1: 這一行很重要，務必要加上去。若你不提供此選項，則 mysqldump 預設會將此選項設定為 --default-character-set=utf8，你資料庫裡面的資料以 latin1 格式儲存，你讀出來的時候卻用 UTF8 去讀，結果就會是亂碼。

二、將 example.sql 這個文字檔案中，有關 character-set 的部份全部修改為 utf8

例如 SET NAMES latin1 改成 SET NAMES utf8；DEFAULT CHARSET=latin1 改成 DEFAULT CHARSET=utf8

註：

grep CHARSET=latin1 example.sql |sort|uniq

可以用來找出有哪些 table 的 character-set 需要修改。

三、修改 MySQL Server 的系統設定檔

修改 /etc/my.cnf：

引用:[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

init-connect='SET NAMES utf8'

[client]

default-character-set=utf8

註1：

MySQL官方建議 [mysqld] 的部份應該要使用 character-set-server 與 collation-server 等選項，而不是使用 default-character-set，因為此選項已經是 deprecated。

註2：

對於擁有 SUPER 權限的使用者(root)，init-connect 選項無效。因此最好不要用具有 SUPER 權限的帳號來存取資料庫。會有這個現象的原因是 MySQL 怕你要是不小心設錯編碼造成無法登入 MySQL Server 時，至少還有 root 帳號可以登入 MySQL Server 去進行故障排除。

四、重新啟動 MySQL，一定要記得重新啟動，不然之前的設定不會生效。

五、檢查 MySQL Server 的編碼是否已為 utf8

執行以下的 SQL 指令：SHOW VARIABLES LIKE 'character\_set\_%'

得到的結果應該會像下面這樣：

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

註：其中 filesystem 會是 binary，此為正常的現象。

六、以 UTF8 編碼重新建立資料庫

執行以下的 SQL 指令："CREATE DATABASE example;"

然後再檢查新資料庫的編碼，應該就會是 utf8。

七、把修改好的 example.sql 還原到新的資料庫中

mysql -u root -p example < example.sql

註：各項參數同 mysqldump，不再重覆

八、使用 phpMyAdmin 檢查 utf8 資料庫是否可以正常顯示中文，若可以就表示成功。

註：

以上範例皆以 Linux 作業系統測試與執行過，沒有問題。Windows 作業系統作法差不多，但我沒實際測過，不敢打包票。