MySQL: INSERT IF NOT EXISTS syntax
18th October 2007
To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality.
There are two possible solutions: using INSERT IGNORE or REPLACE.
Imagine we have a table:
-
CREATE TABLE `transcripts` (
-
`ensembl_transcript_id` varchar(20) NOT NULL,
-
`transcript_chrom_start` int(10) UNSIGNED NOT NULL,
-
`transcript_chrom_end` int(10) UNSIGNED NOT NULL,
-
PRIMARY KEY (`ensembl_transcript_id`)
-
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now imagine that we have an automatic pipeline importing transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things: 1) repeated executions of the pipeline will not destroy our database, and 2) repeated executions will not die due to 'duplicate primary key' errors.
Method 1: using REPLACE
It's very simple:
-
REPLACE INTO `transcripts`
-
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
-
`transcript_chrom_start` = 12345,
-
`transcript_chrom_end` = 12678;
If the record exists, it will be overwritten; if it does not yet exist, it will be created.
However, using this method isn't efficient for our case: we do not need to overwrite existing records, it's fine just to skip them.
Method 2: using INSERT IGNORE
Also very simple:
-
INSERT IGNORE INTO `transcripts`
-
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
-
`transcript_chrom_start` = 12345,
-
`transcript_chrom_end` = 12678;
Here, if the 'ensembl_transcript_id' is already present in the database, it will be silently skipped (ignored). (To be more precise, here's a quote from MySQL reference manual: "If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.".) If the record doesn't yet exist, it will be created.
This second method has several potential weaknesses, including non-abortion of the query in case any other problem occurs (see the manual). Thus it should be used if previously tested without the IGNORE keyword.
There is one more option: to use INSERT ... ON DUPLICATE KEY UPDATE syntax, and in the UPDATE part just do nothing do some meaningless (empty) operation, like calculating 0+0 (Geoffray suggests doing the id=id assignment for the MySQL optimization engine to ignore this operation). Advantage of this method is that it only ignores duplicate key events, and still aborts on other errors.
As a final notice: this post was inspired by Xaprb. I'd also advise to consult his other post on writing flexible SQL queries.
April 20th, 2008 at 8:19
[...] may also find http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html good reading on the [...]
June 23rd, 2008 at 14:09
Very interesting, and helpful.
June 24th, 2008 at 9:34
thanks
July 13th, 2008 at 12:53
Thanks!
July 22nd, 2008 at 12:38
Thank you very much!!
August 4th, 2008 at 13:41
Very useful, thank you! How about insert, update and delete in a single query, it's possible? Something like:
insert ignore into table (field_one, field_two) values (values_one, values_two) on duplicate key update field_one = values(values_one), field_two = values(values_two) THEN QUERY FOR DELETE HERE?
Thanks
August 4th, 2008 at 19:25
Teo,
As I didn't do anything similar before, I can't offer you a ready working solution.
But you should just try your query
. The part before THEN should work (unless "insert ignore" is not compatible with "on duplicate key"). For THEN-part, I suspect different syntax might be needed - look for MySQL conditional statements IF-THEN-ELSE.
August 28th, 2008 at 14:39
About your last option, I don't think it's possible to do nothing after the "ON DUPLICATE KEY UPDATE". What you can do is updating the column (PRIMARY KEY or/and UNIQUE) which generate a duplicate entry error with it's own value. I think MySQL will ignore the update because these values are similar, so it could also be a quite fast solution.
INSERT INTO `test`(id) VALUES (1) ON DUPLICATE KEY UPDATE id=idAugust 30th, 2008 at 16:21
Geoffray,
if the MySQL optimization engine really does that, and if this is allowed (remember, we are triggering the ON DUPLICATE KEY event, and then trying to assign that key's value to another value, even if it is the same), then this is a really good solution.
Based on your comment, I updated the post, as MySQL syntax in fact doesn't allow the empty statement after ON (...) UPDATE.
September 29th, 2008 at 16:04
Would this work on InnoDB with composite keys as well? My task is to read only new entries from a log and insert them into a MySQL table. The table has a composite key.
(I could try this myself, but I have no MySQL box to test on for the next few weeks.)
September 29th, 2008 at 21:55
Marius,
neither MySQL documentation, nor my (not so extensive) experience with MySQL raise any support for disabling described functionality for composite keys. As I understand, composite key is still a single key, and behaves as such. And MySQL documentation sometimes mentions that composite keys are just concatenated respective fields (columns).
Actually, it might be that I did apply the REPLACE or IGNORE approach to a composite-keyed table (and it was InnoDB at that time, now for that project I'm using MyISAM to save space). But I'm unsure - didn't check the code since it was written (now almost a year ago).
October 29th, 2008 at 11:24
thanks. it is very useful. Sometimes we want to send pop-up messagebox when we changed or ignord the same record. Your solutions are good but I want to know how to inform user that we changed or ignored the record. For example: "Customer couldn't be added. Because the customer you entered is already exists." So how can I determine whether users attempt is executed or ignored?
October 29th, 2008 at 13:01
Cakirhal,
first of all, you will need to consult MySQL documentation for the exact queries you are using - the "returned rows"/"affected rows" count might be helpful.
Also, before executing the actual modification query, you can execute selection query (i.e. "SELECT ... FROM") in your program, to know for sure if the customer exists. After that, it is a trivial task to notify the user of any problems.
January 29th, 2009 at 16:41
Thank you for the concise explanation.
June 24th, 2009 at 19:33
thank you for your help, you are the best
June 24th, 2009 at 22:09
Thanks, this article is very helpful!