MySQL INSERT 數據插入全攻略：語法變體、批量導入、REPLACE 與 LOAD DATA 實戰

前言：寫入是業務的起點，插入效能與正確性同等重要

MySQL 提供多樣化的插入語法：基本 INSERT、INSERT ... SELECT、REPLACE、LOAD DATA INFILE、mysqlimport。本文系統梳理各語法適用場景、參數細節、效能優化與避坑指南。

一、INSERT 基本語法四大變體

1. 完整列值插入（須匹配所有列順序）

INSERT INTO worker VALUES ('tom', 'tom@yahoo.com');
-- 多行單語句 (3.22.5+)
INSERT INTO worker VALUES ('tom','tom@yahoo.com'), ('paul','paul@yahoo.com');

2. 指定列插入（其餘列取 DEFAULT）

INSERT INTO worker (name) VALUES ('tom');
-- 多行
INSERT INTO worker (name) VALUES ('tom'), ('paul');

3. SET 語法（列名=值，可讀性佳，不支援多行）

INSERT INTO worker SET name='tom', email='tom@yahoo.com';

4. 運算式引用前置列

INSERT INTO tbl (col1, col2) VALUES (15, col1*2);  -- 合法
INSERT INTO tbl (col1, col2) VALUES (col2*2, 15);  -- 非法：col2 尚未賦值

二、INSERT ... SELECT：從查詢結果批量寫入

INSERT INTO tbl1 (col1, col2) SELECT col3, col4 FROM tbl2;
-- 省略目標列表（須一一對應）
INSERT INTO tbl1 SELECT col3, col4 FROM tbl2;

限制

• 禁止 ORDER BY 子句。
• 目標表不可出現在 FROM 子句（ANSI SQL 禁止，避免自我引用死循環）。

三、REPLACE：主鍵/唯一鍵衝突時「刪舊插新」

REPLACE INTO tbl (id, val) VALUES (1, 'new');
-- 等效邏輯：
-- IF EXISTS (SELECT 1 FROM tbl WHERE id=1) THEN DELETE ...; INSERT ...;

• 需表有 PRIMARY KEY 或 UNIQUE 索引，否則退化為 INSERT。
• 同支援 REPLACE ... SELECT。
• 影響行數：1=純插入，2=刪1插1，>2=多唯一鍵衝突刪多插1。
• 謹慎使用：觸發 DELETE 觸發器、外鍵級聯、自增 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 n LINES]
  [col_name_or_user_var, ...]
  [SET col_name = expr, ...];

關鍵參數詳解

CSV 匯入範例

LOAD DATA INFILE 'data.csv'
INTO TABLE tbl
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

NULL 處理規則

• 輸入 \N (預設轉義) → 存為 SQL NULL。
• 輸出 SELECT ... INTO OUTFILE 同理。

五、mysqlimport：命令列批量導入封裝

mysqlimport [options] db_name file1.txt [file2.txt ...]
-- 選項對應 LOAD DATA 參數：
-- --fields-terminated-by=, --fields-enclosed-by=\" --ignore-lines=1 --local --replace --ignore

檔名即表名（去除副檔名），自動產生 LOAD DATA INFILE。

六、效能優化黃金法則

1. 批量 INSERT：單條 INSERT 改多值 VALUES (...),(...),(...)，每批 500~5000 行，降低網絡往返與解析開銷。
2. 關閉索引/外鍵/唯一檢查（大量導入前）：
   SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; ALTER TABLE tbl DISABLE KEYS;
   導入後重啟並 ALTER TABLE tbl ENABLE KEYS; (MyISAM) 或 ANALYZE TABLE (InnoDB)。
3. 使用 LOAD DATA INFILE：比多行 INSERT 快 10~20 倍，直寫儲存引擎繞過 SQL 層。
4. 調大 bulk_insert_buffer_size (MyISAM) / innodb_log_file_size / innodb_buffer_pool_size (InnoDB)。
5. 事務分組：InnoDB 多行 INSERT 包在單一事務提交，減少 fsync。