How Can I Efficiently and Securely Insert Multiple Rows into a MySQL Database?

Efficient and Secure Insertion of Multiple Rows Using MySQLi

Inserting a large number of rows into a MySQL database requires both efficiency and security. Using a naive approach, as shown in the provided PHP code, can lead to performance issues and SQL injection vulnerabilities.

To address these concerns, a more optimal solution is to perform the inserts within a transaction and move the statement preparation and binding outside the loop:

$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 technique has been tested and shown to significantly improve performance. Specifically, inserting 10,000 records with the original approach took 226 seconds, while the optimized method completed the task in just 2 seconds. This represents a two order of magnitude increase in efficiency.

By utilizing transactions, you can insert multiple rows at once while maintaining data integrity and ensuring protection against SQL injections. This approach provides both speed and security, making it the preferred method for efficiently inserting large volumes of data into a MySQL database.

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