MySQLi:使用一个准备好的语句插入多行

2021-12-25 00:00:00 php mysql mysqli 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 "
<br>Preparing '" . $query . "'";
            $stmt = $db->prepare($query);
            if (!$stmt) return false;
            $binding = str_pad('', $length, $types);
            $inserted = $length;
        }

        array_unshift($group, $binding);
        #echo "
<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.

相关文章