在 PHP PDO MYSQL 中插入多行的最佳方法是什么?

时间:2023-04-08
本文介绍了在 PHP PDO MYSQL 中插入多行的最佳方法是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

比如说,我们要在一个表中插入多行:

Say, we have multiple rows to be inserted in a table:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ] //[ array of values ];

使用 PDO:

$sql = "insert into `table_name` (col1, col2, col3) values (?, ?, ?)" ;

现在,您应该如何继续插入行?像这样吗?

Now, how should you proceed in inserting the rows? Like this?

$stmt = $db->prepare($sql);

foreach($rows as $row){
  $stmt->execute($row);
}

或者,像这样?

$sql = "insert into `table_name` (col1, col2, col3) values ";
$sql .= //not sure the best way to concatenate all the values, use implode?
$db->prepare($sql)->execute();

哪种方式会更快更安全?插入多行的最佳方法是什么?

Which way would be faster and safer? What is the best way to insert multiple rows?

推荐答案

您至少有以下两个选择:

You have at least these two options:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values (?,?,?)";

$stmt = $db->prepare($sql);

foreach($rows as $row)
{
    $stmt->execute($row);
}

OR:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values ";

$paramArray = array();

$sqlArray = array();

foreach($rows as $row)
{
    $sqlArray[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';

    foreach($row as $element)
    {
        $paramArray[] = $element;
    }
}

// $sqlArray will look like: ["(?,?,?)", "(?,?,?)", ... ]

// Your $paramArray will basically be a flattened version of $rows.

$sql .= implode(',', $sqlArray);

$stmt = $db->prepare($sql);

$stmt->execute($paramArray);

正如你所看到的,第一个版本有很多简单的代码;但是第二个版本确实执行批量插入.批量插入应该更快,但我同意 @BillKarwin 在绝大多数实现中不会注意到性能差异.

As you can see the first version features a lot simpler code; however the second version does execute a batch insert. The batch insert should be faster, but I agree with @BillKarwin that the performance difference will not be noticed in the vast majority of implementations.

这篇关于在 PHP PDO MYSQL 中插入多行的最佳方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

上一篇:致命错误 在 null 上调用成员函数 prepare() 下一篇:SQL Server 错误 1934 发生在 INSERT 到带有计算列 PHP/PDO 的表

相关文章