逐筆呼叫 INSERT 不僅程式碼冗長,更因每次都需經歷 SQL 解析、優化、執行的完整流程,造成資料庫伺服器不必要的 CPU 與網路開銷。MySQL 擴充標準 SQL,支援單一 INSERT 陳述式插入多筆資料,是批次寫入的標準高效做法。
INSERT INTO users (name, age)
VALUES ('姚明', 25), ('比爾·蓋茲', 50), ('火星人', 600);
✅ 正確:VALUES (1), (2), (3), (4), (5)
❌ 錯誤:VALUES (1,2,3,4,5) → ERROR 1136: Column count doesn't match value count at row 1
錯誤原因:MySQL 將 (1,2,3,4,5) 視為單筆資料的 5 個欄位值,而非 5 筆單欄位資料。
-- 必須:每組括號內值的數量 = 表的總欄位數,且順序完全一致
INSERT INTO table1 VALUES (1), (2), (3), (4), (5);
若表結構變更(增減欄位),省略欄位名的寫法極易報錯,生產環境建議永遠显式列出欄位名。
| 方式 | 10,000 筆耗時 (估) | 網路往返 | 解析/優化次數 |
|---|---|---|---|
| 循環 10,000 次 INSERT | ~30-60 秒 | 10,000 | 10,000 |
| 單次 INSERT 10,000 組 VALUES | ~0.5-2 秒 | 1 | 1 |
| LOAD DATA INFILE | ~0.1-0.5 秒 | 1 | 1 |
批次 INSERT 效能接近 LOAD DATA,且無需檔案 I/O 權限,是應用層批次寫入的首選。
INSERT 建議 500-2,000 筆,避免封包過大超過 max_allowed_packet (預設 4MB/64MB)-- Python + PyMySQL 範例
sql = "INSERT INTO users (name, age) VALUES " + ", ".join(["(%s, %s)"] * len(data))
cursor.execute(sql, [val for row in data for val in row])
INSERT INTO users (id, name, age) VALUES
(1, 'Alice', 25), (2, 'Bob', 30)
ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age)
SET autocommit = 0;
INSERT ... VALUES (...), (...), ...; -- 批次 1
INSERT ... VALUES (...), (...), ...; -- 批次 2
COMMIT;
Got a packet bigger than 'max_allowed_packet' bytes,調大參數或減少批次筆數BEFORE/AFTER INSERT 觸發器ON DUPLICATE KEY UPDATE 或 IGNORE 處理MySQL 多值 INSERT 是應用層批次寫入的標準高效方案:語法簡單、相容性好、效能提升 10-100 倍。配合適當批次大小、參數化查詢與事務控制,是處理大量資料匯入、日誌批次寫入、ETL 載入的必備技能。