How Can I Efficiently and Securely Insert Many Rows into MySQLi Using PHP?

Efficient and Secure Multi-Row Insertion in MySQLi

In PHP development, inserting numerous rows into a MySQL database efficiently and securely can be a challenge. To address this, it's essential to employ techniques that prevent SQL injection while maintaining performance.

One common approach is using prepared statements with parameter binding. However, in cases where a large number of rows (e.g., 2000) need to be inserted, this approach can be slow.

A more efficient solution is to leverage database transactions. Transactions allow you to group multiple operations together and execute them as a single atomic unit. By initiating a transaction, you can insert multiple rows using a single prepared statement, reducing the overhead associated with executing separate inserts.

To illustrate this, here's an optimized code snippet:

$array = array("array", "with", "about", "2000", "values");
$query = "INSERT INTO table (link) VALUES (?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $one);

$mysqli->query("START TRANSACTION");
foreach ($array as $one) {
    $stmt->execute();
}
$stmt->close();
$mysqli->query("COMMIT");

This code initializes a transaction, prepares the insert statement, and binds the parameter. Then, it iterates through the array of values, executing the insert statement for each value within the transaction. Finally, it commits the transaction, finalizing all the insertions.

Compared to the original method, this approach significantly improves performance. In a test with 10,000 iterations, the transaction-based insertion took only 2 seconds, while the original approach took 226 seconds. That's a massive two-order-of-magnitude speed increase.

By leveraging transactions and prepared statements, you can efficiently and securely insert multiple rows into a MySQL database in PHP, ensuring both data integrity and performance optimization.

The above is the detailed content of How Can I Efficiently and Securely Insert Many Rows into MySQLi Using PHP?. For more information, please follow other related articles on the PHP Chinese website!