MySQL LOAD DATA INFILE 深度解析：高速導入、字段行分隔、轉義、前綴跳過、替換策略

MySQL LOAD DATA INFILE 深度解析：高速導入、字段行分隔、轉義、前綴跳過、替換策略

完整語法

LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE 'file_name.txt'
  [REPLACE|IGNORE]
  INTO TABLE tbl_name
  [FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
  ]
  [LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
  ]
  [IGNORE number LINES]
  [(col_name_or_user_var,...)]
  [SET col_name = expr, ...]

經典 CSV 導入

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

指定導入列

LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2);

文件路徑規則

• LOCAL：客戶端讀取文件並發送給服務端，路徑相對客戶端啟動目錄。
• 無 LOCAL：服務端直接讀取，路徑相對服務端 datadir 或絕對路徑。

IGNORE n LINES 跳過標題行

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

REPLACE vs IGNORE 處理主鍵衝突

• REPLACE：新行替換舊行（主鍵/唯一鍵相同）。
• IGNORE：跳過衝突行。
• 無選項且無 LOCAL：報錯中止；有 LOCAL：預設 IGNORE 行為。

索引影響與優化

空 MyISAM 表載入時，非唯一索引批量建立（同 REPAIR TABLE），極快。大表可 ALTER TABLE ... DISABLE KEYS 載入後 ENABLE KEYS 重建。

FIELDS/LINES 默認值

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

Windows 文件需 LINES TERMINATED BY '\r\n' 或 '\r'。

STARTING BY 跳過行前綴

文件每行以 xxx 開頭：

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test LINES STARTING BY 'xxx';

無前綴行會被整行跳過。

LINES TERMINATED BY 多字符行分隔

LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY '' LINES TERMINATED BY '\n%%\n' (joke);

TERMINATED / ENCLOSED / ESCAPED 詳解

• TERMINATED BY：字段分隔符，可多字符。
• ENCLOSED BY：引號字符，**必須單字符**。OPTIONALLY 只包裹字符型列。
• ESCAPED BY：轉義字符，**必須單字符**。空值輸出 \N，若設為空字符則無轉義（不推薦）。

導出示例：

-- OPTIONALLY
1,"a string",100.20
-- 無 OPTIONALLY
"1","a string","100.20"