萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL 批次 INSERT 高效插入多筆資料:單一語句提升效能

MySQL 批次插入優化:一條 INSERT 取代多次呼叫,大幅降低伺服器負載

逐筆呼叫 INSERT 不僅程式碼冗長,更因每次都需經歷 SQL 解析、優化、執行的完整流程,造成資料庫伺服器不必要的 CPU 與網路開銷。MySQL 擴充標準 SQL,支援單一 INSERT 陳述式插入多筆資料,是批次寫入的標準高效做法。

核心語法:VALUES 後接多組值清單

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,00010,000
單次 INSERT 10,000 組 VALUES~0.5-2 秒11
LOAD DATA INFILE~0.1-0.5 秒11

批次 INSERT 效能接近 LOAD DATA,且無需檔案 I/O 權限,是應用層批次寫入的首選。

實戰最佳實踐

1. 批次大小控制

  • 單次 INSERT 建議 500-2,000 筆,避免封包過大超過 max_allowed_packet (預設 4MB/64MB)
  • 大量資料分批次提交,每批次在獨立事務中執行,平衡效能與回滾成本

2. 參數化查詢防注入

-- 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])

3. 結合 ON DUPLICATE KEY UPDATE 實現 Upsert 批次

INSERT INTO users (id, name, age) VALUES 
(1, 'Alice', 25), (2, 'Bob', 30)
ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age)

4. 關閉自動提交 + 手動事務

SET autocommit = 0;
INSERT ... VALUES (...), (...), ...; -- 批次 1
INSERT ... VALUES (...), (...), ...; -- 批次 2
COMMIT;

常見陷阱

  • 超過 max_allowed_packet:錯誤 Got a packet bigger than 'max_allowed_packet' bytes,調大參數或減少批次筆數
  • 觸發器逐行觸發:每筆資料仍會獨立觸發 BEFORE/AFTER INSERT 觸發器
  • 自增 ID 連續性:批次插入的自增值連續分配,但若發生重複鍵錯誤並回滾,ID 會留下間隙
  • 唯一索引衝突導致整批失敗:預設行為,需搭配 ON DUPLICATE KEY UPDATE 或 IGNORE 處理

總結

MySQL 多值 INSERT 是應用層批次寫入的標準高效方案:語法簡單、相容性好、效能提升 10-100 倍。配合適當批次大小、參數化查詢與事務控制,是處理大量資料匯入、日誌批次寫入、ETL 載入的必備技能。

copyright © 萬盛學電腦網 all rights reserved