MySQL 資料匯入全攻略：INSERT、REPLACE、LOAD DATA 與批次匯入技巧

INSERT 語句：基礎與進階用法

基本語法

INSERT [INTO] tbl_name [(col_list)] VALUES (val_list), ...;
INSERT [INTO] tbl_name SET col=val, ...;

• INTO 為選用（3.22.5+）。
• VALUES 必須包含所有欄位值，順序依建表定義；可用 DESCRIBE tbl_name 確認順序。
• 多值列表一次插入多行（3.22.5+），效能遠優於多條單行 INSERT。
• 指定欄位清單可僅初始化部分欄位，其餘採預設值。
• SET 語法（3.22.10+）可讀性佳，但不支援多行。
• 運算式可引用同列表前欄位：VALUES(15, col1*2)；不可反向引用。

INSERT ... SELECT：從其他表複製資料

INSERT INTO tbl1 (c1, c2) SELECT c3, c4 FROM tbl2;
-- 目標表欄位順序與來源一致時可省略欄位清單

• SELECT 不可含 ORDER BY。
• 目標表不可出現在 SELECT 的 FROM 子句中（ANSI SQL 禁止）。

REPLACE：插入或更新

語法同 INSERT，差異在於：若唯一鍵衝突，先刪除舊行再插入新行；INSERT 則報錯。可搭配 SELECT 使用。慎用，因會導致自動遞增 ID 變更、觸發器雙重觸發等副作用。

LOAD DATA INFILE：高速批次匯入

核心語法

LOAD DATA [LOCAL] INFILE 'file_path'
  [REPLACE | IGNORE]
  INTO TABLE tbl_name
  [FIELDS
    [TERMINATED BY '\t']
    [[OPTIONALLY] ENCLOSED BY '']
    [ESCAPED BY '\\']
  ]
  [LINES TERMINATED BY '\n']
  [IGNORE number LINES]
  [(col_list)];

• LOCAL：從客戶端讀取檔案（3.22.6+），預設行為等同 IGNORE。
• 無 LOCAL：檔案須在伺服器端，需 FILE 權限，且檔案需可被所有者讀取或位於資料目錄。
• REPLACE：唯一鍵衝突覆蓋；IGNORE：跳過衝突行；無參數則報錯停止。

檔案路徑解析規則

1. 絕對路徑：直接使用。
2. 相對路徑（含目錄分隔符）：相對於伺服器資料目錄。
3. 純檔名：相對於當前資料庫目錄。

FIELDS 與 LINES 子句詳解

實務範例：匯入 CSV

LOAD DATA INFILE 'data.csv'
  INTO TABLE orders
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;  -- 跳過標題列

效能優化建議

1. 大量匯入前 DISABLE KEYS（MyISAM），匯入後 ENABLE KEYS 重建索引，避免逐行維護索引。
2. 使用 LOAD DATA INFILE 替代多條 INSERT，速度快 20 倍以上。
3. 調大 bulk_insert_buffer_size（MyISAM）或 innodb_buffer_pool_size（InnoDB）。
4. 交易型表使用 START TRANSACTION + COMMIT 批次提交，而非自動提交。
5. 非交易表可 LOCK TABLES WRITE 後批次插入，解鎖前僅刷新一次索引。

結語

掌握 INSERT 變體、REPLACE 語意與 LOAD DATA INFILE 的豐富選項，並配合批次處理、索引暫停與交易控制，能讓資料匯入效能最大化，滿足從日常維護到 TB 級遷移的各種場景需求。