異構遷移 SQL Server → MySQL，百萬級大表首選：BCP 高效導出文本 + LOAD DATA INFILE 極速導入。本文提供完整參數手冊、編碼對齊、常見坑點與自動化腳本模板。
第一階段：BCP 導出 SQL Server 數據
核心命令
bcp "SELECT col1, col2 FROM db.dbo.table WHERE ..." queryout "F:\export.txt" -c -t"," -r"\n" -S 1.2.3.4 -U sa -P 1111
關鍵參數詳解
| 參數 | 含義 | 推薦值 |
|---|
queryout | 用SQL查詢導出（非整表） | 配合WHERE分片導出 |
-c | 字符模式，字段分隔符\t，行分隔符\n | 通用文本導出首選 |
-w | Unicode模式，nchar存儲 | 含中文/特殊字符時優先 |
-t | 字段分隔符 | -t"," (CSV) 或 -t"|" |
-r | 行分隔符 | -r"\n" (LF) 或 -r"\r\n" (CRLF) |
-C 65001 | 導出 UTF-8 (代碼頁 65001) | **必加**，配合MySQL utf8mb4 |
-F / -L | 起始/結束行號 | 分片導出大表 |
-k | 空值保持NULL而非默認值 | 保持語義 |
UTF-8 導出關鍵
bcp "query" queryout "F:\export.txt" -c -C 65001 -t"," -r"\n" -S ... -U ... -P ...
第二階段：MySQL LOAD DATA 極速導入
核心命令
LOAD DATA LOCAL INFILE 'F:/export.txt'
INTO TABLE target_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES -- 若含表頭
(col1, col2, col3);
關鍵子句解析
| 子句 | 作用 |
|---|
LOCAL | 從客戶端讀文件（無則從服務端讀，需secure_file_priv） |
CHARACTER SET utf8mb4 | **關鍵**：指定文件編碼，防止中文亂碼 |
FIELDS TERMINATED BY | 字段分隔符，對應BCP -t |
OPTIONALLY ENCLOSED BY | 引用字符（雙引號），處理含分隔符的字段 |
ESCAPED BY | 轉義字符（反斜槓） |
LINES TERMINATED BY | 行分隔符，對應BCP -r |
IGNORE n LINES | 跳過表頭行 |
(col1, col2...) | 指定導入欄位順序，支援欄位映射/跳過 |
進階技巧：導入時附加額外欄位
LOAD DATA LOCAL INFILE 'F:/export.txt'
INTO TABLE target_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
SET
created_by = 'migration_script',
created_at = CURRENT_TIMESTAMP,
batch_id = 20240101;
常見問題排查表
| 現象 | 原因 | 解決 |
|---|
The used command is not allowed with this MySQL version | 客戶端未啟用 local-infile | 啟動客戶端加 --local-infile=1；服務端 local_infile=ON |
| 中文亂碼 | 編碼不匹配 | BCP加-C 65001，LOAD DATA加CHARACTER SET utf8mb4 |
| 導入極慢 | 索引/外鍵/唯一檢查 | 導入前SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0;，導入後恢復 |
| 主鍵衝突 | 重復數據 | 用REPLACE或IGNORE關鍵字 |
| 文件過大內存溢出 | 單次導入過大 | BCP分片-F/-L，分多文件導入 |
自動化腳本模板
#!/bin/bash
# sqlserver_to_mysql.sh
BCP_QUERY="SELECT id, name, email FROM dbo.users WHERE created_at >= '2024-01-01'"
OUT_FILE="/tmp/users_export.txt"
MYSQL_TABLE="users_migrated"
# 1. BCP 導出
bcp "$BCP_QUERY" queryout "$OUT_FILE" -c -C 65001 -t"," -r"\n" -S $SRV -U $USR -P $PWD
# 2. MySQL 導入
mysql --local-infile=1 -h $MYSQL_HOST -u $MYSQL_USR -p$MYSQL_PWD $MYSQL_DB -e "
SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '$OUT_FILE'
INTO TABLE $MYSQL_TABLE
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
(id, name, email);
SET UNIQUE_CHECKS=1; SET FOREIGN_KEY_CHECKS=1;
"
echo "Migration completed: $(date)"
BCP + LOAD DATA 組合，是異構大表遷移的「黃金搭檔」，實測百萬行秒級導入。
