If your MySQL version is 3.22.5 or later, you can now INSERT multiple values / records in one single query. In most cases this is probably not useful but I recently found this out while trying to optimise my GIDTopsites script.
I had a function in my PHP script that empties a given MySQL table daily and automatically populates the empty table with a summary of ‘stats’ with data off another database table. Though not quite the same, my sample below will give you a vague idea how I eventually optimised my script using multiple inserts. First, a sample empty table to start with:
TABLE: beautiful ================= id | name | age ----+--------+--- | |
As usual, the id field is an auto-increment field and our PHP script will just INSERT multiple values for the name and age columns (fields).
Multiple values in a single INSERT query example
In our sample PHP code below, we will INSERT 5 different records using just one MySQL INSERT query:
// Our MySQL query $sql = "INSERT INTO beautiful (name, age) VALUES ('Helen', 24), ('Katrina', 21), ('Samia', 22), ('Hui Ling', 25), ('Yumie', 29)"; mysql_query( $sql, $conn );
That’s it! Assuming you had a MySQL table named
`beautiful` all set up, you could save the sample script above and run it; and each run of the script will insert the five records at the same time every time!
Like I said, it’s wonderful to use something like this when you fill-up a certain MySQL table with (a summary of) data off other tables or an array.
The MySQL documentation randomly states this, under the Other Optimization Tips:
Use the multi-line INSERT statement to store many rows with one SQL command (many SQL servers supports this).