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()).