Use stmt_init with bind_param to perform batch insertion operations

M66 2025-05-29

When processing large amounts of data insertion operations, it is less efficient to loop through INSERT statements. In order to improve performance, mysqli::stmt_init combined with bind_param() can be used to implement batch execution of preprocessed statements, which can not only improve execution efficiency but also prevent SQL injection risks. This article will explain how to perform this operation efficiently through examples.

1. Why use preprocessing statements?

The use of prepare + bind_param is safer than the traditional string splicing method, and is especially suitable for situations with more dynamic parameters. Its advantages include:

Avoid SQL injection;
Avoid repeated compilation of SQL to improve execution efficiency;
Good readability and clear structure.

2. The basic process of using mysqli::stmt_init and bind_param

We will take a simple user information table users as an example, and its structure is as follows:

 CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

Suppose we need to batch insert multiple user records, the following is the complete PHP implementation:

 <?php
// Database connection configuration
$mysqli = new mysqli("localhost", "db_user", "db_pass", "db_name");

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

// Initialize statement object
$stmt = $mysqli->stmt_init();

// Prepare SQL Statement
$sql = "INSERT INTO users (username, email) VALUES (?, ?)";
if (!$stmt->prepare($sql)) {
    die("Preprocessing failed: " . $stmt->error);
}

// Bind parameters（Note that variables are referenced）
$stmt->bind_param("ss", $username, $email);

// Simulate the data to be inserted
$data = [
    ["alice", "alice@m66.net"],
    ["bob", "bob@m66.net"],
    ["charlie", "charlie@m66.net"]
];

// Perform batch insertion
foreach ($data as $row) {
    $username = $row[0];
    $email = $row[1];
    if (!$stmt->execute()) {
        echo "Insert failed: " . $stmt->error . "<br>";
    }
}

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

3. Analysis of key points

1. The combination of stmt_init and prepare

Although you can directly use $mysqli->prepare() abbreviation, use stmt_init to explicitly express the process, which is more suitable for complex or large projects.

2. Parameter binding must be completed outside the loop

bind_param binds a reference to the variable, so the value of the variable can be changed in the loop, and there is no need to rebind each time.

3. Error handling

Checking the return value after each execution() can help us quickly locate which record failed to insert.

4. Performance optimization suggestions

Batch submission: For large amounts of data insertion, it can be processed in batches (such as one group per 1,000 items);
Turn off automatic commit ( $mysqli->autocommit(false) ), and submit transactions uniformly after a batch is processed;
Compare the test performance differences using the multi-valued insertion syntax (INSERT INTO ... VALUES (...), (...), ...).

V. Conclusion

Using mysqli::stmt_init and bind_param() can not only improve the efficiency of batch insertion, but also enhance the security and maintainability of the application. This method is particularly recommended when dealing with scenarios such as large amounts of user data or logging.

mysqli::stmt_init