批次插入是資料庫操作常見需求，正確策略能大幅提升寫入效能。本文詳細比較三種批次插入方式：多條 SQL 字串、預處理語句迴圈、交易包裹，並提供 MySQLi (OO/程序式) 與 PDO 完整實作範例、效能基準建議與最佳實務。
| 策略 | 原理 | 優點 | 缺點 | 適用場景 |
|---|
| 多條 SQL 字串 (multi_query) | 單次傳送多條 SQL，以分號分隔 | 代碼簡單、單次網路往返 | 難以參數化、注入風險、錯誤定位困難、大小受限 | 少量靜態資料、維舊系統 |
| 預處理語句迴圈 | 準備一次，綁定參數重複執行 | 防注入、計畫重用、參數型別安全、記憶體穩定 | 多次網路往返 (可配合交易優化) | 推薦標準做法 |
| 單條 SQL 多值列 (VALUES..., (...)) | 單條 INSERT 多組值 | 極高效能、單次網路往返、原子性 | SQL 長度限制 (max_allowed_packet)、建構複雜、錯誤回滾全失敗 | 大量資料 (千~百萬筆)、效能關鍵 |
方式一：mysqli_multi_query 多條 SQL (簡單但有風險)
MySQLi 物件導向
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
if ($mysqli->connect_error) die("連線失敗: " . $mysqli->connect_error);
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com');";
if ($mysqli->multi_query($sql) === true) {
echo "批次插入成功";
} else {
echo "錯誤: " . $mysqli->error;
}
// 處理多重結果集 (若有 SELECT)
do {
if ($result = $mysqli->store_result()) {
$result->free();
}
} while ($mysqli->next_result());
$mysqli->close();
MySQLi 程序式
$conn = mysqli_connect('localhost', 'user', 'pass', 'db');
if (!$conn) die("連線失敗: " . mysqli_connect_error());
$sql = "INSERT ...; INSERT ...; INSERT ...";
if (mysqli_multi_query($conn, $sql)) {
echo "成功";
do {
if ($res = mysqli_store_result($conn)) mysqli_free_result($res);
} while (mysqli_next_result($conn));
} else {
echo "錯誤: " . mysqli_error($conn);
}
mysqli_close($conn);
PDO (使用交易模擬批次)
$pdo = new PDO("mysql:host=localhost;dbname=db", 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$pdo->beginTransaction();
$pdo->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')");
$pdo->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com')");
$pdo->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', 'julie@example.com')");
$pdo->commit();
echo "成功";
} catch (PDOException $e) {
$pdo->rollBack();
echo "失敗: " . $e->getMessage();
}
方式二：預處理語句迴圈 (標準推薦做法)
MySQLi 物件導向
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$stmt = $mysqli->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param('sss', $firstname, $lastname, $email);
$data = [
['John', 'Doe', 'john@example.com'],
['Mary', 'Moe', 'mary@example.com'],
['Julie', 'Dooley', 'julie@example.com']
];
$mysqli->begin_transaction(); // 包裹交易提升效能
foreach ($data as $row) {
[$firstname, $lastname, $email] = $row;
$stmt->execute();
}
$mysqli->commit();
$stmt->close();
$mysqli->close();
MySQLi 程序式 (mysqli_stmt_* 函數)
$conn = mysqli_connect('localhost', 'user', 'pass', 'db');
$stmt = mysqli_stmt_init($conn);
mysqli_stmt_prepare($stmt, "INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);
mysqli_begin_transaction($conn);
$data = [ /* ... */ ];
foreach ($data as $row) {
[$firstname, $lastname, $email] = $row;
mysqli_stmt_execute($stmt);
}
mysqli_commit($conn);
mysqli_stmt_close($stmt);
mysqli_close($conn);
PDO (bindParam 參考綁定)
$pdo = new PDO("mysql:host=localhost;dbname=db", 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:fn, :ln, :em)");
$stmt->bindParam(':fn', $firstname);
$stmt->bindParam(':ln', $lastname);
$stmt->bindParam(':em', $email);
$pdo->beginTransaction();
$data = [ /* ... */ ];
foreach ($data as $row) {
[$firstname, $lastname, $email] = $row;
$stmt->execute();
}
$pdo->commit();
方式三：單條 SQL 多值列 (最高效能)
function batchInsertMultiValues(PDO $pdo, string $table, array $columns, array $rows, int $batchSize = 1000): int {
$colList = '`' . implode('`, `', $columns) . '`';
$placeholders = '(' . implode(', ', array_fill(0, count($columns), '?')) . ')';
$totalInserted = 0;
$pdo->beginTransaction();
try {
foreach (array_chunk($rows, $batchSize) as $chunk) {
$valuePlaceholders = implode(', ', array_fill(0, count($chunk), $placeholders));
$sql = "INSERT INTO `$table` ($colList) VALUES $valuePlaceholders";
$stmt = $pdo->prepare($sql);
$paramIndex = 1;
foreach ($chunk as $row) {
foreach ($row as $value) {
$stmt->bindValue($paramIndex++, $value);
}
}
$stmt->execute();
$totalInserted += $stmt->rowCount();
}
$pdo->commit();
} catch (Exception $e) {
$pdo->rollBack();
throw $e;
}
return $totalInserted;
}
// 使用
$columns = ['firstname', 'lastname', 'email'];
$rows = [
['John', 'Doe', 'john@example.com'],
['Mary', 'Moe', 'mary@example.com'],
// ... 10000 筆
];
$inserted = batchInsertMultiValues($pdo, 'MyGuests', $columns, $rows, 500);
echo "插入 $inserted 筆資料";
效能基準參考 (相對比較)
| 資料量 | multi_query | 預處理迴圈+交易 | 多值列+交易 |
|---|
| 10 筆 | 基準 | ~1.2x | ~2x |
| 1,000 筆 | ~5x | ~10x | ~50x |
| 10,000 筆 | ~10x | ~30x | ~200x |
| 100,000 筆 | 失敗/極慢 | ~100x | ~500x+ |
註：實際倍數視網路延遲、資料庫配置、索引、欄位型別而異。多值列受 max_allowed_packet (預設 64MB) 限制，超大批次需分塊。
關鍵最佳實務
- 務必包裹交易：自動提交模式下每筆 INSERT 皆 fsync，交易僅提交時 fsync 一次
- 分塊處理大量資料：避免單條 SQL 過大、記憶體暴增、鎖定過久。建議 500-2000 筆/批次
- 預處理語句重用：準備一次，迴圈綁定執行
- 關閉自動提交：
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false) 或 $mysqli->autocommit(false)
- 錯誤處理完整性：try-catch 確保 rollBack、資源釋放
- 考慮 LOAD DATA INFILE：超大量 (百萬級) 純資料導入，CSV + LOAD DATA 最快
- 索引策略：大量寫入前暫時移除非必要索引，寫入後重建
- 主鍵衝突處理：
INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO 視業務需求選擇
常見錯誤
| 錯誤 | 對策 |
|---|
| max_allowed_packet 超限 | 縮減批次大小、增大 my.cnf 設定 |
| 交易鎖定過久導致死鎖 | 縮小批次、優化索引、避免長交易 |
| 記憶體耗盡 (PDO fetchAll) | 使用迴圈逐筆 bindValue、或生成器分塊 |
| multi_query 未處理所有結果集 | 必須迴圈 next_result/store_result/free |
| 自動遞增 ID 獲取錯誤 | 多值列僅回傳第一筆 ID，需自行計算或查詢 |
總結
批次插入選擇原則：少量/簡單 → 預處理迴圈+交易；大量/效能關鍵 → 多值列分塊+交易；極大量導入 → LOAD DATA INFILE。PDO 提供最統一、安全、現代的介面，搭配 ATTR_EMULATE_PREPARES=false 獲得真實預處理效能。掌握交易控制、分塊策略、錯誤處理，是構建高效資料寫入系統的核心能力。
- 上一篇文章： PHP MySQL 預處理語句完整指南：防注入、效能優化與 MySQLi/PDO 實作
- 下一篇文章： PHP MySQL 插入資料完整指南：INSERT 語法、MySQLi/PDO 實作與安全最佳實務
Copyright ©
程式碼之家 All Rights Reserved
