MySQL LOAD DATA INFILE 完整實戰指南：從入門到避坑

核心語法

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var, ...)]
[SET col_name = expr, ...]

關鍵參數詳解

實戰避坑記錄

錯誤 1：把 SQL INSERT 語句直接塞進檔案

錯誤做法：檔案內容為 INSERT INTO t (a,b) VALUES (1,'x'),(2,'y');

正確做法：檔案只需純資料，每行一筆，欄位對應表結構：

1,"x"
2,"y"

錯誤 2：分隔符與包裹字元不匹配

預設是 Tab 分隔、無包裹。若資料含逗號、引號，必須明確指定：

LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE t
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

錯誤 3：Linux 伺服器權限問題 (Errcode: 13)

現象：無 LOCAL 時，檔案必須在伺服器 datadir 或全域可讀目錄，且需 FILE 權限。

解法：

• 加上 LOCAL 關鍵字 → 從客戶端讀取，無需伺服器權限。
• 或將檔案放入 /var/lib/mysql-files/（需 secure_file_priv 允許）。

錯誤 4：Windows 換行符 \r\n 未處理

Windows 產出的文字檔結尾為 \r\n，必須指定 LINES TERMINATED BY '\r\n'，否則最後一欄會殘留 \r。

進階技巧

1. 使用使用者變數進行轉換

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @var1)
SET column2 = @var1 / 100;

2. 直接寫入當前時間

LOAD DATA INFILE 'file.txt'
INTO TABLE t1 (column1, column2)
SET column3 = CURRENT_TIMESTAMP;

3. 忽略某欄位

LOAD DATA INFILE 'file.txt'
INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);

4. 大量匯入加速

• 匯入前：ALTER TABLE t DISABLE KEYS;（僅 MyISAM）
• 匯入後：ALTER TABLE t ENABLE KEYS;
• 或設定 bulk_insert_buffer_size、innodb_flush_log_at_trx_commit=2 臨時放寬。

與 SELECT ... INTO OUTFILE 對照

兩者欄位/行處理選項必須完全一致，才能正確往返匯出入。

常見錯誤代碼速查

最佳實踐清單

1. 資料檔採用 UTF-8 編碼，欄位用逗號分隔、雙引號包裹。
2. 明確指定 FIELDS 與 LINES 所有子句，避免預設值誤判。
3. 生產環境一律使用 LOCAL，避免伺服器權限與路徑問題。
4. 大量匯入前關閉索引、外鍵檢查、binlog，匯入後恢復。
5. 匯入後驗證筆數與 SHOW WARNINGS。

掌握以上細節，LOAD DATA INFILE 即可成為你最高效、最穩定的大量資料匯入利器。