php PDO Prepared 在单个查询中插入多行

PDO Prepared Inserts multiple rows in single query

提问by hoball

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

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

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

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

关于 PDO 的阅读，使用准备好的语句应该给我一个比静态查询更好的安全性。

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?

如果是，我可以知道如何实施吗？

回答by Herbert Balagtas

Multiple Values Insert with PDO Prepared Statements

使用 PDO 准备好的语句插入多值

Inserting multiple values in one execute statement. Why because according to this pageit 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.

这基本上就是我们希望插入语句的样子。

Now, the code:

现在，代码：

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();

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

尽管在我的测试中，使用多个插入和常规准备的具有单个值的插入时只有 1 秒的差异。

回答by jamesvl

Same answer as Mr. Balagtas, slightly clearer...

和 Balagtas 先生的回答一样，稍微清楚一点……

Recent versions MySQL and PHP PDO dosupport multi-row INSERTstatements.

最新版本的 MySQL 和 PHP PDO确实支持多行INSERT语句。

SQL Overview

SQL 概述

The SQL will look something like this, assuming a 3-column table you'd like to INSERTto.

SQL 看起来像这样，假设您想要一个 3 列的表INSERT。

INSERT INTO tbl_name
            (colA, colB, colC)
     VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) [,...]

ON DUPLICATE KEY UPDATEworks as expected even with a multi-row INSERT; append this:

ON DUPLICATE KEY UPDATE即使使用多行插入也能按预期工作；附加这个：

ON DUPLICATE KEY UPDATE colA = VALUES(colA), colB = VALUES(colB), colC = VALUES(colC)

PHP Overview

PHP 概述

Your PHP code will follow the usual $pdo->prepare($qry)and $stmt->execute($params)PDO calls.

你的PHP代码将按照通常$pdo->prepare($qry)和$stmt->execute($params)PDO电话。

$paramswill be a 1-dimensional array of allthe values to pass to the INSERT.

$params将一个1维阵列的所有的值，以传递到INSERT。

In the above example, it should contain 9 elements; PDO will use every set of 3 as a single row of values. (Inserting 3 rows of 3 columns each = 9 element array.)

在上面的例子中，它应该包含 9 个元素；PDO 将使用每组 3 作为单行值。（插入 3 行，每行 3 列 = 9 个元素数组。）

Implementation

执行

Below code is written for clarity, not efficiency. Work with the PHP array_*()functions for better ways to map or walk through your data if you'd like. Whether you can use transactions obviously depends on your MySQL table type.

下面的代码是为了清晰而不是效率而编写的。array_*()如果您愿意，可以使用 PHP函数以更好的方式映射或遍历数据。是否可以使用事务显然取决于您的 MySQL 表类型。

Assuming:

假设：

• $tblName- the string name of the table to INSERT to
• $colNames- 1-dimensional array of the column names of the table These column names must be valid MySQL column identifiers; escape them with backticks (``) if they are not
• $dataVals- mutli-dimensional array, where each element is a 1-d array of a row of values to INSERT

• $tblName- 要插入的表的字符串名称
• $colNames- 表列名的一维数组这些列名必须是有效的 MySQL 列标识符；如果不是，则用反引号 (``) 转义它们
• $dataVals- 多维数组，其中每个元素是要插入的一行值的一维数组

Sample Code

示例代码

// setup data values for PDO
// memory warning: this is creating a copy all of $dataVals
$dataToInsert = array();

foreach ($dataVals as $row => $data) {
    foreach($data as $val) {
        $dataToInsert[] = $val;
    }
}

// (optional) setup the ON DUPLICATE column names
$updateCols = array();

foreach ($colNames as $curCol) {
    $updateCols[] = $curCol . " = VALUES($curCol)";
}

$onDup = implode(', ', $updateCols);

// setup the placeholders - a fancy way to make the long "(?, ?, ?)..." string
$rowPlaces = '(' . implode(', ', array_fill(0, count($colNames), '?')) . ')';
$allPlaces = implode(', ', array_fill(0, count($dataVals), $rowPlaces));

