MySQL Bulk Insert or Update

If you’re not familiar with the MySQL Insert or Update statement, check it out before reading on. It’s essentially a way to leverage a database table’s primary key to reduce the number of queries required to insert or update a record, by eliminating the need to perform a select query first.

Many aren’t aware that this technique can also be applied in bulk queries. Okay I admit, I didn’t know until today!

By combining MySQL’s Bulk Insert syntax with the Insert or Update syntax, you are able to reduce a very large number of queries and statements down to one single statement.

Let’s using the following cross-reference table as an example. Assuming we have a users table and a preferences table. The user_preference table provides a many-to-many cross-reference table between the two.

user_preference
user_id INT PRIMARY_KEY
preference_id INT PRIMARY_KEY
pref_value VARCHAR(16)

Let’s say we wanted to assign group of user’s a value of ‘true’ to preference_id 1. We can do that in a single query as seen in the example below:

INSERT INTO user_preference (user_id, preference_id, pref_value) 
VALUES 
(1, 1, 'true'), (2, 1, 'true'), (3, 1, 'true')
ON DUPLICATE KEY UPDATE
value = 'true'

This is only the tip of the iceberg though. The Insert or Update syntax also allows you to use the VALUES() function to access the group of values that would be inserted had they not already existed.

This comes in handy if each row will have a different value for non-key fields, as seen below:

INSERT INTO user_preference (user_id, preference_id, value) 
VALUES 
(1, 1, 'true'), (2, 1, 'true'), (3, 1, 'false')
ON DUPLICATE KEY UPDATE
pref_value = VALUES(pref_value)

Let’s wrap this up with some quick PHP code to demonstrate one way to put a bulk Insert or Update query together, given an array of users.

Please note, I have left out all sql-injection prevention code for clarity.

$sql = 'INSERT INTO user_preference
        (user_id, preference_id, pref_value) 
        VALUES ';

 $userCount = count($users);

 foreach ($users as $key => $user) {
     $sql .= $user['user_id'] . ', 1, true)';
     if (($key + 1) < $userCount) $sql .= ', ';
 }

 $sql .= ' ON DUPLICATE KEY UPDATE 
           pref_value = VALUES(pref_value)';