MySQL 4.0 升級 5.x 字符集遷移實戰：mysqldump 分離結構與數據

MySQL 4.0 → 5.x 升級遷移：字符集問題徹底解決

MySQL 4.0 僅支援 latin1，4.1+ 引入多字符集（GBK、UTF8 等），且多字節字符儲存方式改變（latin1 下漢字佔 2 字節，GBK/UTF8 下佔 1 字節）。本文給出分離結構與數據的遷移標準流程。

遷移核心步驟

1. 導出結構（無數據）

   mysqldump --no-data -u root -p database > struct.sql
   

2. 導出純數據（無建表語句，關閉 extended-insert）

   mysqldump --no-create-info --extended-insert=FALSE -u root -p database > data.sql
   

   --extended-insert=FALSE 使每行單獨 INSERT，便於後續編輯與錯誤定位。

3. 修改結構文件字符集聲明（vi 批量替換）

   vi struct.sql
   # 將舊版 TYPE=MyISAM 改為 ENGINE=MyISAM 並指定字符集
   :%s/) TYPE=MyISAM;/) ENGINE=MyISAM DEFAULT CHARSET=gbk;/g
   :%s/) TYPE=HEAP.*$/) ENGINE=MEMORY DEFAULT CHARSET=gbk;/g
   :wq
   

   目標字符集可改為 utf8 或 utf8mb4（推薦）。

4. 導入結構與數據

   mysql -u root -p database < struct.sql
   mysql -u root -p database < data.sql
   

關鍵變化說明

遷移後驗證

-- 檢查表字符集
SHOW CREATE TABLE t;

-- 檢查數據完整性
SELECT HEX(col), col, CHAR_LENGTH(col), LENGTH(col) FROM t WHERE col LIKE '%中文%';
-- GBK: LENGTH = 2 * CHAR_LENGTH
-- UTF8: LENGTH = 3 * CHAR_LENGTH

常見問題

• 導入報錯 "Invalid default value"：4.0 允許 DATETIME DEFAULT '0000-00-00'，5.7+ NO_ZERO_DATE 模式禁止。解決：sql_mode 移除 NO_ZERO_DATE 或修正默認值。
• 索引長度超限：UTF8 下 VARCHAR(255) 索引佔 765 字節，超過 767 限制（InnoDB 頁面 1/2）。解決：縮短列長度、使用前綴索引、升級 MySQL 5.7+ 支援 3072 字節。
• 外鍵約束名衝突：4.0 無外鍵，遷移後手動添加時注意名稱唯一。