一次运行多个mysql命令而不是在代码点火器中顺序执行？

Question

So I am working on an existing codebase and the function takes each row from a csv, formats the data and then inserts it into the db. This whole process takes about ~20 minutes with the rows in the CSV. However if I keep everything the same and remove the line that actually does the database entry

$this->db->replace('my_table', $myObject);

The whole code runs in about 2 seconds. So I am wondering is it possible to store all of the objects and then assign them all to the DB at once? I am thinking of doing 1 sql call instead of thousands and thousands. Thoughts ?

Answer 1

Have you looked into insert_batch() ? See Documentation here.

You can parse your csv and build an array like this

$insert_array = array();
foreach($myCSV as $item)
{
     $insert_array[] = array('db_field1' => $item->value1, 
                             'db_field2' => $item->value2);
}

$this->db->insert_batch('mytable', $insert_array);

This will run a query whitch looks like this :

INSERT INTO mytable (db_field1, db_field2) VALUES ('item1value1', 'item1value2'),  ('item2value1', 'item2value2');