問題描述

我制作了一個腳本，用于創(chuàng)建一個原始查詢字符串，然后在一個語句中插入數(shù)百行.它可以工作，但不提供準備好的語句所提供的保護.然后我修改了我的腳本以添加準備好的語句.但是，它可以工作，但速度要慢得多.與原始查詢腳本相比，帶有準備好的語句的腳本插入行所需的時間要長得多，因為腳本會一次運行一行準備好的插入語句，而不是一次插入數(shù)百行.

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>";
  }
}

它完成了工作，但只有在數(shù)百次插入之后.有沒有辦法將列表或數(shù)組綁定到 bind_param() 參數(shù)，然后只運行 $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.

抱歉，如果之前有人問過并回答過這個問題.我環(huán)顧四周，發(fā)現(xiàn)了一些類似的問題，但沒有明確回答我的要求.

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.

推薦答案

可以通過動態(tài)構(gòu)建批量插入語句查詢來準備它，但這需要一些技巧.最重要的位是使用 str_pad() 構(gòu)造可變長度的查詢字符串，并使用 call_user_func_array() 調(diào)用 bind_param() 與可變數(shù)量的參數(shù).

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;
}

此函數(shù)將您的 $db 作為 mysqli 實例、表名、字段名數(shù)組和值引用的平面數(shù)組.每個查詢最多插入 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:

• 表名和字段名沒有轉(zhuǎn)義；我把它留給你來確保它們不包含反引號.幸運的是，它們永遠不應(yīng)該來自用戶輸入.
• 如果$values 的長度不是$fields 長度的偶數(shù)倍，那么最終塊可能會在準備階段失敗.
• 同樣，在大多數(shù)情況下，$types 參數(shù)的長度應(yīng)該與 $fields 的長度匹配，尤其是當(dāng)它們中的一些不同時.
• 它不區(qū)分三種失敗方式.它也不會跟蹤成功插入的次數(shù)，也不會在出現(xiàn)錯誤后嘗試繼續(xù).

• 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.

定義此函數(shù)后，您的示例代碼可以替換為:

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.

您沒有給我們原始準備好的語句，因此您可能需要調(diào)整表和字段名稱.看起來您的代碼也位于 $i 上的循環(huán)中；在這種情況下，只有 for 循環(huán)需要在外循環(huán)內(nèi).如果您將其他行移出循環(huán)，您將使用更多的內(nèi)存來構(gòu)建 $inserts 數(shù)組，以換取更高效的批量插入.

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() 以接受多維數(shù)組，從而消除潛在錯誤的一個來源，但這需要在對數(shù)組進行分塊后進行展平.

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.

這篇關(guān)于MySQLi:使用一個準備好的語句插入多行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！