MySQL 原生 SELECT ... INTO OUTFILE 與 LOAD DATA INFILE 是高效處理 CSV 的核心工具。本文通過實戰案例深度解析參數含義、權限限制與常見坑點。

實戰場景：從寬表精準匯出指定欄位

表 tt_address 擁有 40+ 欄位，需匯出 uid, name, gender, email, module_sys_dmail_html 至 CSV。

匯出指令

SELECT 
    `uid`, `name`, `gender`, `email`, `module_sys_dmail_html`
INTO OUTFILE 'd:/test7.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM `tt_address`;

參數深度解析

子句	參數值	作用	範例輸出
FIELDS TERMINATED BY	','	欄位分隔符	uid,name,gender...
OPTIONALLY ENCLOSED BY	'"'	欄位包圍符（含特殊字元時自動加上）	"uid","name with,comma"
ESCAPED BY	'\\'	跳脫字元（預設反斜線）	"He said \"Hi\""
LINES TERMINATED BY	'\n'	行結束符	行尾換行

產出 CSV 範例

uid,name,gender,email,module_sys_dmail_html
2,"aa",m,"tian.li@atop-online.de",0

CSV 匯入 MySQL：LOAD DATA INFILE 完整語法

LOAD DATA LOCAL INFILE 'e:/input1.csv'
INTO TABLE `test1`
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
(`first_name`, `last_name`, `email`);

關鍵差異：LOCAL 關鍵字

• 無 LOCAL：檔案必須在「MySQL 伺服器」本機，由 Server 讀取，需 FILE 權限
• 有 LOCAL：檔案在「客戶端」機器，由 Client 讀取並傳送給 Server，無需 FILE 權限，受 local_infile=1 參數控制

欄位映射與跳過

-- 指定欄位順序（CSV 欄位順序與表不一致時）
LOAD DATA INFILE 'data.csv'
INTO TABLE `users`
FIELDS TERMINATED BY ','
(`email`, `username`, @dummy, `phone`)  -- @dummy 跳過第3欄
SET `created_at` = NOW();                -- 衍生欄位賦值

權限與安全配置（必讀）

伺服器端 my.cnf

[mysqld]
secure_file_priv = /var/lib/mysql-files/  # 限制檔案操作目錄
local_infile = 1                          # 允許 LOCAL 匯入

用戶權限授予

GRANT FILE ON *.* TO 'etl_user'@'%';  -- 僅授予 FILE 權限，非全權限

進階場景處理

處理標題行

LOAD DATA INFILE 'data.csv'
INTO TABLE `t1`
IGNORE 1 LINES;  -- 跳過第一行標題

處理 NULL 值

-- CSV 中用 \N 表示 NULL
LOAD DATA INFILE 'data.csv'
INTO TABLE `t1`
FIELDS TERMINATED BY ',' 
NULL VALUES ARE REPRESENTED BY '\\N';

錯誤容忍與跳過

LOAD DATA INFILE 'data.csv'
INTO TABLE `t1`
IGNORE 1 LINES
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 10 LINES;  -- 跳過前 10 行（如說明文字）

效能優化建議

1. 禁用索引/外鍵：匯入前 ALTER TABLE t DISABLE KEYS（MyISAM）/ SET FOREIGN_KEY_CHECKS=0，匯入後重建
2. 調大緩衝：SET GLOBAL bulk_insert_buffer_size = 1024*1024*1024; (MyISAM) / innodb_log_file_size (InnoDB)
3. 分區表並行：按分區拆分 CSV，多線程並行 LOAD DATA
4. 避免觸發器：匯入期間暫時 DROP TRIGGER

常見錯誤代碼速查

掌握 INTO OUTFILE/LOAD DATA INFILE 參數矩陣與權限模型，能將 CSV 處理效能提升數十倍，是海量資料遷移、ETL 流程、報表匯出的核心技能。

錯誤碼	訊息	原因	解決
1290	The MySQL server is running with the --secure-file-priv option	檔案路徑不在允許目錄	移動檔案至 secure_file_priv 目錄或修改配置
1045	Access denied for user	缺少 FILE 權限	GRANT FILE ON *.* TO user
29	File not found	路徑錯誤/權限不足	檢查路徑、OS 層面權限
1261	Row N doesn't contain data for all columns	欄位數不匹配	檢查 CSV 結構、指定欄位清單