在PHP PDO MYSQL中插入多行的最佳方法是什么? [英] What is the best way to insert multiple rows in PHP PDO MYSQL?
本文介绍了在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中插入多行的最佳方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文