You are here:
Web Design > MySQL > Multiple Inserts in 1 Query.

Multiple Inserts in 1 Query

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 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:

code:

TABLE: beautiful
=================
id  |name    |age
----+--------+---
    |        |

As usual, the id field is auto-increment and our script will just INSERT multiple values for the name and age columns (fields).

Multiple values in a single INSERT query example

In our sample code, we will INSERT 5 different records using just one MySQL INSERT query:

php:



<?php

//  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!

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).

You are here:
Web Design > MySQL > Multiple Inserts in 1 Query.