MySQLi:用一个准备好的语句插入多行 [英] MySQLi : Inserting multiple rows with one prepared statement

问题描述

我制作了一个脚本，该脚本创建一个原始查询字符串，然后在一个语句中插入数百行.它有效，但不提供准备好的语句所提供的保护.然后，我修改了脚本以添加准备好的语句.但是，它的运行速度明显慢得多.带有预准备语句的脚本比原始查询脚本插入行所花的时间要长得多，这是因为该脚本一次通过一行预准备的插入语句，而不是一次插入数百行.

I made a script that creates a raw query string and then inserts the hundreds of rows in one statement. It works but does not offer the protections that prepared statements do. I then modified my script to add prepared statements. It works however, it is significantly slower. The script with prepared statements takes much longer to insert the rows than the raw query script, due to the script running through each prepared insert statement one row at a time rather than inserting hundred of rows at a time.

下面是准备好的语句代码的片段:

Here's a snippet of the prepared statement code:

for( $j = 0; $j < $abilitiesMax - 2; $j++ ){
  $stmtAbility->bind_param('iiiii', $abilityArray[$i]["match_id"] , $abilityArray[$i]["player_slot"],
  $abilityArray[$i][$j]["ability"], $abilityArray[$i][$j]["time"], $abilityArray[$i][$j]["level"] );

  if(  !($stmtAbility->execute()) ){      
   echo "<p>$db->error</p>";
   echo "<p>ERROR: when trying to insert abilities query</p>";
  }
}

它可以完成工作，但要经过成百上千次插入.有没有一种方法可以将列表或数组绑定到bind_param()参数，并且只运行一次$ stmtAbility-> execute或其他可以提高性能的方法.

It gets the job done but only after hundreds upon hundreds of inserts. Is there a way to bind lists or arrays to the bind_param() arguments and just run the $stmtAbility->execute one time or some other method that can speed up performance.

很抱歉，是否曾经有人问过并回答过.我环顾了一会儿，发现了一些类似的问题，但没有任何问题可以明确地回答我的要求.

Sorry if this has been asked and answered before. I looked around for a while and found some similar questions but nothing that answered what I was asking for explicitly.

推荐答案

可以通过动态构造批量插入语句查询来准备它，但这需要一些技巧.最重要的位是使用str_pad()构造长度可变的查询字符串，并使用call_user_func_array()调用具有可变数量参数的bind_param().

It's possible to prepare a bulk insert statement query by constructing it on the fly, but it takes a few tricks. The most important bits are using str_pad() to construct a query string of variable length, and using call_user_func_array() to call bind_param() with a variable number of parameters.

function insertBulkPrepared($db, $table, $fields, $types, $values) {
    $chunklength = 500;
    $fieldcount = count($fields);
    $fieldnames = '`'.join('`, `', $fields).'`';
    $prefix = "INSERT INTO `$table` ($fieldnames) VALUES ";
    $params = '(' . str_pad('', 3*$fieldcount - 2, '?, ') . '), ';
    $inserted = 0;

    foreach (array_chunk($values, $fieldcount*$chunklength) as $group) {
        $length = count($group);
        if ($inserted != $length) {
            if ($inserted) $stmt->close();
            $records = $length / $fieldcount;
            $query = $prefix . str_pad('', 3*$length + 2*($records - 1), $params);
            #echo "\n<br>Preparing '" . $query . "'";
            $stmt = $db->prepare($query);
            if (!$stmt) return false;
            $binding = str_pad('', $length, $types);
            $inserted = $length;
        }

        array_unshift($group, $binding);
        #echo "\n<br>Binding " . var_export($group, true);
        $bound = call_user_func_array(array($stmt, 'bind_param'), $group);
        if (!$bound) return false;
        if (!$stmt->execute()) return false;
    }

    if ($inserted) $stmt->close();
    return true;
}

此函数将您的$db用作mysqli实例，一个表名，一个字段名数组和一个对值的引用的平面数组.每个查询最多可插入500条记录，并在可能的情况下重新使用准备好的语句.如果所有插入均成功，则返回true；如果所有插入均失败，则返回false.注意事项:

This function takes your $db as a mysqli instance, a table name, an array of field names, and a flat array of references to values. It inserts up to 500 records per query, re-using prepared statements when possible. It returns true if all of the inserts succeeded, or false if any of them failed. Caveats:

• 表名和字段名不转义；我要由您决定，以确保它们不包含反引号.幸运的是，它们绝不应该来自用户输入.
• 如果$values的长度不是$fields长度的偶数倍，则最后一个块可能会在准备阶段失败.
• 同样，在大多数情况下，$types参数的长度应与$fields的长度匹配.
• 它不能区分三种失败方法.它也不会跟踪成功插入了多少次，也不会在出现错误后尝试继续.

• The table and field names are not escaped; I leave it up to you to ensure that they don't contain backticks. Fortunately, they should never come from user input.
• If the length of $values is not an even multiple of the length of $fields, the final chunk will probably fail at the preparation stage.
• Likewise, the length of the $types parameter should match the length of $fields in most cases, particularly when some of them differ.
• It doesn't distinguish between the three ways to fail. It also don't keep track of how many inserts succeeded, nor does it attempt to continue after an error.

定义此功能后，您的示例代码可以替换为:

With this function defined, your example code can be replaced with something like:

$inserts = array();
for ($j = 0; $j < $abilitiesMax - 2; $j++) {
    $inserts[] = &$abilityArray[$i]['match_id'];
    $inserts[] = &$abilityArray[$i]['player_slot'];
    $inserts[] = &$abilityArray[$i][$j]['ability'];
    $inserts[] = &$abilityArray[$i][$j]['time'];
    $inserts[] = &$abilityArray[$i][$j]['level'];
}

$fields = array('match_id', 'player_slot', 'ability', 'time', 'level');
$result = insertBulkPrepared($db, 'abilities', $fields, 'iiiii', $inserts);
if (!$result) {
    echo "<p>$db->error</p>";
    echo "<p>ERROR: when trying to insert abilities query</p>";
}

这些与号很重要，因为mysqli_stmt::bind_param需要引用，而最新版本的PHP中没有call_user_func_array提供的引用.

Those ampersands are important, because mysqli_stmt::bind_param expects references, which aren't provided by call_user_func_array in recent versions of PHP.

您没有给我们提供原始的准备好的语句，因此您可能需要调整表和字段名称.看起来您的代码也位于$i上的循环内；在这种情况下，仅for循环需要位于外部循环内部.如果将其他行放在循环之外，则将使用更多的内存来构造$inserts数组，以换取更有效的批量插入.

You didn't give us the original prepared statement, so you probably need to adjust the table and field names. It also looks like your code sits inside a loop over $i; in that case, only the for loop needs to be inside the outer loop. If you take the other lines outside the loop, you will use a bit more memory constructing the $inserts array, in return for much more efficient bulk inserts.

还可以重写insertBulkPrepared()以接受多维数组，从而消除了一个潜在的错误源，但这需要在对数组进行分块后对其进行展平.

It's also possible to rewrite insertBulkPrepared() to accept a multi-dimensional array, eliminating one source of potential error, but that requires flattening the array after chunking it.

这篇关于MySQLi:用一个准备好的语句插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！