MySQL 單條語句批量插入多條數據：原理、寫法與效能實測

MySQL 單條語句批量插入多條數據：原理、寫法與效能實測

使用 INSERT INTO ... VALUES (...), (...), ... 多值語法，可將 N 次網絡往返合併為 1 次，大幅提升批量寫入吞吐。本文對比循環插入與批量插入的代碼寫法、效能數據、事務特性與最佳實踐。

一、語法原理

INSERT INTO `table` (col1, col2, col3)
VALUES
  ('val1_1', 'val1_2', 'val1_3'),
  ('val2_1', 'val2_2', 'val2_3'),
  ...
  ('valN_1', 'valN_2', 'valN_3');

• 單條 SQL，單次網絡往返，單次解析優化
• 隱式事務：要么全成功，要么全失敗 (自動提交模式下)
• 受 max_allowed_packet 限制，超大批次需分片

二、代碼寫法對比 (PHP 範例)

❌ 循環插入 (低效)

$b = 14;
for ($a = 0; $a < 100; $a++) {
    $sql = "INSERT INTO roles (uid, rid) VALUES ($a, $b)";
    mysql_query($sql);  // 100 次網絡往返、解析、執行
}

✅ 批量插入 (高效)

$b = 14;
$values = [];
for ($a = 0; $a < 100; $a++) {
    $values[] = "($a, $b)";
}
$sql = "INSERT INTO roles (uid, rid) VALUES " . implode(',', $values);
mysql_query($sql);  // 1 次網絡往返

✅ 更優雅：PDO 預處理 + 批量綁定

$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO roles (uid, rid) VALUES (?, ?)");
for ($a = 0; $a < 100; $a++) {
    $stmt->execute([$a, 14]);
}
$pdo->commit();  // 顯式事務，可控回滾

三、效能實測數據 (30 條記錄對比)

數據量越大、網絡延遲越高，優勢越明顯。百萬級導入建議分批 (每批 1000-5000 行) 配合 LOAD DATA INFILE。

四、關鍵配置與限制

五、最佳實踐清單

1. 分批提交：每 1000-5000 行一批，避免超大事務阻塞 Purge、鎖升級、日志爆增
2. 預處理語句：防注入、複用執行計劃、支援批量綁定參數
3. 關閉自動提交：SET autocommit=0，批次結束 COMMIT
4. 主鍵順序插入：InnoDB 聚簇索引按主鍵順序寫入最快，避免頁分裂
5. 禁用二級索引 (大量導入)：導入前 DROP INDEX，導入後 CREATE INDEX
6. 使用 LOAD DATA INFILE：極速導入 CSV/TSV，比 INSERT 快 10-20 倍

六、常見錯誤

• 單條 SQL 超過 max_allowed_packet → Packet too large 錯誤
• 批次過大導致主從延遲、Replication Lag 飆升
• 未處理重複鍵衝突 → 整批失敗，建議 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE

批量插入是提升寫入吞吐的「銀彈」。掌握多值語法、分批策略、事務邊界與配置調優，讓數據導入從小時級縮短至分鐘級。