php Mysqli 多行插入，简单的多行插入查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19512498/
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
Mysqli multiple row insert, simple multi insert query
提问by user1675155
How do I insert this query with mysqli?...
如何使用 mysqli 插入此查询？...
INSERT INTO table (field1, field2, field3) VALUES ('value', 'value', 'value'), ('value', 'value', 'value'), ('value', 'value', 'value');
Normally in mysql this query is straight forward and will insert 3 rows, how do I do this in mysqli without using a prepared statement or maybe using a prepared statement but without getting too complicated?. I just wish to know if there is a way to execute such query without doing extra funky stuff in PHP.
通常在 mysql 这个查询是直接的并且会插入 3 行，我如何在 mysqli 中做到这一点而不使用准备好的语句或者使用准备好的语句但不会太复杂？我只想知道是否有一种方法可以执行这样的查询，而无需在 PHP 中做额外的时髦事情。
In essence, I have some extracted data that has around 10 rows per insert (and also needs multiple inserts in addition to having multiple rows), and this is what I need it for. I only wish to do this with a query as I have normally done it with mysql, and not add multiple insert as one per each row.
从本质上讲，我有一些提取的数据，每个插入大约有 10 行（除了多行之外还需要多个插入），这就是我需要的。我只想用一个查询来做到这一点，就像我通常用 mysql 做的那样，而不是每行添加多个插入。
回答by fyo
The mysqli class provides a number of different ways of accomplishing your inserts, each with its own benefits. Certainly, one of them should fit your needs.
mysqli 类提供了许多不同的方法来完成插入，每种方法都有自己的好处。当然，其中之一应该适合您的需求。
The following examples assume that your unspecified "extracted data" is stored in an array of arrays: $bigArray[0...datasetsize][0...2].
以下示例假定您未指定的“提取数据”存储在数组数组中：$bigArray[0...datasetsize][0...2]。
The mysqli database is assumed to be $db.
mysqli 数据库假定为 $db。
Method 1 - Old School
方法 1 - 老派
You can do it straight forward like you are used to by simply building your query string and querying the database with it. Inserts are bundled 10 at a time, as you specified. The following code shows one such bundle and is trivially extended to the whole data set (bigArray). The data should probably be escaped using mysqli::escape_string (not done here).
您可以像以往一样直接完成，只需构建查询字符串并使用它查询数据库即可。根据您的指定，一次捆绑 10 个插入物。下面的代码显示了一个这样的包，并简单地扩展到整个数据集 (bigArray)。数据可能应该使用 mysqli::escape_string 进行转义（此处未完成）。
The data to be inserted is assumed to be integers in all examples.
在所有示例中，要插入的数据都假定为整数。
$sql = "INSERT INTO testTable (fieldA, fieldB, fieldC) VALUES ";
for ($i = 0; $i < 10; ++$i)
{
if ($i > 0) $sql .= ", ";
$sql .= "({$bigArray[$i][0]}),({$bigArray[$i][1]}),({$bigArray[$i][2]})";
}
$db->query($sql);
Method 2 - As Simple As Possible
方法 2 - 尽可能简单
If you want to use a prepared statement and parameter binding, a first effort might look like the following. While not optimal, the statement is only prepared once. However, the variables are bound for each insert, which is wasteful (but simple). Since inserts are not bundled, the example loops over 10.
如果要使用准备好的语句和参数绑定，第一个尝试可能如下所示。虽然不是最优的，但语句只准备一次。但是，每个插入都绑定了变量，这很浪费（但很简单）。由于未捆绑插入，因此示例循环超过 10 个。
$statement = $db->prepare("INSERT INTO testTable (fieldA, fieldB, fieldC) VALUES (?,?,?)");
for ($i = 0; $i < 10; ++$i)
{
$statement->bind_param("iii",$bigArray[$i][0],$bigArray[$i][1],$bigArray[$i][2]);
$statement->execute();
}
Method 3 - Optimized
方法 3 - 优化
Prepared statements and multiple inserts combined enable performance which is nearly identical to the raw insert queries of Method 1. Actual results will vary depending on your setup, but a quick test on my system with both a local and a remote database showed performance a few percentage points fasterwith the optimized method, increasing a few points more if data in Method 1 needs to be escaped.
准备好的语句和多个插入组合实现的性能几乎与方法 1 的原始插入查询相同。实际结果将因您的设置而异，但在我的系统上使用本地和远程数据库进行的快速测试显示性能只有几个百分比使用优化的方法点数更快，如果方法 1 中的数据需要转义，则增加几个点数。
The following uses call_user_func_array, but you could avoid that if you know how many inserts you want to bundle each time and build call bind_param directly. That would further increase performance slightly.
以下使用 call_user_func_array，但如果您知道每次要捆绑多少个插入并直接构建 call bind_param，则可以避免这种情况。这将进一步略微提高性能。
For clarity, this examples includes the outer loop and assumes 10k total lines to be inserted (i.e. bigArray[0..9999][0..2]).
为清楚起见，此示例包括外循环并假设总共要插入 10k 行（即 bigArray[0..9999][0..2]）。
$sql = "INSERT INTO testTable (fieldA,fieldB,fieldC) VALUES (?,?,?)".str_repeat(",(?,?,?)",9);
$statement = $db->prepare($sql);
// This is the type string used by statement::bind_param.
// Example assumes all INTs.
$types = (array)str_repeat("i",30);
$values = array_fill(0,30,0); // A bit of unneeded variable init.
// * See notes following code snippet on why the intermediate array is used.
$intermediate = array();
for ($n = 0; $n < 30; ++$n)
{
$intermediate[$n] = &$values[$n];
}
call_user_func_array(array(&$statement, "bind_param"), array_merge($types,$f));
for ($j = 0; $j < 1000; ++$j)
{
for ($i = 0; $i < 10; ++$i)
{
$values[$i*3] = $bigArray[$i][0];
$values[$i*3+1] = $bigArray[$i][1];
$values[$i*3+2] = $bigArray[$i][2];
}
$statement->execute();
}
// call_user_func_array with bind_param requires the values be
// passed by reference which is evaluated only on the initial
// call. Using $values[...] = &$bigArray[...] below won't work
// and an intermediate array referencing $values is used. This
// bit of "extra funky stuff" can be avoided at a very slight
// performance penalty by setting $values[...] = $bigArray[...]
// AND EVALUATING EACH TIME (move call_user_func_array
// inside the outer loop, i.e. right above $statement->execute()).
回答by Your Common Sense
Mysqli is not a database of it's own, but just a set of functions to send your query in old mysql.
Mysqli 不是它自己的数据库，而只是在旧 mysql 中发送查询的一组函数。
So, using mysqli you can run any mysql query.
因此，使用 mysqli 您可以运行任何 mysql 查询。
However, in case of dynamically supplied values you cannot avoid "extra funky stuff in PHP" as you are supposed to use prepared statementsfor that. And, unfortunately, raw mysqli is not that easy with them.
但是，在动态提供的值的情况下，您无法避免“PHP 中的额外时髦内容”，因为您应该为此使用准备好的语句。而且，不幸的是，原始 mysqli 对它们来说并不容易。
So, to perform such insert you will need to create a query with placeholders first
因此，要执行此类插入，您需要先创建一个带有占位符的查询
INSERT INTO table (field1,field2,field3) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?);
INSERT INTO table (field1,field2,field3) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?);
then bind all the values using call_user_func_array()
and finally execute;
然后使用call_user_func_array()
和 finally绑定所有值execute；
