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

The Will Will Web

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

如何將 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 的話,一定要讓匯入的資料變成具有文字編碼特性的資料,要成功匯入資料我認為有以下步驟:

  1. 從 MySQL 4.0 匯出資料 ( 透過 mysqldump 指令 )
  2. 修正匯出的 SQL 指令檔
  3. 將資料修正成符合 big5 字集的格式
  4. 匯入檔時要設定正確的字集
  5. 將修正過的 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
如果你運氣好的話,應該一次就能成功了!