問題描述

如何使用 mysqli 插入此查詢?...

How do I insert this query with mysqli?...

INSERT INTO table (field1, field2, field3) VALUES ('value', 'value', 'value'), ('value', 'value', 'value'), ('value', 'value', 'value');

通常在 mysql 中，這個查詢是直截了當的，將插入 3 行，我如何在 mysqli 中執行此操作而不使用準備好的語句，或者可能使用準備好的語句但不會太復雜?.我只是想知道是否有一種方法可以執行這樣的查詢，而無需在 PHP 中做額外的時髦事情.

Normally in mysql this query is straight forward and will insert 3 rows, how do I do this in mysqli without using a prepared statement or maybe using a prepared statement but without getting too complicated?. I just wish to know if there is a way to execute such query without doing extra funky stuff in PHP.

本質上，我有一些提取的數據，每個插入大約有 10 行(并且除了具有多行之外還需要多個插入)，這就是我需要的.我只希望像我通常使用 mysql 所做的那樣使用查詢來執行此操作，而不是每行添加多個插入.

In essence, I have some extracted data that has around 10 rows per insert (and also needs multiple inserts in addition to having multiple rows), and this is what I need it for. I only wish to do this with a query as I have normally done it with mysql, and not add multiple insert as one per each row.

推薦答案

mysqli 類提供了許多不同的方法來完成插入，每種方法都有自己的優點.當然，其中之一應該適合您的需求.

The mysqli class provides a number of different ways of accomplishing your inserts, each with its own benefits. Certainly, one of them should fit your needs.

以下示例假設您未指定的提取數據"存儲在數組數組中:$bigArray[0...datasetsize][0...2].

The following examples assume that your unspecified "extracted data" is stored in an array of arrays: $bigArray[0...datasetsize][0...2].

mysqli 數據庫假定為 $db.

The mysqli database is assumed to be $db.

方法 1 - 老派

您可以像以往一樣直接進行操作，只需構建查詢字符串并使用它查詢數據庫即可.根據您的指定，一次捆綁 10 個插入物.下面的代碼顯示了一個這樣的包，并簡單地擴展到整個數據集 (bigArray).數據可能應該使用 mysqli::escape_string 進行轉義(此??處未完成).

You can do it straight forward like you are used to by simply building your query string and querying the database with it. Inserts are bundled 10 at a time, as you specified. The following code shows one such bundle and is trivially extended to the whole data set (bigArray). The data should probably be escaped using mysqli::escape_string (not done here).

在所有示例中，要插入的數據都假定為整數.

The data to be inserted is assumed to be integers in all examples.

$sql = "INSERT INTO testTable (fieldA, fieldB, fieldC) VALUES ";
for ($i = 0; $i < 10; ++$i)
{
    if ($i > 0) $sql .= ", ";
    $sql .= "({$bigArray[$i][0]}),({$bigArray[$i][1]}),({$bigArray[$i][2]})";
}
$db->query($sql);

方法 2 - 盡可能簡單

如果您想使用準備好的語句和參數綁定，第一個嘗試可能如下所示.雖然不是最優的，但語句只準備一次.但是，每個插入都綁定了變量，這很浪費(但很簡單).由于未捆綁插入，因此示例循環了 10 個.

If you want to use a prepared statement and parameter binding, a first effort might look like the following. While not optimal, the statement is only prepared once. However, the variables are bound for each insert, which is wasteful (but simple). Since inserts are not bundled, the example loops over 10.

$statement = $db->prepare("INSERT INTO testTable (fieldA, fieldB, fieldC) VALUES (?,?,?)");
for ($i = 0; $i < 10; ++$i)
{
    $statement->bind_param("iii",$bigArray[$i][0],$bigArray[$i][1],$bigArray[$i][2]);
    $statement->execute();
}

方法 3 - 優化

準備好的語句和多個插入組合實現的性能幾乎與方法 1 的原始插入查詢相同.實際結果將因您的設置而異，但在我的系統上使用本地和遠程數據庫進行的快速測試顯示了性能使用優化的方法更快幾個百分點，如果需要轉義方法 1 中的數據，則增加幾個百分點.

Prepared statements and multiple inserts combined enable performance which is nearly identical to the raw insert queries of Method 1. Actual results will vary depending on your setup, but a quick test on my system with both a local and a remote database showed performance a few percentage points faster with the optimized method, increasing a few points more if data in Method 1 needs to be escaped.

以下使用call_user_func_array，但如果您知道每次要捆綁多少個插入并直接構建調用bind_param，則可以避免這種情況.這將進一步略微提高性能.

The following uses call_user_func_array, but you could avoid that if you know how many inserts you want to bundle each time and build call bind_param directly. That would further increase performance slightly.

為清楚起見，此示例包括外循環并假設要插入的總行數為 10k(即 bigArray[0..9999][0..2]).

For clarity, this examples includes the outer loop and assumes 10k total lines to be inserted (i.e. bigArray[0..9999][0..2]).

$sql = "INSERT INTO testTable (fieldA,fieldB,fieldC) VALUES (?,?,?)".str_repeat(",(?,?,?)",9);
$statement = $db->prepare($sql);

// This is the type string used by statement::bind_param. 
// Example assumes all INTs.
$types = (array)str_repeat("i",30);

$values = array_fill(0,30,0); // A bit of unneeded variable init.

// * See notes following code snippet on why the intermediate array is used.
$intermediate = array();
for ($n = 0; $n < 30; ++$n)
{
    $intermediate[$n] = &$values[$n];
}

call_user_func_array(array(&$statement, "bind_param"), array_merge($types,$f));

for ($j = 0; $j < 1000; ++$j)
{
    for ($i = 0; $i < 10; ++$i)
    {
        $values[$i*3] = $bigArray[$i][0];
        $values[$i*3+1] = $bigArray[$i][1];
        $values[$i*3+2] = $bigArray[$i][2];
    }
    $statement->execute();
}

// call_user_func_array with bind_param requires the values be 
// passed by reference which is evaluated only on the initial 
// call. Using $values[...] = &$bigArray[...] below won't work
// and an intermediate array referencing $values is used. This 
// bit of "extra funky stuff" can be avoided at a very slight 
// performance penalty by setting $values[...] = $bigArray[...] 
// AND EVALUATING EACH TIME (move call_user_func_array
// inside the outer loop, i.e. right above $statement->execute()).

這篇關于Mysqli多行插入，簡單的多行插入查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！