Use mysqli_stmt::prepare to achieve batch update

gitbox 2025-05-28

Batch updates are a common requirement when using PHP for database operations. Direct execution of multiple separate UPDATE statements is not only inefficient, but also increases the load on the database. Using the mysqli_stmt::prepare method, preprocessing statements and parameter binding can significantly improve the performance and security of batch updates.

This article will introduce in detail how to use mysqli_stmt::prepare to implement batch update operations and give specific efficiency optimization suggestions.

1. Why choose mysqli_stmt::prepare to achieve batch update?

Preprocessing statements are highly secure Preprocessing statements can avoid the risk of SQL injection, and separate user input from SQL statements by binding parameters.
Improved execution efficiency The same preprocessing statement only needs to be compiled once, executed multiple times, and only parameters are replaced, reducing the database parsing time
Concise code and easy to maintain Clear structure, suitable for batch operation logic packaging and multiplexing

2. Basic ideas for batch updates

Suppose we have a user table that needs to update their status in batches based on user ID. The traditional practice is:

 UPDATE users SET status = 'active' WHERE id = 1;
UPDATE users SET status = 'inactive' WHERE id = 2;
...

However, the overhead of executing multiple SQL statements is high, and the improvement solution is:

Precompiled UPDATE statement templates.
Bind different parameters in turn to execute.

3. Sample code analysis

The following is a demonstration code to implement batch updates using mysqli_stmt::prepare :

 <?php
$mysqli = new mysqli("gitbox.net", "username", "password", "database");

// Check the connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Preprocessing SQL，Set placeholders
$sql = "UPDATE users SET status = ? WHERE id = ?";
$stmt = $mysqli->prepare($sql);

if (!$stmt) {
    die("Preprocessing失败: " . $mysqli->error);
}

// Simulate batch update data
$updateData = [
    ['status' => 'active', 'id' => 1],
    ['status' => 'inactive', 'id' => 2],
    ['status' => 'pending', 'id' => 3],
];

// Bind parameters
foreach ($updateData as $data) {
    // 'si' Indicates that the first parameter is a string，The second parameter is an integer
    $stmt->bind_param('si', $data['status'], $data['id']);
    $stmt->execute();
    
    if ($stmt->error) {
        echo "Execution failed: " . $stmt->error . "\n";
    }
}

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

4. Efficiency improvement skills

Transaction processing Put multiple update statements into the transaction to reduce the number of transaction commits

 $mysqli->begin_transaction();

foreach ($updateData as $data) {
    $stmt->bind_param('si', $data['status'], $data['id']);
    $stmt->execute();
}

$mysqli->commit();

Batch update merge If the update fields are the same and the data volume is large, you can use the CASE WHEN statement to update multiple records at once.

 $ids = array_column($updateData, 'id');
$ids_list = implode(',', $ids);

$sql = "UPDATE users SET status = CASE id ";
foreach ($updateData as $data) {
    $status = $mysqli->real_escape_string($data['status']);
    $sql .= "WHEN {$data['id']} THEN '{$status}' ";
}
$sql .= "END WHERE id IN ($ids_list)";

$mysqli->query($sql);

This method is suitable for large batch updates at one time, but loses the flexibility and security of preprocessing statements.

Prepare for adequate connections and resources Ensure that the database connection is properly configured to avoid performance bottlenecks due to resource bottlenecks

5. Summary

Using mysqli_stmt::prepare to achieve batch updates can not only ensure the security of operations, but also improve execution efficiency. Combining transaction management and reasonable SQL construction methods, performance can be further optimized and is suitable for most PHP+MySQL application scenarios.

With the above example code and techniques, you can effectively achieve efficient and secure batch update operations.

mysqli_stmt::prepare