MySQL CSV 批量導入導出實戰：LOAD DATA INFILE 與 SELECT INTO OUTFILE 完整參數解析
1. 導入：LOAD DATA INFILE
LOAD DATA [LOW_PRIORITY] [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, ...)]
實戰示例
LOAD DATA INFILE 'C:/data/test.csv'
REPLACE INTO TABLE my_table
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(Id, @dummy, DayOfWeek, PdDistrict, Address, X, Y)
SET update_time = CURRENT_TIMESTAMP;
關鍵參數詳解
| 參數 | 說明 | 常見值 |
|---|
| REPLACE / IGNORE | 主鍵/唯一鍵衝突處理：REPLACE 覆蓋，IGNORE 跳過，不指定則報錯 | REPLACE |
| FIELDS TERMINATED BY | 字段分隔符 | ',' (CSV), '\t' (TSV) |
| ENCLOSED BY | 字段包裹符（含引號時） | '\"' |
| ESCAPED BY | 轉義字符 | '\\' |
| LINES TERMINATED BY | 行結束符 | Windows: '\r\n', Linux: '\n' |
| IGNORE n LINES | 跳過前 n 行（通常跳過標題行） | 1 |
| (col, @dummy, ...) | 列映射：@dummy 表示跳過該 CSV 列不導入 | 按需映射 |
| SET col=expr | 導入時額外賦值（如寫入導入時間） | CURRENT_TIMESTAMP |
2. 導出：SELECT INTO OUTFILE
SELECT * FROM my_table
INTO OUTFILE 'C:/export/test.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
ESCAPED BY '\"'
LINES TERMINATED BY '\n';
3. 權限與安全限制
- 需
FILE 權限：GRANT FILE ON *.* TO 'user'@'host';
- 文件路徑必須在
secure_file_priv 目錄下（SHOW VARIABLES LIKE 'secure_file_priv'; 查看）
- 導出文件不能覆蓋已存在文件，需先刪除或改名
LOCAL 關鍵字：客戶端讀取本地文件發送給服務端，不受 secure_file_priv 限制，但需客戶端支持
4. 大文件導入優化建議
- 臨時關閉索引：
ALTER TABLE t DISABLE KEYS; 導入後 ENABLE KEYS;（MyISAM 有效，InnoDB 無效）
- 調大
bulk_insert_buffer_size、innodb_buffer_pool_size
- 事務批次提交（自腳本控制）
- 使用
mysqlimport 命令行工具（封裝 LOAD DATA）