$sql = "INSERT INTO $tblName (" . implode(', ', $colNames) . 
    ") VALUES " . $allPlaces . " ON DUPLICATE KEY UPDATE $onDup";

// and then the PHP PDO boilerplate
$stmt = $pdo->prepare ($sql);

try {
   $stmt->execute($dataToInsert);
} catch (PDOException $e){
   echo $e->getMessage();
}

$pdo->commit();

回答by JM4

For what it is worth, I have seen a lot of users recommend iterating through INSERT statements instead of building out as a single string query as the selected answer did. I decided to run a simple test with just two fields and a very basic insert statement:

就其价值而言，我看到很多用户建议迭代 INSERT 语句，而不是像所选答案那样构建为单个字符串查询。我决定运行一个只有两个字段和一个非常基本的插入语句的简单测试：

<?php
require('conn.php');

$fname = 'J';
$lname = 'M';

$time_start = microtime(true);
$stmt = $db->prepare('INSERT INTO table (FirstName, LastName) VALUES (:fname, :lname)');

for($i = 1; $i <= 10; $i++ )  {
    $stmt->bindParam(':fname', $fname);
    $stmt->bindParam(':lname', $lname);
    $stmt->execute();

    $fname .= 'O';
    $lname .= 'A';
}


$time_end = microtime(true);
$time = $time_end - $time_start;

echo "Completed in ". $time ." seconds <hr>";

$fname2 = 'J';
$lname2 = 'M';

$time_start2 = microtime(true);
$qry = 'INSERT INTO table (FirstName, LastName) VALUES ';
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?)";

$stmt2 = $db->prepare($qry);
$values = array();

for($j = 1; $j<=10; $j++) {
    $values2 = array($fname2, $lname2);
    $values = array_merge($values,$values2);

    $fname2 .= 'O';
    $lname2 .= 'A';
}

$stmt2->execute($values);

$time_end2 = microtime(true);
$time2 = $time_end2 - $time_start2;

echo "Completed in ". $time2 ." seconds <hr>";
?>

While the overall query itself took milliseconds or less, the latter (single string) query was consistently 8 times faster or more. If this was built out to say reflect an import of thousands of rows on many more columns, the difference could be enormous.

虽然整个查询本身花费了几毫秒或更短的时间，但后者（单字符串）查询始终快 8 倍或更多。如果这是为了反映在更多列上导入数千行，那么差异可能是巨大的。

回答by Chris M.

The Accepted Answer by Herbert Balagtas works well when the $data array is small. With larger $data arrays the array_merge function becomes prohibitively slow. My test file to create the $data array has 28 cols and is about 80,000 lines. The final script took 41sto complete.

当 $data 数组很小时，Herbert Balagtas 的 Accepted Answer 效果很好。对于更大的 $data 数组，array_merge 函数会变得非常慢。我创建 $data 数组的测试文件有 28 列，大约有 80,000 行。最终的剧本花了41 秒才完成。

Using array_push()to create $insert_values instead of array_merge() resulted in a 100X speed upwith execution time of 0.41s.

使用array_push()创建 $insert_values 而不是 array_merge() 导致速度提高了100 倍，执行时间为0.41s。

The problematic array_merge():

有问题的 array_merge()：

$insert_values = array();

foreach($data as $d){
 $question_marks[] = '('  . placeholders('?', sizeof($d)) . ')';
 $insert_values = array_merge($insert_values, array_values($d));
}

To eliminate the need for array_merge(), you can build the following two arrays instead:

为了消除对 array_merge() 的需要，您可以构建以下两个数组：

//Note that these fields are empty, but the field count should match the fields in $datafields.
$data[] = array('','','','',... n ); 

//getting rid of array_merge()
array_push($insert_values, $value1, $value2, $value3 ... n ); 

These arrays can then be used as follows:

然后可以按如下方式使用这些数组：

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();

foreach($data as $d){
 $question_marks[] = '('  . placeholders('?', sizeof($d)) . ')';
}

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

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

