2008年6月11日 星期三

MySQL Big5轉utf8及亂碼問題解決

加入書籤: HemiDemi MyShare Baidu Google Bookmarks Yahoo! My Web Del.icio.us Digg technorati furl

1、MySQL 新版的變化:
MySQL 4.1 以後對多語言的支持有了很大變化 (這也導致了許多問題的出現);儘管大部分的地方,MySQL 3 仍然占主導地位;但 MySQL 4.1 是 MySQL 官方推薦的數據庫,(截自目前MySQL己出到了5.2.2),已經有主機提供商開始提供並將會越來越多; 許多 PHP 程序以 MySQL 作為默認的數據庫管理軟件,但它們一般不區分 MySQL 4.1 以上與 4.1 以下版本的區別,籠統地稱「MySQL 3.xx.xx 以上版本」就滿足安裝需求了; 因為 latin1 在許多地方 (下邊會詳細描述具體是哪些地方) 作為默認的字符集,成功的蒙蔽了許多 PHP 程序的開發者和用戶,掩蓋了在中文等語言環境下會出現的問題;簡單的說,MySQL 自身的變化和使用 MySQL 的 PHP 程序對此忽略,導致了問題的出現和複雜化,而由於大部分用戶使用的是英文,使得這種問題不被重視。但在中文環境使用下因而產生了許多的問題。
2、MySQL 4.1 字符集支持的原理:
MySQL 4.1 對於字符集的指定可以細化到一台機器上安裝的 MySQL,其中的一個數據庫,其中的一張表,其中的一欄,應該用什麼字符集。但是,傳統的 Web 程序在創建數據庫和數據表時並沒有使用那麼複雜的配置,它們用的是默認的配置,那麼,默認的配置從何而來呢?
編譯 MySQL 時,指定了一個默認的字符集,這個字符集是 latin1;
安裝 MySQL 時,可以在配置文件 (my.ini) 中指定一個默認的的字符集,如果沒指定,這個值繼承自編譯時指定的;
啟動 mysqld 時,可以在命令行參數中指定一個默認的的字符集,如果沒指定,這個值繼承自配置文件中的;
此時 character_set_server 被設定為這個默認的字符集;
當創建一個新的數據庫時,除非明確指定,這個數據庫的字符集被缺省設定為 character_set_server;
當選定了一個數據庫時,character_set_database 被設定為這個數據庫默認的字符集;
在這個數據庫裡創建一張表時,表默認的字符集被設定為character_set_database,也就是這個數據庫默認的字符集;
當在表內設置一欄時,除非明確指定,否則此欄缺省的字符集就是表默認的字符集;
這個字符集就是數據庫中實際存儲數據採用的字符集,mysqldump 出來的內容就是這個字符集下的;
簡單的總結一下,如果什麼地方都不修改,那麼所有的數據庫的所有表的所有欄位的都用 latin1 存儲,不過我們如果安裝 MySQL,一般都會選擇多語言支持,也就是說,安裝程序會自動在配置文件中把 default_character_set 設置為 UTF-8,這保證了缺省情況下,所有的數據庫的所有表的所有欄位的都用 UTF-8 存儲。
當一個 PHP 程序與 MySQL 建立連接後,這個程序發送給 MySQL 的數據採用的是什麼字符集?MySQL 無從得知 (它最多只能猜測),所以 MySQL 4.1 要求客戶端必須指定這個字符集,也就是 character_set_client,MySQL 的怪異之處在於,得到的這個字符集並不立即轉換為存儲在數據庫中的那個字符集,而是先轉換為 character_set_connection 變量指定的一個字符集;這個 connection 層究竟有什麼用我不大明白,但轉換為 character_set_connection 的這個字符集之後,還要轉換為數據庫默認的字符集,也就是說要經過兩次轉換;當這個數據被輸出時,又要由數據庫默認的字符集轉換為 character_set_results 指定的字符集。
以一個典型的環境為例,電腦上安裝著 Apache 2,PHP 5 和 WordPress 1.5.1.3,MySQL 配置文件中指定了 default_character_set 為 utf8。於是問題出現了:
WordPress 按照默認情況安裝,所以所有的表都用 UTF-8 存儲數據;
WordPress 默認採用的瀏覽字符集是 UTF-8 (Options->Reading 中設置),因此所有 WP 頁面的 meta 中會說明 charset 是 utf-8;
所以瀏覽器會以 utf-8 方式顯示所有的 WP 頁面;這樣一來 Write 的所有 Post,和 Comment 都會以 UTF-8 格式從瀏覽器發送給 Apache,再由 Apache 交給 PHP;
所以 WP 從所有的表單中得到的數據都是 utf-8 編碼的;WP 不加轉換的直接把這些數據發送給 MySQL;
MySQL 默認設置的 character_set_client 和 character_set_connection 都是 latin1,此時怪異的事情發生了,實際上是 utf-8 格式的數據,被「當作 latin1」轉換成……居然還是轉換成 latin1,然後再由這個 latin1 轉換成 utf-8,這麼兩次轉換,有一部分 utf-8 的字符就丟失了,變成 ??,最後輸出的時候 character_set_results 默認是 latin1,也就輸出為奇怪的東西了。
3、PHP 程序與 MySQL 建立連接:
所以為了解決這些問題,在PHP 程序與 MySQL 建立連接時,於query 之前先執行:
SET character_set_client='utf8'
SET character_set_connection='utf8'
SET character_set_results='utf8'
如上三列編碼都一樣時,可寫成 SET NAMES 'utf8'
上面3個變量的作用是這樣的,client表示客戶端發送過來的字符集,results表示發送到客戶端的字符集(這兩個分開是因為發送過來和發送過去的不一定是同一個客戶端),connection則在客戶端和數據庫起一個連接作用。
如果是租用網路伺服器,首先測試服務器MySQL資料庫是否 >= 4.1,編譯時是否加入了 UTF-8 支持;然後測試數據庫以什麼格式存儲 ($dbEncoding);
SET NAMES $dbEncoding 。
4、MySQL資料庫big5轉utf8:
步驟一:首先把資料dump出來
由於先前用4.0時我的資料是存成latin1,所以dump出來後要用iconf或piconf把他轉成UTF-8,記得dump出來時要加上-default-character-set=latin1
#mysqldump -u帳號 -p -default-character-set=latin1 資料庫 >output.sql
步驟二:big5轉成utf8
如果原先是Big5的資料, dump出來後就直接把Big5轉成UTF-8即可
#piconv -f big5 -t utf8 output.sql >utf8.sql
如果原先是UTF-8的資料,像是wordpress,dump出來後要先轉成Big5後,再把他轉成UTF-8,因為原先dump出來的是偽裝成UTF-8的lantin1不是真正的UTF-8
#mysqldump -u帳號 -p -default-character-set=latin1 資料庫 >output.sql
#piconv -f utf8 -t big5 output.sql > big5.sql
#piconv -f big5 -t utf8 big5.sql >utf8.sql
步驟三:修改sql檔
1、修改轉成的sql檔中所產生的‘’\"及換行
的問題(有時換行時會成‘\n’)
2、在dump出來的sql檔最前面加上:
SET NAMES utf8;
SET CHARACTER_SET_CLIENT=utf8;
SET CHARACTER_SET_RESULTS=utf8;
再來把每個資料表後面的TYPE=MyISAM;
改成ENGINE=MyISAM DEFAULT CHARSET=utf8;
步驟四:建立新的資料庫
方法1. SQL語法
CREATE DATABASE `abc` DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci;
方法2. 用phpMyAdmin建立資料庫的,建立時”校對”要選utf8_general_ci
步驟五:import資料
方法1. SQL語法
#mysql -u帳號 -p 資料庫 < utf8.sql
方法2. 直接用phpMyAdmin把他import進去了,import時記得文字編碼檔案要選utf8即可。不過使用phpMyAdmin import 有檔案大小的限制,可先把sql檔分成幾個檔案。
方法3.用Webmin  import (我即是用此方法,如滙入不成功還可知在那一行的sql出問題,因有時在轉成utf8時有些因‘許功蓋’會產生一些‘\’)
最後記得檢查看MySQL的my.cnf設定檔內要加入以下設定
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
default-character-set=utf8
default-collation=utf8_general_ci

沒有留言:

狀態廣告

EasyReadMore