php 使用 PDO 准备语句插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19680494/
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
Insert multiple rows with PDO prepared statements
提问by ezero
I would like to know if it is possible to insert multiple rows using one prepared statement. Below is an example of how I would normally insert one row into the db:
我想知道是否可以使用一个准备好的语句插入多行。下面是我通常如何在数据库中插入一行的示例：
$params=array();
$params[':val1']="val1";
$params[':val2']="val2";
$params[':val3']="val3";
$sql="INSERT INTO table VALUES (col1,col2,col3) VALUES (:val1,:val2,:val3)";
$stmt=DB::getInstance()->prepare($sql);
$stmt->execute($params);
The values I want to insert will come from an array, for example: $values[0]['val1']; $values[0]['val2']; $values[0]['val3']; $values[1]['val1']; $values[2]['val2'];
我要插入的值将来自一个数组，例如： $values[0]['val1']; $values[0]['val2']; $values[0]['val3']; $values[1]['val1']; $values[2]['val2'];
etc.
等等。
This code may have to insert a few hundred rows at once, I thought about creating a loop to create hundreds of params and then append the sql statement with an extra insert for each row but I thought there must be a better way. What would be the best way to do this?
这段代码可能必须一次插入几百行，我想过创建一个循环来创建数百个参数，然后为每行附加一个额外的插入的 sql 语句，但我认为必须有更好的方法。什么是最好的方法来做到这一点？
回答by Justin Iurman
The first important thing to say is that you caninsert multiple rows thanks to only oneINSERTquery
首先要说的重要一点是，由于只有一个查询，您可以插入多行INSERT
INSERT INTO Table (col1, col2, col3)
VALUES ('abc', 'def', 'ghi'),
('abc', 'def', 'ghi'),
('abc', 'def', 'ghi'),
('abc', 'def', 'ghi'),
('abc', 'def', 'ghi')
-- and so on...
Once you know that, you're able to get a good solution with PDO (for instance).
You have to keep in mind that you want a complete prepareand executeprocess (in term of security, you have to pass each parameter separately).
一旦你知道这一点，你就可以得到一个很好的 PDO 解决方案（例如）。
你要记住，你想有一个完整prepare和execute过程（在安全期限，你必须单独通过每个参数）。
Let's say you have rows to insert structured as follow:
假设您要插入的行结构如下：
$rows = array(
array('abc', 'def', 'ghi'), // row 1 to insert
array('abc', 'def', 'ghi'), // row 2 to insert
array('abc', 'def', 'ghi') // row 3 to insert
// and so on ...
);
Your goal is to have this result as a prepared query:
您的目标是将此结果作为准备好的查询：
INSERT INTO Table (col1, col2, col3)
VALUES (?, ?, ?),
(?, ?, ?),
(?, ?, ?)
With its corresponding execute:
与其对应的执行：
PDOStatement::execute(array('abc', 'def', 'ghi', 'abc', 'def', 'ghi', 'abc', 'def', 'ghi'));
Well, you onlyhave to do it now:
好吧，你现在只需要这样做：
$rows = array(
array('abc', 'def', 'ghi'),
array('abc', 'def', 'ghi'),
array('abc', 'def', 'ghi')
);
$row_length = count($rows[0]);
$nb_rows = count($rows);
$length = $nb_rows * $row_length;
/* Fill in chunks with '?' and separate them by group of $row_length */
$args = implode(',', array_map(
function($el) { return '('.implode(',', $el).')'; },
array_chunk(array_fill(0, $length, '?'), $row_length)
));
$params = array();
foreach($rows as $row)
{
foreach($row as $value)
{
$params[] = $value;
}
}
$query = "INSERT INTO Table (col1, col2, col3) VALUES ".$args;
$stmt = DB::getInstance()->prepare($query);
$stmt->execute($params);
And... That's it!
还有……就是这样！
This way, each param is treated separately, which is what you want (security, security, security!) and all of it, in a dynamic way, with only one INSERTquery
这样，每个参数都被单独处理，这就是您想要的（安全性，安全性，安全性！）以及所有这些，以动态方式，只有一个INSERT查询
If you have too many rows to insert (see this), you should executeone by one
如果要插入的行太多（请参阅this），则应execute一一
$rows = array(
array('abc', 'def', 'ghi'), // row 1 to insert
array('abc', 'def', 'ghi'), // row 2 to insert
array('abc', 'def', 'ghi') // row 3 to insert
// and so on ...
);
$args = array_fill(0, count($rows[0]), '?');
$query = "INSERT INTO Table (col1, col2, col3) VALUES (".implode(',', $args).")";
$stmt = $pdo->prepare($query);
foreach ($rows as $row)
{
$stmt->execute($row);
}
回答by Bill Karwin
If you're only inserting a few hundred rows, I'd favor simpler code like the following. Prepare a single-row INSERT statement, and then loop over your data array, executing the prepared query once for each row.
如果你只插入几百行，我更喜欢像下面这样的更简单的代码。准备一个单行 INSERT 语句，然后遍历您的数据数组，为每一行执行一次准备好的查询。
$rows = array(
array('abc', 'def', 'ghi'), // row 1 to insert
array('abc', 'def', 'ghi'), // row 2 to insert
array('abc', 'def', 'ghi') // row 3 to insert
// and so on ...
);
$params = implode(",", array_fill(0, count($rows[0]), "?"));
$sql = "INSERT INTO mytable VALUES ($params)";
$stmt = $pdo->prepare($sql); // rely on exceptions for error detection
foreach ($rows as $row) {
$stmt->execute($row);
}
MySQL does support multi-row INSERT syntax of course, so you could try putting that together.
MySQL 当然支持多行 INSERT 语法，所以你可以尝试把它们放在一起。
$params = implode(",", array_fill(0, count($rows[0]), "?"));
$tuples = "(" . implode("),(", array_fill(0, count($rows), $params)) . ")";
$sql = "INSERT INTO mytable VALUES $tuples";
$values = call_user_func_array("array_merge", $rows);
$stmt = $pdo->prepare($sql);
$stmt->execute($values);
But if you try to create a single INSERT statement with as many tuples as the items in your data array, you might accidentally generate an SQL statement that is longer than the maximum packet length.
但是，如果您尝试使用与数据数组中的项一样多的元组创建单个 INSERT 语句，您可能会意外生成一个长度超过最大数据包长度的 SQL 语句。
If you have thousands of rows, enough so that executing a prepared statement one row at a time is too much overhead, you should use LOAD DATA INFILE.
如果您有数千行，足以以至于一次执行一行准备好的语句开销太大，您应该使用LOAD DATA INFILE。
回答by Lucas Kahlert
If your table is transactional (for example an InnoDB), you can use a Transactionto speed up your insertions. A transaction has also the advantage of roll backs.
如果您的表是事务性的（例如 InnoDB），您可以使用事务来加速插入。事务还具有回滚的优势。
$pdo = DB::getInstance();
$stmt = $pdo->prepare('INSERT INTO table VALUES (col1, col2, col3) VALUES (:val1, :val2, :val3)');
$pdo->beginTransaction();
// The queries are not executed yet, but pushed to a transaction "stack"
foreach ($values as $value) {
$stmt->execute([
':val1' => $value['val1'],
':val2' => $value['val2'],
':val3' => $value['val3'],
]);
}
// Executes all the queries "at once"
$pdo->commit();
回答by Asenar
To keep your code, you have to make a loop for executing all insertions you need :
为了保留您的代码，您必须创建一个循环来执行您需要的所有插入：
$array_params = array();
$params[':val1']="val1 1";
$params[':val2']="val1 2";
$params[':val3']="val1 3";
$array_params[] = $params;
$params[':val1']="val2 1";
$params[':val2']="val2 2";
$params[':val3']="val2 3";
$array_params[] = $params;
$sql="INSERT INTO table (col1,col2,col3) VALUES (:val1,:val2,:val3)";
$stmt=DB::getInstance()->prepare($sql);
foreach($array_params as $params)
{
$stmt->execute($params);
}
But its possible to execute multiple insertions with one query like INSERT INTO table (col1,col2,col3) VALUES ("val1","val2","val3"),("val4","val5","val6"),("val7","val8,"val9"), by using something like this to build the query:
但是可以使用一个查询执行多个插入，例如INSERT INTO table (col1,col2,col3) VALUES ("val1","val2","val3"),("val4","val5","val6"),("val7","val8,"val9")，通过使用这样的东西来构建查询：
$all_inserts = array( array('val1', 'val2', 'val3'),array('val4', 'val5', 'val6'));
$sql = 'INSERT INTO table (col1,col2,col3) VALUES ';
$rows = array();
foreach ($all_inserts as $one_insert)
{
$rows[] = '('.implode(',', $pdo->quote($one_insert).')';
}
$sql .= ' '.implode(',', $rows);
$pdo->query($sql);
