LOAD DATA INFILE 完整語法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_path'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\']
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
[IGNORE number LINES]
[(col_name_or_user_var, ...)]
[SET col_name = expr, ...]
關鍵參數對照表
| 子句 | 預設值 | 說明 | 常用場景 |
|---|
FIELDS TERMINATED BY | \t (Tab) | 欄位分隔符 | CSV 用 ','，自定義用 '|' 等 |
ENCLOSED BY | 空 (無包裹) | 欄位包裹字符 (如雙引號) | CSV 標準 '"'"'"，含分隔符/換行的欄位必須包裹 |
ESCAPED BY | \\ | 轉義字符 | 關閉轉義用 ESCAPED BY '' |
LINES TERMINATED BY | \n | 行結束符 | Windows 檔案用 '\r\n' |
IGNORE n LINES | 0 | 跳過前 n 行 (如標題行) | IGNORE 1 LINES |
LOCAL | 無 | 從客戶端讀取文件 | 文件在應用伺服器、非 DB 伺服器時必加 |
REPLACE / IGNORE | 錯誤停止 | 主鍵/唯一鍵衝突處理 | REPLACE 覆蓋，IGNORE 跳過 |
文件路徑解析規則 (無 LOCAL 時，服務端讀取)
- 相對路徑
./data.txt → 相對於 datadir
- 無路徑
data.txt → 相對於當前資料庫目錄 (datadir/db_name/)
- 絕對路徑
/var/lib/mysql-files/data.txt → 直接讀取 (需 secure_file_priv 允許)
實戰範例：標準 CSV 導入
LOAD DATA LOCAL INFILE '/tmp/users.csv'
INTO TABLE `users`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(`id`, `username`, `email`, `created_at`);
導出對應：SELECT ... INTO OUTFILE
SELECT `id`, `username`, `email`, `created_at`
FROM `users`
INTO OUTFILE '/tmp/users_export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';
原則：導出與導入的 FIELDS/LINES 參數**必須完全一致**，否則解析錯誤。
常見錯誤與解決
| 錯誤 | 原因 | 解決 |
|---|
Data truncation: Data truncated for column 'id' at row 3 | 數據超出欄位定義長度/類型 | 檢查 CSV 欄位順序、類型匹配、ENCLOSED BY 是否正確 |
File not found (Errcode: 2) | 路徑錯誤或 secure_file_priv 限制 | 用 SHOW VARIABLES LIKE 'secure_file_priv'; 查看允許目錄，或用 LOCAL |
Access denied (Errcode: 13) | 文件系統權限不足 | 確保 mysql 進程用戶可讀文件/目錄 |
| 亂碼 | 字符集不匹配 | 導出/導入雙方同加 CHARACTER SET utf8mb4 |
| 行數不對 | 欄位含換行符未包裹 | 導出時加 OPTIONALLY ENCLOSED BY '"'"'"，導入同樣 |
效能優化建議
- 關閉索引：導入前
DROP INDEX，導入後 ADD INDEX (見文章 89232)
- 調大緩衝：
bulk_insert_buffer_size (MyISAM) / innodb_log_file_size (InnoDB)
- 禁用外鍵/唯一檢查：
SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; 導入後恢復
- 分區表並行導入：按分區拆分文件，多線程導入
- 使用 mydumper/myloader：並行、壓縮、斷點續傳，大庫首選
安全提醒
secure_file_priv 限制導入導出目錄，防止任意文件讀寫。LOCAL 選項需客戶端支援 (--local-infile=1) 且服務端 local_infile=ON。- 生產環境嚴禁直接導入未驗證的外部文件。
