已准备好PDO在单个查询中插入多行 [英] PDO Prepared Inserts multiple rows in single query

问题描述

我目前正在MySQL上使用这种类型的SQL在一个查询中插入多行值:

I am currently using this type of SQL on MySQL to insert multiple rows of values in one single query:

INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...

关于PDO的读物，使用准备好的语句应该比静态查询给我提供更好的安全性.

On the readings on PDO, the use prepared statements should give me a better security than static queries.

因此，我想知道是否可以使用准备好的语句生成通过使用一个查询来插入多行值".

I would therefore like to know whether it is possible to generate "inserting multiple rows of values by the use of one query" using prepared statements.

如果可以，我是否知道该如何实施?

If yes, may I know how can I implement it?

推荐答案

带有PDO准备语句的多值插入

在一个execute语句中插入多个值.为什么要根据此页面，它比常规方法要快插入.

Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.

$datafields = array('fielda', 'fieldb', ... );

$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);

更多数据值，或者您可能有一个填充数据的循环.

more data values or you probably have a loop that populates data.

使用准备好的插入，您需要知道要插入的字段以及创建?的字段数量.占位符以绑定您的参数.

With prepared inserts you need to know the fields you're inserting to, and the number of fields to create the ? placeholders to bind your parameters.

insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....

基本上，这就是我们想要插入语句的样子.

That is basically how we want the insert statement to look like.

现在，代码:

function placeholders($text, $count=0, $separator=","){
    $result = array();
    if($count > 0){
        for($x=0; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

$pdo->beginTransaction(); // also helps speed up your inserts.
$insert_values = array();
foreach($data as $d){
    $question_marks[] = '('  . placeholders('?', sizeof($d)) . ')';
    $insert_values = array_merge($insert_values, array_values($d));
}

$sql = "INSERT INTO table (" . implode(",", $datafields ) . ") VALUES " .
       implode(',', $question_marks);

$stmt = $pdo->prepare ($sql);
try {
    $stmt->execute($insert_values);
} catch (PDOException $e){
    echo $e->getMessage();
}
$pdo->commit();

尽管在我的测试中，使用多个刀片和具有单个值的常规预加工刀片仅相差1秒.

Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.

这篇关于已准备好PDO在单个查询中插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！