如何使用类似于pdo的mysqli一次插入多个记录

Question

I've been using PDO and the following function to insert multiple records in chunks of 1000 at once. Now I'm working with a system that is using mysqli and I was wondering if I could slightly modify my function to work with mysqli as well however I noticed that mysqli execute doesn't accept an array as a parameter. The following function works perfectly fine and fast with PDO:

$sub_data = array_chunk($data, 1000);
for ($b = 0; $b < count($sub_data); $b++)
{
    $insert_values = array();
    for ($a = 0; $a < count($sub_data[$b]); $a++)
    {
        $insert_values = array_merge($insert_values, array_values($sub_data[$b][$a]));
        $placeholder[] = '(' . implode(', ', array_fill(0, count($sub_data[$b][$a]), '?')) . ')';
    }

    $sql2    = "INSERT INTO $table_name (" . implode(",", array_keys($sub_data[$b][0])) . ") VALUES " . implode(',', $placeholder) . "";
    $prepare = $db->prepare($sql2);
    try
    {
        $prepare->execute($insert_values);
    }
    catch (mysqli_sql_exception $e)
    {
        echo "<pre>";
        print_r($sub_data[$b]);
        echo "</pre>";
        echo $e->getMessage();
        print_r($db->errorInfo());
    }
    unset($insert_values);
    unset($placeholder);
}

Thank you!

Answer 1

You've found one of the bigger gripes people have with the mysqli extension. PDO uses a token binding system where you can pass an array of parameters and keys and PDO will marry them up. Mysqli uses a much more vague binding system which can cause issues when you have an indeterminate number of elements to pass to your array.

A major problem is that mysqli wants to know what type of data to expect as the first argument. If you're not concerned with that level of filtering you could probably skate by just declaring everything a string. If that won't work, add some logic to change between string and integer. To do this, we'll add another parameter to your $insert_values at the start so we can pass the appropriate number of strings in as the first argument

$insert_values = array(0 => '');
for ($a = 0; $a < count($sub_data[$b]); $a++)
{
    $insert_values = array_merge($insert_values, array_values($sub_data[$b][$a]));
    $placeholder[] = '(' . implode(', ', array_fill(0, count($sub_data[$b][$a]), '?')) . ')';
    $insert_values[0] .= 's';
}

$insert_values[0] should now look like ssssss (with the same number of s as elements in your array). I am assuming doing this without refactoring your array_merge, which could cause issues. That string MUST be first because it will become the first argument of the mysqli bind_param call.

We can then use the Reflection class to do the binding

$reflect = new ReflectionClass('mysqli_stmt');
$method = $reflect->getMethod('bind_param');
$method->invokeArgs($prepare, $insert_values);
$prepare->execute();