回答by Zyx

Two possible approaches:

两种可能的方法：

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:v1_1, :v1_2, :v1_3),
    (:v2_1, :v2_2, :v2_3),
    (:v2_1, :v2_2, :v2_3)');
$stmt->bindValue(':v1_1', $data[0][0]);
$stmt->bindValue(':v1_2', $data[0][1]);
$stmt->bindValue(':v1_3', $data[0][2]);
// etc...
$stmt->execute();

Or:

或者：

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
    $stmt->bindValue(':a', $item[0]);
    $stmt->bindValue(':b', $item[1]);
    $stmt->bindValue(':c', $item[2]);
    $stmt->execute();
}

If the data for all the rows are in a single array, I would use the second solution.

如果所有行的数据都在一个数组中，我会使用第二种解决方案。

回答by sebasgo

That's simply not the way you use prepared statements.

这根本不是您使用准备好的语句的方式。

It is perfectly okay to insert one row per query because you can execute one prepared statement multiple times with different parameters. In fact that is one of the greatest advantages as it allows you to insert you a great number of rows in an efficient, secure and comfortable manner.

每个查询插入一行是完全可以的，因为您可以使用不同的参数多次执行一个准备好的语句。事实上，这是最大的优势之一，因为它允许您以高效、安全和舒适的方式插入大量行。

So it maybe possible to implement the scheme you proposing, at least for a fixed number of rows, but it is almost guaranteed that this is not really what you want.

因此，也许可以实现您提出的方案，至少对于固定数量的行，但几乎可以保证这不是您真正想要的。

回答by fyrye

A shorter answer: flatten the array of data ordered by columns then

一个简短的答案：展平按列排序的数据数组，然后

//$array = array( '1','2','3','4','5', '1','2','3','4','5');
$arCount = count($array);
$rCount = ($arCount  ? $arCount - 1 : 0);
$criteria = sprintf("(?,?,?,?,?)%s", str_repeat(",(?,?,?,?,?)", $rCount));
$sql = "INSERT INTO table(c1,c2,c3,c4,c5) VALUES$criteria";

When inserting a 1,000 or so records you don't want to have to loop through every record to insert them when all you need is a count of the values.

当插入 1,000 条左右的记录时，您不希望循环遍历每条记录来插入它们，而您只需要对值进行计数。

回答by fyrye

Here is my simple approach.

这是我的简单方法。

    $values = array();
    foreach($workouts_id as $value){
      $_value = "(".$value.",".$plan_id.")";
      array_push($values,$_value);
    }
    $values_ = implode(",",$values);

    $sql = "INSERT INTO plan_days(id,name) VALUES" . $values_."";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();

回答by Pierre Dumuid

Here's a class I wrote do multiple inserts with purge option:

这是我编写的一个使用清除选项进行多次插入的类：

<?php

/**
 * $pdo->beginTransaction();
 * $pmi = new PDOMultiLineInserter($pdo, "foo", array("a","b","c","e"), 10);
 * $pmi->insertRow($data);
 * ....
 * $pmi->insertRow($data);
 * $pmi->purgeRemainingInserts();
 * $pdo->commit();
 *
 */
class PDOMultiLineInserter {
    private $_purgeAtCount;
    private $_bigInsertQuery, $_singleInsertQuery;
    private $_currentlyInsertingRows  = array();
    private $_currentlyInsertingCount = 0;
    private $_numberOfFields;
    private $_error;
    private $_insertCount = 0;

    function __construct(\PDO $pdo, $tableName, $fieldsAsArray, $bigInsertCount = 100) {
        $this->_numberOfFields = count($fieldsAsArray);
        $insertIntoPortion = "INSERT INTO `$tableName` (`".implode("`,`", $fieldsAsArray)."`) VALUES";
        $questionMarks  = " (?".str_repeat(",?", $this->_numberOfFields - 1).")";

        $this->_purgeAtCount = $bigInsertCount;
        $this->_bigInsertQuery    = $pdo->prepare($insertIntoPortion.$questionMarks.str_repeat(", ".$questionMarks, $bigInsertCount - 1));
        $this->_singleInsertQuery = $pdo->prepare($insertIntoPortion.$questionMarks);
    }

