How to do a batch insert in MySQL?
How to do a batch insert in MySQL?
In this tutorial, I will present you how to do a batch insert in mysql. Let’s say you want to insert some informations from an array into the database. In order for this to work efficient, you should have more than one information in that array.
Why batch insert ?
Because calling insert statements line after line is not the best solution when it comes to performance. You slow down you mysql server and the app will go slow.
How to ?
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:
|
1 |
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); |
More practicaly ?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$qId = '3'; $users = array( 0 => 'Andrei', 1 => 'Robert', 2 => 'Bogdan', 3 => 'Marius' ); $length = count($users); $sql = "INSERT INTO questions (`id_question`, `id_user`) VALUES "; for($i=0; $i<$length; $i++) { if( $i === 0 ) { $sql .= " ('$qId', '$users[$i]')"; } else { $sql .= " ,('$qId', '$users[$i]')"; } } $sql .= ';'; |
Explain ?
We have a static id called “qId” which should be inserted and a dynamic array containing names that should be inserted with that id also. We construct a for loop in which we parse out “$users” array and we concatenate to our initial sql statement all the names from within that array. Why do we need the if inside ? Because for the first position, we don’t need a comma in front, only for the other ones, starting from the second position.
Result ?
Our query will result into this one:
|
1 |
INSERT INTO questions (`id_question`, `id_user`) VALUES ('3', 'Andrei') ,('3', 'Robert') ,('3', 'Bogdan') ,('3', 'Marius'); |
This is a very fast and productive way to do bulk insert in mysql.
