使用 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?

推荐答案

首先要说的是，您可以插入多行，这要归功于 one INSERT 查询

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

如果要插入的行太多(请参阅this)，你应该一一执行

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屋！