如何將 MySQL 4.0 的繁體中文資料庫移轉到 MySQL 5.1

我很久以前曾經寫過一篇【MySQL 4.1/5 如何將現有 latin1 中文資料匯入成 UTF-8 資料】文章，而我最近為了協助客戶將他們舊有的網站從 PHP 4.3 + MySQL 4.0 全面升級到 PHP 5 + MySQL 5.1 也是費了一番功夫，本次的心得比上次更精簡、實用。

MySQL 4.0 升級到 MySQL 5.1 最大的困難點在於資料庫本身對文字編碼 (Text Encoding) 的支援，舊版的 MySQL 4.0 所匯出的資料對 MySQL 5.1 來說，所有的文字都是 latin1 字集，所以你在 MySQL 5.1 中建立資料庫時選的若是 latin1 的話，要匯入這些資料是完全沒問題的，只是你就再也無法透過任何 MySQL 管理工具看到正常的中文字了。

所以要正確的從 MySQL 4.0 匯入資料到 MySQL 5.0 的話，一定要讓匯入的資料變成具有文字編碼特性的資料，要成功匯入資料我認為有以下步驟：

從 MySQL 4.0 匯出資料 ( 透過 mysqldump 指令 )
修正匯出的 SQL 指令檔
將資料修正成符合 big5 字集的格式
匯入檔時要設定正確的字集
將修正過的 SQL 指令執行匯入到 MySQL 5.1
- 匯入過程若還有錯誤可手動再修正過再重新匯入
- 有時後會因為某筆資料的某欄位的最後一個字元當 ASCII 字碼大於 127 的字元 ( High bit ) 時，而導致將欄位結尾的單引號 ( ' ) 給吃掉，這在後來匯入時會導致錯誤發生！

首先，我先準備一個檔案 ( prepend-big5.txt )，用來修正舊版匯出檔案的字集 ( Big5 )，之後要加在 SQL 指令檔最前面的：

SET NAMES 'big5';

SET CHARACTER SET 'big5';

然後，我也寫了一支 PHP 程式用來修正 MySQL 4.0 匯出檔用的，將 SQL 指令全部轉成符合 Big5 字集的指令，其實最主要也是修正「許功蓋」的問題而已，大部分的文字都是 Big5 編碼應該是沒錯的。

檔名：Fix_MySQL_Script_For_Big5.php

<?php

set_time_limit(0);

$fp = fopen('php://stdin', "r");

while($line = fgets($fp))
{
    $hasB5 = false;
    $len = strlen($line);

    for($i=0 ; $i < $len ; $i++)
    {
        $highbit = ord($line[$i]);
        if($highbit >= 0x81 && $highbit <= 0xFE)
        {
            $hasB5 = true;
            break;
        }
    }

    if($hasB5) for($high=0x81; $high <= 0xFE; $high++)
    {
        $line = str_replace(
                   chr($high) . '\\' . '\\', 
                   chr($high) . '\\', 
                   $line);
    }
    echo $line;
}

fclose($fp);

?>

這支程式會讀入 stdin 的串流資料，並且修正語法後在輸出到 stdout 串流中。

準備好資料與程式後，就是重頭戲了。

從 MySQL 4.0 匯出資料並修正字集問題

1. 先將之前準備的 prepend-big5.txt 加到 dbname.sql 檔案中

cat prepend-big5.txt > dbname.sql

2. 從 MySQL 4.0 匯出指令檔，並附加到 dbname.sql 中

mysqldump -Q --all --add-drop-table -u root -pYourPassword dbname >> dbname.sql

3. 透過 Fix_MySQL_Script_For_Big5.php 指令修正 SQL 指令檔將 dbname.sql 轉換成 dbname-big5.sql

cat dbname.sql | php -q Fix_MySQL_Script_For_Big5.php > dbname-big5.sql

此時，這個 dbname-big5.sql 已經是符合 big5 字集的 SQL 指令檔了，這時就可以準備將資料正式匯入 MySQL 5.1 資料庫。

匯入資料至 MySQL 5.1

1. 建立資料庫 ( 預設為 utf-8 字集 )

mysqladmin -u root -pYourPassword create dbname

2. 匯入資料 ( 需指明匯入的指令檔字集為 big5 )

mysql --default-character-set=big5 -u root -pYourPassword dbname < dbname-big5.sql

如果你運氣好的話，應該一次就能成功了！

The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享