logo标签列表

使用PDO预处理语句插入多行数据

phpmysqlpdoprepared-statement
14

我想知道是否可以使用一个预处理语句插入多行数据。 以下是我通常将一行数据插入数据库的示例：

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

等等。

这段代码可能要一次插入几百行，我考虑创建循环来创建数百个参数，然后为每一行追加SQL语句的额外插入，但我认为肯定有更好的方法。最好的方法是什么？

你目前在这个插入操作上有任何问题吗？ - Your Common Sense
1
不，这个单个插入的代码运行良好，我想知道如何最好地使用占位符以此方式插入多行。 - ezero
1
你从哪里得到要插入的值？ - eggyal
只需循环遍历您的多个参数并执行。您试过了吗？ - Your Common Sense
2
实际上，仔细检查后，我认为这是 https://dev59.com/oXM_5IYBdhLWcg3w43pt?rq=1 的重复。 - Martin Burch
4个回答
33

首先需要说明的是，你可以通过仅使用一个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...

一旦你了解了这个，你就能使用PDO（例如）得到一个很好的解决方案。
你必须记住要完整地使用prepare和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 ...
);

你的目标是将此结果作为预处理查询：

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查询。

如果您需要插入的行数太多（参见此文档），则应逐个执行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);
}
1
我这里有什么遗漏或者这样行不行？对于两行数据，"INSERT INTO Table (col1, col2, col3) VALUES (".implode(',', $args).")"; 会变成 "INSERT INTO Table (col1, col2, col3) VALUES (?,?,?,?,?,?)"; ，但是这是无效的。(?,?,?,?,?,?) 应该改为 (?,?,?),(?,?,?)。 - jonathancardoso
1
@JCM 你说得对，我有点惊讶之前没有人提到过。现在已经更正了。感谢您的评论！ - Justin Iurman
1
@Edward 这里有一个参考链接来解释答案中的“too many”。 - Justin Iurman
1
啊，抱歉我错过了那个问题，谢谢！不管怎样，五年后你仍然支持这个答案，太棒了！ - Edward
7

如果您的表是事务性的（例如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();
1
这会导致单个插入查询吗？所以一次性插入数千条数据仍然非常快速吗？ - kentor
@kentor 不是的，但由于预处理语句的特性，它仍然非常快速，随着执行越来越多的预处理语句，其效率也会更高。 - The Onin
6
如果你只需要插入几百行数据，我建议使用以下简单的代码。准备一个单行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当然支持多行插入语法，因此您可以尝试将其组合在一起。
$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);

但如果您尝试创建一个包含与数据数组中的项数量相同的元组的单个INSERT语句，则可能会意外生成超过最大数据包长度的SQL语句。

如果您有成千上万行，足以使逐行执行预处理语句成为过重负担，则应使用LOAD DATA INFILE。

3
为了保留您的代码，您需要创建一个循环来执行所有所需的插入操作：
$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);
}

但是使用类似于以下的方式构建查询，可以通过一个查询执行多个插入操作，例如：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);
实际上，最后一个答案并没有使用绑定参数，因此不够安全。在第一个答案中，准备工作是在循环外进行的，因此无法正常工作。 - ezero
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接