LOAD DATA INFILE 全參數詳解：從文本到表的標準化導入
完整語法圖
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[FIELDS
[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, ...]
參數分類速查
優先級與衝突處理
LOW_PRIORITY：延遲寫入，等無讀鎖REPLACE：主鍵/唯一鍵衝突 → 覆蓋舊行IGNORE：衝突 → 跳過新行
文件格式控制
| 子句 | 預設值 | CSV 常用值 |
|---|
| FIELDS TERMINATED BY | \t | , |
| FIELDS ENCLOSED BY | 空 | " |
| FIELDS ESCAPED BY | \ | \ |
| LINES TERMINATED BY | \n | \r\n (Windows) |
| LINES STARTING BY | 空 | 前綴字符 |
字符集與跳行
CHARACTER SET utf8mb4：指定文件編碼，避免亂碼IGNORE 1 LINES：跳過 CSV 標題行
實戰範例
1. 空格分隔簡單導入
LOAD DATA LOCAL INFILE "/home/beaver/db" INTO TABLE user
FIELDS TERMINATED BY ' ';
2. 標準 CSV 導入（含引號、轉義、標題行）
LOAD DATA INFILE "/home/mark/Orders.txt" REPLACE INTO TABLE Orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
3. 指定欄位導入（源文件欄位少於目標表）
LOAD DATA INFILE "/home/Order.txt" INTO TABLE Orders
(Order_Number, Order_Date, Customer_ID);
4. 導入時轉換/賦值（SET 子句）
LOAD DATA INFILE 'data.txt' INTO TABLE t
(col1, col2, @var1)
SET col3 = UPPER(@var1), col4 = NOW();
對比 mysqlimport
mysqlimport 為命令行封裝，本質調用 LOAD DATALOAD DATA 可在應用程式/存儲過程中動態調用，支援 SET 運算，更靈活
權限要求
- 需
FILE 權限（非 LOCAL 時）
LOCAL 受 local_infile 系統變量控制- 服務端
secure_file_priv 限制文件讀寫目錄
