在php中添加数据库的最佳multiInsert方法? [英] Best multiInsert way to add database in php?

问题描述

如何将最佳方式添加到数据库multiInsert行中?
例如我有数组，我想将所有数组添加到数据库中.我可以创建循环foreach并添加所有数组.

How is best way add to database multiInsert row?
E.g I have array and i would like add all array to database. I can create loop foreach and add all arrays.

 $array=['apple','orange'];
 foreach($array as $v) 
 {
   $stmt = $db->exec("Insert into test(fruit) VALUES ('$v')");
 }

这是可行的，但也许我应该使用事务处理?还是其他方式?

And it's work, but maybe i should use transaction? or it do other way?

推荐答案

使用准备好的语句.

$sql = "INSERT INTO test (fruit) VALUES ";
$sql .= implode(', ', array_fill(0, count($array), '(?)'));
$stmt = $db->prepare($sql);
$stmt->execute($array);

SQL将如下所示:

INSERT INTO test (fuit) VALUES (?), (?), (?), ...

其中(?)的数量与$array中的元素数量一样.

where there are as many (?) as the number of elements in $array.

使用多个VALUES进行单个查询比循环执行单独的查询要有效得多.

Doing a single query with many VALUES is much more efficient that performing separate queries in a loop.

如果您有一个带有输入行的关联数组的单行，则可以使用如下准备的查询:

If you have an associative array with input values for a single row, you can use a prepared query like this:

$columns = implode(',', array_keys($array);
$placeholders = implode(', ', array_fill(0, count($array), '?'));
$sql = "INSERT INTO test($columns) VALUES ($placeholders)";
$stmt = $db->prepare($sql);
$stmt->execute(array_values($array));

这篇关于在php中添加数据库的最佳multiInsert方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！