MySQL 文字檔匯入與批次模式操作完整說明

MySQL 提供 LOAD DATA INFILE 高效匯入文字檔，以及批次模式執行 SQL 腳本，是資料遷移、ETL 載入與自動化部署的核心技能。本文詳細說明檔案格式要求、匯入指令參數、批次執行方式與輸出重導向。

一、文字檔匯入資料表

1. 檔案格式要求

• 每行一筆記錄
• 欄位值以 Tab (\t) 分隔
• 欄位順序與表定義一致
• 缺失值用 \N 表示

2. 基本匯入指令

LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

3. Windows 文字檔換行符處理

Windows 編輯器產生的檔案使用 \r\n 換行，需明確指定：

LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet
LINES TERMINATED BY '\r\n';

4. 進階參數

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE target_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;  -- 跳過標題行

二、批次模式執行 SQL 腳本

1. 準備腳本檔案

將欲執行的 SQL 陳述式逐行存入文字檔，如 batch-file.txt。

2. 命令列執行

mysql -h host -u user -p < batch-file.txt

3. 輸出重導向至檔案

mysql -h host -u user -p < batch-file.txt > out.txt

4. 在 mysql 互動模式中執行

mysql> SOURCE batch-file.txt;
-- 或
mysql> \. batch-file.txt;

三、實務應用場景

1. 大量初始資料載入（比 INSERT 快數十倍）
2. 跨平台資料遷移（CSV/TSV 統一格式）
3. 自動化部署腳本（Schema 變更、種子資料）
4. 定時 ETL 排程載入

四、注意事項

• LOCAL 關鍵字表示從客戶端讀取檔案，需客戶端與伺服器均啟用 local_infile=1。
• 檔案路徑在伺服器端時需具備 FILE 權限且路徑在 secure_file_priv 目錄下。
• 批次模式下語法錯誤預設會中止執行，可加 --force 參數忽略錯誤繼續。
• 大量匯入前建議暫停索引更新（ALTER TABLE ... DISABLE KEYS）匯入完成後重建（ENABLE KEYS）。