批量插入

Question

I have the following code which is working fine, But I need a way to batch process it and Insert it all in one go.

for($i=0; $i<sizeof($my_array); $i++)
        {

            $sql = "INSERT INTO exclude_resource (id,resource_id,config_id)
                    VALUES(DEFAULT, '$my_array[$i]' ,'$insert_id')";

            $command = $connection->createCommand($sql);
            $result = $command->execute();
        }

Answer 1

Your query should resemble something like (see mysql docs):

INSERT INTO table (field1, field2, field3)
VALUES
    (value1a, value2a, value3a),
    (value1b, value2b, value3b),
    (value1c, value2b, value3c),
    ...

So put the values in an array, join them with commas, and execute the resulting query. Incorporated in PHP:

$values = array();
for($i=0; $i<sizeof($my_array); $i++) {
    $values[] = "(DEFAULT, '$my_array[$i]' ,'$insert_id')";
}

$sql = 
    "INSERT INTO exclude_resource (id,resource_id,config_id) VALUES '.
    join(',', $values);
$command = $connection->createCommand($sql);
$result = $command->execute();

$insert_id is needs to have a value, but thats the same with your code snippet.

If you have more than 5k or 10k rows to insert, you should run an INSERT in between and reset the array.