LOAD DATA INFILE 批次載入完全指南
LOAD DATA INFILE 是 MySQL 最快的批次載入工具，繞過 SQL 解析、優化、執行引擎，直接由儲存引擎寫入資料頁。40 萬行 3~5 秒實測，比 INSERT 快 10~50 倍。
核心語法與關鍵參數
LOAD DATA [LOCAL] INFILE 'file_path'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[FIELDS
TERMINATED BY '\t'
[[OPTIONALLY] ENCLOSED BY '"']
ESCAPED BY '\\'
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name_or_user_var, ...)]
[SET col_name = expr, ...];
關鍵選項對照
| 選項 | 作用 | 衝突處理 |
|---|
| (無) | 預設 | 唯一鍵衝突 → 報錯回滾，整批失敗 |
| IGNORE | 跳過衝突行 | 衝突行丟棄，繼續載入，返回 Skipped 計數 |
| REPLACE | 覆蓋衝突行 | 依主鍵/唯一鍵刪除舊行再插入新行，返回 Deleted + Inserted |
| LOCAL | 客戶端讀檔傳給伺服器 | 需客戶端啟用 --local-infile=1，伺服器 local_infile=ON |
實戰測試案例 (InnoDB 表，主鍵 id)
-- 表結構
CREATE TABLE tmp_loaddata (
id INT PRIMARY KEY,
name VARCHAR(10)
) ENGINE=InnoDB;
-- 初始資料
INSERT INTO tmp_loaddata VALUES (1, 'test');
-- 檔案 /home/user/1.txt 內容：
1,new update
2,new update
1. 無選項：衝突報錯回滾
LOAD DATA INFILE '/home/user/1.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
-- 表資料不變 (事務回滾)
2. IGNORE：跳過衝突保留舊值
LOAD DATA INFILE '/home/user/1.txt' IGNORE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
-- Query OK, 1 row affected, 1 warning
-- Records: 2 Deleted: 0 Skipped: 1 Warnings: 0
-- 結果：id=1 保留 'test'，id=2 新增 'new update'
3. REPLACE：衝突覆蓋更新
LOAD DATA INFILE '/home/user/1.txt' REPLACE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
-- Query OK, 3 rows affected
-- Records: 2 Deleted: 1 Skipped: 0 Warnings: 0
-- 結果：id=1 更新為 'new update'，id=2 新增 'new update'
本地 vs 遠端模式差異
| 維度 | LOCAL (客戶端讀檔) | 非 LOCAL (伺服器讀檔) |
|---|
| 檔案位置 | 客戶端機器 | 伺服器機器 (需 secure_file_priv 目錄) |
| 權限需求 | 客戶端讀取權限 | 伺服器 FILE 權限 + 目錄存取權 |
| 預設衝突行為 | IGNORE | 報錯回滾 |
| 網路流量 | 客戶端 → 伺服器傳輸資料 | 無額外流量 |
| 啟用條件 | 客戶端 --local-infile=1、伺服器 local_infile=ON | 預設啟用 (受 secure_file_priv 限制) |
中文/UTF-8 載入關鍵點
- 檔案編碼 必須為 UTF-8 (無 BOM)。
- 指定
CHARACTER SET utf8mb4 (或 utf8)：
LOAD DATA INFILE 'file.txt' CHARACTER SET utf8mb4 INTO TABLE t ...
- 連線字元集一致：
SET NAMES utf8mb4; 或 JDBC/PDO 參數指定。
ENCLOSED BY '"' 處理含逗號/換行的欄位；ESCAPED BY '\\' 處理引號轉義。
效能優化建議
- 目標表 先匯入再建索引：
ALTER TABLE t DISABLE KEYS (MyISAM) / SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; (InnoDB) → 載入 → 重新啟用。
- 大檔案拆分並行載入 (多線程/多連線)，單表單線程受限於單 CPU 核心。
innodb_flush_log_at_trx_commit=2、sync_binlog=0 臨時調整 (導入期間犧牲安全換速度)。- 監控
Innodb_rows_inserted、Innodb_data_written 確認進度。
