What is the most efficient way to INSERT multiple values in mysqli?-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW)

What is the most efficient way to INSERT multiple values in mysqli?

P粉908138620 2023-10-21 14:39:48

399

I'm looking for a SQL injection safe technique to insert a large number of rows (~2000 rows) at once using PHP and MySQLi.

I have an array that contains all the values it must contain. Currently I'm doing this:

<?php
$array = array("array", "with", "about", "2000", "values");

foreach ($array as $one) 
{
    $query = "INSERT INTO table (link) VALUES ( ?)";
    $stmt = $mysqli->prepare($query);
    $stmt ->bind_param("s", $one);
    $stmt->execute();
    $stmt->close();
}
?>

I tried call_user_func_array() but it resulted in stack overflow.

What is a faster way to do this (like inserting them all at once?) but still prevent SQL injection (like prepared statements) and stack overflow?

P粉908138620

reply all(2)

P粉2504220452023-10-22 12:11:47 2 floor

Try again, I don't understand why your original code doesn't work after a slight modification:

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

foreach ($array as $one) {
    $stmt->execute();
}
$stmt->close();

Like +0

Add Reply

P粉7859577292023-10-22 09:31:38 1 floor

By putting your inserts into a transaction you should be able to speed things up a lot. You can also move prepare and bind statements 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");

I tested this code on my web server for 10,000 iterations.

No transaction: 226 seconds. Transaction time: 2 seconds. Or be two orders of magnitude faster, at least for this test.

Like +0

Add Reply