2011年6月27日

MySQL 疑難雜症之 mysqldump 匯出亂碼篇

較新版本的 MySQL 支元多國語言編碼的資料儲存,建立資料庫結構或查詢、匯出時,必須自己注意編碼問題。

以下以 UTF-8 為例:

my.ini 的設定:
[mysqld]
init_connect='SET NAMES utf8'
default-character-set = utf8
[client]
default-character-set = utf8

建立資料庫:
CREATE DATABASE `aa` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

建立資料表:
CREATE TABLE  IF NOT EXISTS `aat` (
...略...
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

mysqldump 匯出資料:

mysqldump -u USER -p DBNAME --default-character-set=utf8 > DBNAME.sql

有些網站在開發時,並沒有正確指定編碼,採用了預設的 latin1 ,並且將 big5 的中文資料直接儲存。這種情況如果直接用 mysqldump 直接匯出,結果可能是一堆亂碼,必須加上 --default-character-set=latin1 讓匯出的資料保持正常的 big5 編碼。

mysqldump -u USER -p DBNAME --default-character-set=latin1 > DBNAME.sql

如果在匯出備份資料時,忘記加上參數,產生的亂碼資料,用 iconv 可以救回部分(以下指令示範假設匯出檔為錯誤編碼的utf8),參數 "-c" 表示忽略無法轉碼的字元。

iconv -c -f utf8 -t latin1 DBNAME.sql > DBNAME.big5.sql

匯出檔中可能存在錯誤指定編碼格式的SQL語法,可以使用 perl 指令進行替換。

perl -pi -e 's/latin1/big5/g' DBNAME.big5.sql

對於 big5 編碼的匯出檔,也可以用 iconv 轉換成 utf8 格式。

iconv -c -f big5 -t utf8 DBNAME.sql > DBNAME.utf8.sql

再進行編碼格式設定的替換。

perl -pi -e 's/big5/utf8/g' DBNAME.utf8.sql

轉換後資料若存在許功蓋問題,會在 INSERT 資料的語法看到許功蓋等字都被多加了一個倒斜線,例如:許\功\蓋\閱\。

perl -pi -e 's/許\\\\/許/g' DBNAME.utf8.sql

由於有很多中文字都存在許功蓋問題,可以寫一段 PHP Script 產生所有需要的指令碼。

<?php
$str = <<<_HTML_
么 功 吒 吭 沔 坼 歿 俞 枯 苒 娉 珮 豹 崤 淚 許 廄 琵 跚 愧 稞 鈾 暝 蓋 墦 穀 閱 璞 餐 縷 擺 黠 孀 踊 髏 躡 尐 佢 汻 岤 垥 柦 胐 娖
 涂 罡 偅 惝 牾 莍 傜 揊 焮 茻 鄃 幋 滜 綅 赨 塿 縷 槙 擺 箤 踊 嫹 髏 潿 蔌 醆 嬞 獦 佢 螏 餤 燡 螰 駹 礒 鎪 瀙 酀 瀵 騱 酅 贕 鱋 鱭
_HTML_;

foreach (explode(" ", $str) as $word) {
        echo "perl -pi -e 's/$word\\\\/$word/g' DBNAME.utf8.sql\n";
}

重新將資料匯入 mysql 時,可以加上 "-f" 參數強制忽略 SQL 語法的錯誤。

mysql -f -u USER -p DBNAME < DBNAME.utf8.sql

沒有留言:

張貼留言

lyhcode by lyhcode
歡迎轉載,請務必註明出處!