在 mysqli 中插入多个值的最佳方法? [英] Best way to INSERT many values in mysqli?

问题描述

我正在寻找一种 SQL 注入安全技术，以使用 PHP 和 MySQLi 一次插入大量行(大约 2000 年).
我有一个数组，其中包含必须包含的所有值.目前我正在这样做:

I'm looking for a SQL-injection-secure technique to insert a lot of rows (ca. 2000) at once with PHP and MySQLi.
I have an array with all the values that have to be include. Currently I'm doing that:

<?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();
}
?>

我尝试了 call_user_func_array()，但它导致堆栈溢出.

I tried call_user_func_array(), but it caused a stackoverflow.

有什么更快的方法可以做到这一点(比如一次插入它们?)，但仍然可以防止 SQL 注入(比如准备好的语句)和计算溢出?
谢谢！

What is faster method to do this (like inserting them all at once?), but still secure against SQL-injections (like a prepared statement) and stackoverflows?
Thank you!

推荐答案

通过将插入内容放入事务中，您应该能够大大提高速度.您还可以将准备和绑定语句移到循环之外.

You should be able to greatly increase the speed by putting your inserts inside a transaction. You can also move your prepare and bind statements outside of your 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");

---

我在我的网络服务器上用 10,000 次迭代测试了这段代码.

无交易:226 秒.有交易:2 秒.或者两个数量级的速度提升，至少在那个测试中是这样.

Without transaction: 226 seconds. With transaction: 2 seconds. Or a two order of magnitude speed increase, at least for that test.

这篇关于在 mysqli 中插入多个值的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！