    function insertRow($rowData) {
        // @todo Compare speed
        // $this->_currentlyInsertingRows = array_merge($this->_currentlyInsertingRows, $rowData);
        foreach($rowData as $v) array_push($this->_currentlyInsertingRows, $v);
        //
        if (++$this->_currentlyInsertingCount == $this->_purgeAtCount) {
            if ($this->_bigInsertQuery->execute($this->_currentlyInsertingRows) === FALSE) {
                $this->_error = "Failed to perform a multi-insert (after {$this->_insertCount} inserts), the following errors occurred:".implode('<br/>', $this->_bigInsertQuery->errorInfo());
                return false;
            }
            $this->_insertCount++;

            $this->_currentlyInsertingCount = 0;
            $this->_currentlyInsertingRows = array();
        }
        return true;
    }

    function purgeRemainingInserts() {
        while ($this->_currentlyInsertingCount > 0) {
            $singleInsertData = array();
            // @todo Compare speed - http://www.evardsson.com/blog/2010/02/05/comparing-php-array_shift-to-array_pop/
            // for ($i = 0; $i < $this->_numberOfFields; $i++) $singleInsertData[] = array_pop($this->_currentlyInsertingRows); array_reverse($singleInsertData);
            for ($i = 0; $i < $this->_numberOfFields; $i++) array_unshift($singleInsertData, array_pop($this->_currentlyInsertingRows));

            if ($this->_singleInsertQuery->execute($singleInsertData) === FALSE) {
                $this->_error = "Failed to perform a small-insert (whilst purging the remaining rows; the following errors occurred:".implode('<br/>', $this->_singleInsertQuery->errorInfo());
                return false;
            }
            $this->_currentlyInsertingCount--;
        }
    }

    public function getError() {
        return $this->_error;
    }
}

回答by Théo T. Carranza

This is how I did it:

我是这样做的：

First define the column names you'll use, or leave it blank and pdo will assume you want to use all the columns on the table - in which case you'll need to inform the row values in the exact order they appear on the table.

首先定义您将使用的列名，或者将其留空，pdo 将假设您要使用表中的所有列 - 在这种情况下，您需要按照它们在表中出现的确切顺序通知行值.

$cols = 'name', 'middleName', 'eMail';
$table = 'people';

Now, suppose you have a two dimensional array already prepared. Iterate it, and construct a string with your row values, as such:

现在，假设您已经准备好一个二维数组。迭代它，并用您的行值构造一个字符串，如下所示：

foreach ( $people as $person ) {
if(! $rowVals ) {
$rows = '(' . "'$name'" . ',' . "'$middleName'" . ',' .           "'$eMail'" . ')';
} else { $rowVals  = '(' . "'$name'" . ',' . "'$middleName'" . ',' . "'$eMail'" . ')';
}

Now, what you just did was check if $rows was already defined, and if not, create it and store row values and the necessary SQL syntax so it will be a valid statement. Note that strings should go inside double quotes and single quotes, so they will be promptly recognized as such.

现在，您刚才所做的是检查 $rows 是否已经定义，如果没有，则创建它并存储行值和必要的 SQL 语法，使其成为有效的语句。请注意，字符串应该放在双引号和单引号内，这样它们会被及时识别。

All it's left to do is prepare the statement and execute, as such:

剩下要做的就是准备语句并执行，如下所示：

$stmt = $db->prepare ( "INSERT INTO $table $cols VALUES $rowVals" );
$stmt->execute ();

Tested with up to 2000 rows so far, and the execution time is dismal. Will run some more tests and will get back here in case I have something further to contribute.

到目前为止测试了多达 2000 行，执行时间很短。将运行更多测试，如果我有进一步的贡献，我会回到这里。

Regards.

问候。