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

?>