If you are familiar with the MySQL INSERT syntax you’ve probably already noticed the ability to insert multiple rows with one call:
[sourcecode lang=”sql”]
INSERT INTO table_name (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9);
[/sourcecode]
This can be handy if you have several rows that you need to insert, no point in making separate calls for each one.
Another nice feature is the ON DUPLICATE syntax, which allows for:
[sourcecode lang=”sql”]
INSERT INTO table_name (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c = c + 1;
[/sourcecode]
If you have a unique index on the table, then ON DUPLICATE will kick in if there is a conflict with the unique on insert. Without ON DUPLICATE you’d generally do a SELECT first to see if the value was already in the table, then either do an INSERT or an UPDATE based on the response.
What you may not have noticed is that you can combine the multi-row insert and ON DUPLICATE features to do:
[sourcecode lang=”sql”]
INSERT INTO table_name (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9)
ON DUPLICATE KEY UPDATE c = c + 1;
[/sourcecode]
Where might you use something like this? If you were looping over data and inserting using ON DUPLICATE inside the loop you could instead build the SQL string inside the loop and then issue a single database request after the loop.
Exactly what sort of savings you’ll see depends on a number of factors ( number of iterations in your loop, latency for database requests, etc. ), so run your own tests to determine if this will help for your particular situation.