OuTian's 芭樂閣

mysql character set

2006-08-16 03:24

之前就常在網路上看到人家討論 mysql character set 的問題
不過手邊一直沒遇到要轉的 ....
所以也沒實際處理過 ..

但前幾天要幫人搬一個討論區 , 剛好有機會了.
順手做個紀錄.
　
　
狀況 :
　　　原來的 MySQL server 是 5.0.x on FreeBSD ,
　　　因為本來的管理者只有在 ports 中簡單的 make install 而已
　　　所以在編譯的時沒有加入任何的 extra character , 只有預設的 latin1 ..

　　　上面跑了一個php的討論區, 看code的內容會發現 ....
　　　當偵測到 MySQL 4.1 版本以上 ( 4.1/5.0/5.1 ..) 時 ,
　　　會在連線後加入 SET NAMES ‘big5′ 的設定
　　　但是因為 mysql server 根本沒編入 big5 character 的 support ...
　　　所以該行變成沒任何作用 ....
　　　於是從頭到尾, 討論區都是用 latin1 character set 存東西

問題 :
　　　現在要把整個 database 要轉移到另一台 NetBSD 去 ,
　　　但 NetBSD 的 pkgsrc 預設就有 –with-extra-charsets=all
　　　所以照一般程序直接 dump & restore 的話會變亂碼
　　　(因為那個 SET NAMES ‘big5′ 生效了)
　
　
解決 :
　　　懶一點的話 , 這兩種作法都可以讓資料在新 db 上正常運作 .....
　　　1. mysql server 和之前一樣都不加任何 extra character
　　　2. 改一下 php code , mark 掉 SET NAMES ‘big5′ ....

　　　但始終覺得不是正解王道 ....
　　　於是整個搬移的過程如下 -

　　　(1) dump database

　　　　　mysqldump –default-character-set=latin1 -u user -p –skip-extended-insert DATABASE > DATABASE.sql

　　　　　加 –skip-extended-insert 是為了讓一條sql一筆 record
　　　　　雖然 restore 速度較慢, 但是要去除特定筆資料時才會方便.

　　　(2) 調整一下 ... 把 table 的 default character set 改成 big5

　　　　　perl -pi -e ‘s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=big5/;'　DATABASE.sql

　　　　　編輯該檔案 , 在最前面加上 SET NAMES big5;

　　　(3) 由於直接 dump 出來後, 一些含 \ 的中文字會出問題 ,
　　　　　例如 許、功、蓋、淚、 ... etc　會被多加上一個 \ 而導致 sql 錯誤

　　　　　所以用一個小 script 去整理 ....

　　　　　我先去找出了這些字的 list 後 ,
　　　　　寫一個檔案 , 裡面作成這樣的格式 -

　　　　　s/功\\\/功\/g;
　　　　　s/淚\\\/淚\/g;
　　　　　s/蓋\\\/蓋\/g;
　　　　　s/許\\\/許\/g;
　　　　　..... etc

　　　　　存成 replace.pl , 然後再

　　　　　perl -pi replace.pl DATABASE.sql

　　　(4) 最後把 database restore 回去就好了

　　　　 mysql –default-character-set=big5 -u user -p DATABASE < DATABASE.sql

　　　　　在 restore 的過程中 , 可能遇到一些特殊怪字會導致 sql 出問題

　　　　　這時根據錯誤訊息中指定的行號 , 用 editor 修正一下就好了

　　　　 (如果前面 dump 時沒有加 --skip-extended-insert 的話,
　　　　　會因為很多筆 record 塞在同一條 sql 中 , 而難以修改 )

註記:
　　　如果不是要轉 big5 , 而是轉 utf8 的話
　　　變成在 (2) 中把 big5 改 utf8 , 並在做完 (3) 後 ,
　　　跑一次
　　　　　piconv -f big5 -t utf8 DATABASE.sql > DATABASE_utf8.sql

　　　最後要 restore 時則放 –default-character-set=utf8
　　　這樣應該就可以了 ...

---