php 在 mysqli 中插入多个值的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15149157/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to INSERT many values in mysqli?
提问by Roman Holzner
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:
我正在寻找一种 SQL 注入安全技术来使用 PHP 和 MySQLi 一次插入大量行（大约 2000 年）。
我有一个包含所有必须包含的值的数组。目前我正在这样做：
<?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 caused a stackoverflow.
我尝试了call_user_func_array()，但它导致了堆栈溢出。
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!
什么是更快的方法来做到这一点（比如一次插入它们？），但仍然可以防止 SQL 注入（比如准备好的语句）和计算溢出？
谢谢！
回答by Dan Metheus
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");
Edit:
编辑：
I tested this code with 10,000 iterations on my web server.
我在我的网络服务器上用 10,000 次迭代测试了这段代码。
Without transaction: 226 seconds.With transaction: 2 seconds.Or a two order of magnitude speed increase, at least for that test.
无交易：226 seconds.有交易：2 seconds.或 a two order of magnitude speed increase，至少对于该测试而言。
回答by Explosion Pills
Trying this again, I don't see why your original code won't work with minor modifications:
再试一次，我不明白为什么您的原始代码在稍作修改后不起作用：
$query = "INSERT INTO table (link) VALUES (?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $one);
foreach ($array as $one) {
$stmt->execute();
}
$stmt->close();
回答by Mark Ormston
Yes, you can build a single big query manually, with something like:
是的，您可以手动构建单个大查询，例如：
$query = "";
foreach ($array as $curvalue) {
if ($query)
$query .= ",";
$query .= "('" . $mysqli->real_escape_string($curvalue) . "')";
}
if ($query) {
$query = "INSERT INTO table (link) VALUES " . $query;
$mysqli->query($query);
}
回答by G. Kashtanov
You should first convert your array into a string. Given that it is an array of strings (not a two-dimentional array), you can use the implodefunction.
您应该首先将数组转换为字符串。鉴于它是一个字符串数组（不是二维数组），您可以使用implode函数。
Please be aware that each value should be enclosed into parenthesis and properly escaped to ensure a correct INSERTstatement and to avoid the risk of an SQL injection. For proper escaping you can use the quotemethod of the PDOConnection-- assuming you're connecting to MySQL through PDO. To perform this operation on every entry of your array, you can use array_map.
请注意，每个值都应包含在括号中并正确转义，以确保INSERT语句正确并避免 SQL 注入的风险。为了正确转义，您可以使用 -- 的引用方法，PDOConnection假设您通过 PDO 连接到 MySQL。要对数组的每个条目执行此操作，您可以使用array_map。
After escaping each value and imploding them into a single string, you need to put them into the INSERTstatement. This can be done with sprintf.
在转义每个值并将它们内爆为单个字符串后，您需要将它们放入INSERT语句中。这可以通过sprintf来完成。
Example:
例子：
<?php
$connection = new PDO(/*...*/);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dataToBeSaved = [
'some',
'data',
'with "quotes"',
'and statements\'); DROP DATABASE facebook_main; --'
];
$connection->query(
sprintf(
'INSERT INTO table (link) VALUES %s',
implode(',',
// for each entry of the array
array_map(function($entry) use ($connection) {
// escape it and wrap it in parenthesis
return sprintf('(%s)', $connection->quote($entry));
}, $dataToBeSaved)
)
)
);
Note: depending on the amount of records you're willing to insert into the database, you may want to split them into several INSERTstatements.
注意：根据您愿意插入数据库的记录数量，您可能希望将它们拆分为多个INSERT语句。
