使用PDO准备好的语句插入多行 [英] Insert multiple rows with PDO prepared statements

问题描述

我想知道是否可以使用一条准备好的语句插入多行. 以下是我通常如何在数据库中插入一行的示例:

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);

我要插入的值将来自数组，例如: $ values [0] ['val1']; $ values [0] ['val2']; $ values [0] ['val3']; $ values [1] ['val1']; $ values [2] ['val2'];

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'];

等

此代码可能必须一次插入几百行，我考虑过创建一个循环以创建数百个参数，然后为每行添加一个sql语句并添加一个额外的插入，但是我认为必须有更好的方法.最好的方法是什么?

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?

推荐答案

首先要说的是，您可以插入多行，这要感谢一个 查询

The first important thing to say is that you can insert multiple rows thanks to only one INSERT query

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...

一旦知道了这一点，就可以使用PDO获得良好的解决方案(例如).
您必须记住，您需要完整的prepare和execute过程(就安全性而言，您必须分别传递每个参数).

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 prepare and execute process (in term of security, you have to pass each parameter separately).

假设您要插入的行结构如下:

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 (?, ?, ?),
       (?, ?, ?),
       (?, ?, ?)

及其相应的执行:

PDOStatement::execute(array('abc', 'def', 'ghi', 'abc', 'def', 'ghi', 'abc', 'def', 'ghi'));

好吧，您只现在就必须这样做:

$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);

然后...就这样！

通过这种方式，每个参数都可以单独处理，这就是您想要的(安全性，安全性，安全性！)以及所有这些参数，并且只需一个INSERT查询即可通过动态方式进行处理

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 INSERT query

如果要插入的行太多(请参见

If you have too many rows to insert (see this), you should execute one by one

$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);
}

这篇关于使用PDO准备好的语句插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！