PHP MySQL 批量插入數據的三種高效方案：mysqli_multi_query、PDO 事務與預處理語句

場景與挑戰

需要一次性寫入成百上千條記錄時，逐條 INSERT 會產生大量網絡往返，效率極低。本文介紹三種原生擴展支援的批量寫入方案，並分析適用場景。

方案一：mysqli_multi_query() - 多語句單次發送

將多條 SQL 用分號拼接為單一字符串，一次發送給 MySQL 服務器解析執行。

面向對象風格

<?php
$conn = new mysqli('localhost', 'user', 'pass', 'db');
if ($conn->connect_error) die('連接失敗: ' . $conn->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 ($conn->multi_query($sql) === TRUE) {
    echo '批量插入成功';
} else {
    echo '錯誤: ' . $conn->error;
}
$conn->close();
?>

面向過程風格

<?php
$conn = mysqli_connect('localhost', 'user', 'pass', 'db');
if (!$conn) die('連接失敗: ' . mysqli_connect_error());

$sql = "INSERT INTO MyGuests ... ; INSERT INTO MyGuests ... ; INSERT INTO MyGuests ... ";

if (mysqli_multi_query($conn, $sql)) {
    echo '成功';
} else {
    echo '錯誤: ' . mysqli_error($conn);
}
mysqli_close($conn);
?>

注意：multi_query 若包含 SELECT 需用 next_result() 遍歷結果集；大批量 SQL 字符串可能受 max_allowed_packet 限制。

方案二：PDO 事務 - ACID 保證與異構支援

利用事務將多條 exec() 包裹，要么全成功要么全回滾，適合對數據一致性要求高的場景。

<?php
try {
    $conn = new PDO('mysql:host=localhost;dbname=db', 'user', 'pass');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $conn->beginTransaction();
    $conn->exec("INSERT INTO MyGuests ... VALUES ('John', 'Doe', 'john@example.com')");
    $conn->exec("INSERT INTO MyGuests ... VALUES ('Mary', 'Moe', 'mary@example.com')");
    $conn->exec("INSERT INTO MyGuests ... VALUES ('Julie', 'Dooley', 'julie@example.com')");
    $conn->commit();
    echo '事務提交成功';
} catch (PDOException $e) {
    $conn->rollBack();
    echo '回滾: ' . $e->getMessage();
}
$conn = null;
?>

方案三：預處理語句 + 批量綁定執行 - 性能與安全最佳平衡

SQL 解析一次，多次綁定參數執行，有效防止 SQL 注入，執行計劃可復用，是生產環境推薦方案。

<?php
$conn = new mysqli('localhost', 'user', 'pass', 'db');
if ($conn->connect_error) die('連接失敗');

$stmt = $conn->prepare('INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)');
if (!$stmt) die('預處理失敗: ' . $conn->error);

// 綁定參數：'sss' 表示三個字符串類型
$stmt->bind_param('sss', $firstname, $lastname, $email);

// 批量數據
$data = [
    ['John', 'Doe', 'john@example.com'],
    ['Mary', 'Moe', 'mary@example.com'],
    ['Julie', 'Dooley', 'julie@example.com'],
];

foreach ($data as $row) {
    [$firstname, $lastname, $email] = $row;
    $stmt->execute();
}

echo '預處理批量插入完成';
$stmt->close();
$conn->close();
?>

mysqli_stmt_bind_param 類型對照表

性能對比與選擇建議

超大批量（萬級以上）優化技巧

• LOAD DATA INFILE：MySQL 原生批量導入工具，速度最快，需文件權限。
• 分塊提交：每 500-1000 條提交一次事務，避免長事務鎖表、日誌膨脹。
• 關閉索引/外鍵：導入前 ALTER TABLE ... DISABLE KEYS，導入後重建。
• 使用多值 INSERT 語法：INSERT INTO t (a,b) VALUES (1,2),(3,4),(5,6) 单条语句插入多行，配合預處理效果更佳。

掌握這三種核心方案與優化策略，能應對絕大多數 PHP 批量寫入場景。