MySQL 版本差異導致數據遷移報錯三大經典案例與解決方案

MySQL 版本差異導致數據遷移報錯三大經典案例與解決方案

跨版本遷移 (如 3.x → 5.0、5.1 → 5.7、5.7 → 8.0) 常因語法、存儲引擎、保留字變更導致導入失敗。本文總結三大高頻報錯及修復方案。

案例一：DELIMITER 在 phpMyAdmin 中報錯

ERROR: You have an error in your SQL syntax near 'DELIMITER //'

原因：DELIMITER 是客戶端命令，非 SQL 標準語句。phpMyAdmin、MySQL Workbench 等 GUI 工具不支持。

解決：

• 使用命令行導入：mysql -u root -p db_name < dump.sql。
• 或在 GUI 工具中去除 DELIMITER 行，將存儲過程/觸發器定義拆分為單條語句執行。

案例二：USING BTREE 索引語法不兼容

KEY `authorid` (`authorid`) USING BTREE
→ ERROR 1064 near 'USING BTREE'

原因：舊版本導出的 INDEX 列 USING BTREE(列) 語法，新版本要求 INDEX 列 (列) USING BTREE 或默認即 BTREE 可省略。

解決：

-- 批量替換 (sed 示例)
sed -i "s/KEY \`\([^)]*\)\` USING BTREE/KEY \`\1\` (\`\1\`) USING BTREE/g" dump.sql
-- 或直接刪除 USING BTREE 子句 (InnoDB 默認即 BTREE)
sed -i "s/ USING BTREE//g" dump.sql

案例三：目標庫不支持 InnoDB (skip-innodb)

ERROR: Unknown storage engine 'InnoDB'

原因：my.cnf 中配置 skip-innodb 或編譯時未包含 InnoDB。

解決：

1. 編輯 my.cnf，註釋或刪除 skip-innodb。
2. 重啟 MySQL：systemctl restart mysqld。
3. 驗證：SHOW ENGINES; 確認 InnoDB 為 YES 或 DEFAULT。

遷移最佳實踐清單

• 使用 mysqldump --routines --triggers --events --single-transaction --master-data=2 導出。
• 目標庫預建字符集：CREATE DATABASE db CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;。
• 導入前檢查 sql_mode 兼容性 (8.0 默認 ONLY_FULL_GROUP_BY 等)。
• 大數據量採用 mydumper/myloader 並行導入導出。
• 遷移後跑 mysql_upgrade (升級場景) 或 ANALYZE TABLE 更新統計信息。