How to run 1000s insert queries with mysqli?

  1. Comments

The key to have your multiple inserts fast and reliable is to use transactions and prepared statements.

A transaction will ensure that all operations either complete in their entirety or have no effect whatsoever. Another important advantage of using a transaction is that it makes multiple inserts faster, eliminating all possible delays that could be caused by separate query execution.

A prepared statement will add some little boost to the speed and of course ensure that there will be no errors or injections.

First of all, make sure you have a proper mysqli connection, which, among other things, tells mysqli to throw an exception in case of error. Then just prepare your query, start a transaction, execute the queries and commit the transaction

include 'mysqli.php';

$stmt $mysqli->prepare("INSERT INTO table SET col1 = ?, col2 = ?");

$mysqli->begin_transaction();
foreach (
$data as $row) {
    
$stmt->bind_param("ss"$row['col1'], $row['col2']);
    
$stmt->execute();
}
$mysqli->commit();

note that it could be not just a single query but multiple different queries but the principle remains the same: just wrap all queries in a transaction and use prepared statements.


Related articles: