php PDO使用占位符插入多行

I am looking to do multiple inserts using PHP PDO.

我希望使用PHP PDO来做多个插入。

The closest answer I have found is this one

我找到的最接近的答案是这个

how-to-insert-an-array-into-a-single-mysql-prepared-statement

how-to-insert-an-array-into-a-single-mysql-prepared-statement

However the example thats been given uses ?? instead of real placeholders.

但是这个例子有什么用呢?而不是真正的占位符。

I have looked at the examples on the PHP doc site for place holders

我已经在PHP doc站点上查看了位置所有者的示例

php.net pdo.prepared-statements

php.net pdo.prepared-statements

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

Now lets say I wanted to achieve the above but with an array

现在让我们假设我想要实现上面的但是要使用数组

$valuesToInsert = array(
  0 => array('name' => 'Robert', 'value' => 'some value'),
  1 => array('name' -> 'Louise', 'value' => 'another value')
);

How would I go about it with PDO and multiple inserts per transaction?

如何处理每个事务的PDO和多个插入?

I imagine it would start of with a loop?

我想它会以一个循环开始?

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");

foreach($valuesToInsert as $insertRow){

    // now loop through each inner array to match binded values
    foreach($insertRow as $column => value){
        $stmt->bindParam(":{$column}", value);
    }
}
$stmt->execute();

However the above does not work but hopefully will demonstrate what im trying to achieve

然而上述方法并不奏效，但希望能证明我正在努力实现的目标

5 个解决方案

#1

First of all, ? symbols are real place-holders (most drivers allow to use both syntaxes, positional and named place-holders). Secondly, prepared statements are nothing but a tool to inject raw input into SQL statements—the syntax of the SQL statement itself is unaffected. You already have all the elements you need:

首先,?符号是真实的位置持有者(大多数驱动允许使用语法，位置和命名的位置持有者)。其次，准备好的语句只不过是将原始输入注入SQL语句的工具——SQL语句本身的语法不受影响。你已经拥有了所有你需要的元素:

• How to insert multiple rows with a single query
• 如何用一个查询插入多个行
• How to generate SQL dynamically
• 如何动态生成SQL
• How to use prepared statements with named place-holders.
• 如何使用已准备的声明与指定的位置持有者。

It's fairly trivial to combine them all:

把它们结合起来是很琐碎的:

$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();
$n = 0;
foreach ($data as $row) {
    $insertQuery[] = '(:memberID' . $n . ', :programID' . $n . ')';
    $insertData['memberID' . $n] = $memberid;
    $insertData['programID' . $n] = $row;
    $n++;
}

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $stmt = $db->prepare($sql);
    $stmt->execute($insertData);
}

#2

I'm assuming you are using InnoDB so this answer is only valid for that engine (or any other transaction-capable engine, meaning MyISAM isn't included).

我假设您正在使用InnoDB，所以这个答案只适用于该引擎(或任何其他事务处理能力的引擎，这意味着MyISAM不包括在内)。

By default InnoDB runs in auto-commit mode. That means each query is treated as its own contained transaction.

默认情况下，InnoDB在自动提交模式下运行。这意味着每个查询都被视为它自己包含的事务。

To translate that to something us mortals can understand, it means that every INSERT query you issue will force hard-disk to commit it by confirming it wrote down the query information. Considering how mechanical hard-disks are super slow since their input-output operation per second is low (if I'm not mistaken, the average is 300ish IO's), it means your 50 000 queries will be - well, super slow.

要将其转化为我们普通人能够理解的东西，这意味着您发出的每一个插入查询都将通过确认它写下了查询信息来迫使硬盘提交它。考虑到机械硬盘的速度非常慢，因为它们每秒的输入-输出操作都很低(如果我没弄错的话，平均是300ish IO)，这就意味着你的5万个查询会非常慢。

So what do you do? You commit all of your 50k queries in a single transaction. It might not be the best solution for various purposes but it'll be fast.

那你该怎么办呢?在一个事务中提交所有50k查询。它可能不是用于各种目的的最佳解决方案，但速度很快。

You do it like this:

你这样做:

$dbh->beginTransaction();

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");

foreach($valuesToInsert as $insertRow)
{    
    // now loop through each inner array to match bound values
    foreach($insertRow as $column => value)
    {
        $stmt->bindParam(":$column", value);
        $stmt->execute();
    }
}


$dbh->commit();

#3

A little modifications in solution provided by N.B
$stmt->execute() should be outside of inner loop because you may have one or more columns that need to bind before calling $stmt->execute() else you 'll get exception "Invalid parameter number: number of bound variables does not match number of token".
2nd "value" variable were missing dollar signs.

对N提供的溶液做了一点修改。B $stmt->执行()应该在内部循环之外，因为在调用$stmt->execute()之前，您可能有一个或多个列需要绑定，否则您将得到异常“无效参数号:绑定变量的数量与令牌数量不匹配”。第二个“价值”变量缺少美元符号。

function batchinsert($sql,$params){
    try { 
                db->beginTransaction(); 

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

                foreach($params as $row)
                {    
                    // now loop through each inner array to match bound values
                    foreach($row as $column => $value)
                    {                           
                        $stmt->bindParam(":$column", $value);                           
                    }
                    $stmt->execute();
                }                                       
                db->commit();                   

        } catch(PDOExecption $e) {
            $db->rollback();                
        }
}

Test:

测试:

$sql = "INSERT INTO `test`(`name`, `value`) VALUES (:name, :value)" ;

$data = array();    

array_push($data, array('name'=>'Name1','value'=>'Value1')); 

array_push($data, array('name'=>'Name2','value'=>'Value2')); 

array_push($data, array('name'=>'Name3','value'=>'Value3')); 

array_push($data, array('name'=>'Name4','value'=>'Value4')); 

array_push($data, array('name'=>'Name5','value'=>'Value5')); 

batchinsert($sql,$data);

#4

Your code was actually ok, but had a problem in $stmt->bindParam(":$column", value); It should be $stmt->bindValue(":{$column}", $value); and it will work perfectly. This will assist others in future.

您的代码实际上还可以，但是在$stmt->bindParam(“:$column”，value)中出现了问题;它应该是$stmt->bindValue(“:{$ $}”，$value);它会很好地工作。这将在未来帮助其他人。

Full code:

完整的代码:

foreach($params as $row)
{ 
    // now loop through each inner array to match bound values
    foreach($row as $column => $value)
    { 
        $stmt->bindValue(":{$column}", $value); //EDIT
    }
    // Execute statement to add to transaction
    $stmt->execute();
} 

#5

Move execute inside of the loop.

在循环内部移动执行。

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
foreach($valuesToInsert as $insertRow)
{
    $stmt->execute($insertRow);    
}

If you experience any problems with this such recommended way, you have to ask a question, describing these certain problems.

如果你遇到这样的问题，你必须问一个问题，描述这些问题。