PHP mysqli 預處理批量增刪改：提高效能、防注入的正確姿勢

PHP mysqli 預處理批量增刪改：提高效能、防注入的正確姿勢

MySQLi 預處理 (prepare + bind_param + execute) 讓 SQL 模板只編譯一次，批量操作時僅重複綁定參數執行，大幅降低解析開銷並徹底防 SQL 注入。本文示範批量新增、修改、刪除完整流程。

核心流程

1. $stmt = $mysqli->prepare($sql)：SQL 模板含 ? 佔位符，返回 mysqli_stmt 物件。
2. $stmt->bind_param($types, ...$vars)：綁定參數類型與變量引用。類型碼：i(int)、d(double)、s(string)、b(blob)。
3. $stmt->execute()：執行，返回 bool。
4. 循環修改綁定變量值，再次 execute()，無需重新 prepare/bind。

批量新增範例

<?php
$mysqli = new mysqli('localhost', 'root', '123456', 'test');
if ($mysqli->connect_errno) die('Connect Error: ' . $mysqli->connect_error);
$mysqli->set_charset('utf8mb4');

$sql = 'INSERT INTO `account` (`id`, `balance`) VALUES (?, ?)';
$stmt = $mysqli->prepare($sql);
if (!$stmt) die('Prepare failed: ' . $mysqli->error);

// 綁定參數：id 為自增可傳 null，balance 為 double
$id = null;
$balance = 0.0;
$stmt->bind_param('id', $id, $balance); // 類型串 "id" = integer + double

$data = [100.5, 400.3, 250.0, 999.9];
foreach ($data as $bal) {
    $balance = $bal;
    if (!$stmt->execute()) {
        echo "Insert failed (balance=$balance): " . $stmt->error . PHP_EOL;
    } else {
        echo "Inserted ID: " . $stmt->insert_id . " balance=$balance" . PHP_EOL;
    }
}

$stmt->close();
$mysqli->close();

批量修改 / 刪除範例

<?php
// 修改
$updSql = 'UPDATE `account` SET `balance` = ? WHERE `id` = ?';
$updStmt = $mysqli->prepare($updSql);
$updStmt->bind_param('di', $balance, $id);

$updates = [['id'=>1, 'bal'=>200.0], ['id'=>2, 'bal'=>500.0]];
foreach ($updates as $u) {
    $balance = $u['bal'];
    $id = $u['id'];
    $updStmt->execute(); // affected_rows 可判斷是否命中
}

// 刪除
$delSql = 'DELETE FROM `account` WHERE `id` = ?';
$delStmt = $mysqli->prepare($delSql);
$delStmt->bind_param('i', $id);
$delIds = [3, 4];
foreach ($delIds as $id) {
    $delStmt->execute();
}

關鍵優勢

常見坑點

• bind_param **必須傳變量引用**，不能直接傳值或表達式。循環中修改同一變量即可。
• 類型串長度必須與 ? 數量一致，否則 Number of variables doesn't match。
• execute() 返回 bool，insert_id、affected_rows、num_rows 屬於 $stmt 而非 $mysqli。
• 大批量 (＞1 萬) 建議分批提交事務，避免長事務鎖表。

PDO 對比

PDO 同樣支援預處理，API 更統一 (支援命名佔位符 :name)、支援多種資料庫。新專案推薦優先使用 PDO。