204

I have a table with a unique key for two columns:

CREATE  TABLE `xpo`.`user_permanent_gift` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`fb_user_id` INT UNSIGNED NOT NULL ,
`gift_id` INT UNSIGNED NOT NULL ,
`purchase_timestamp` TIMESTAMP NULL DEFAULT now() ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `user_gift_UNIQUE` (`fb_user_id` ASC, `gift_id` ASC) );

I want to insert a row into that table, but if the key exists, to do nothing! I don't want an error to be generated because the keys exist.

I know that there is the following syntax:

INSERT ... ON DUPLICATE KEY UPDATE ...

but is there something like:

INSERT ... ON DUPLICATE KEY DO NOTHING

?

Mark Amery
  • 110,735
  • 57
  • 354
  • 402
ufk
  • 26,596
  • 55
  • 202
  • 346

3 Answers3

360

Yes, use INSERT ... ON DUPLICATE KEY UPDATE id=id (it won't trigger row update even though id is assigned to itself).

If you don't care about errors (conversion errors, foreign key errors) and autoincrement field exhaustion (it's incremented even if the row is not inserted due to duplicate key), then use INSERT IGNORE.

izogfif
  • 3,166
  • 2
  • 27
  • 21
ceejayoz
  • 165,698
  • 38
  • 268
  • 341
  • 5
    just to add IGNORE after the INSERT, rest of syntax is the same ? – ufk Jan 04 '11 at 17:17
  • 25
    @ufk: `INSERT IGNORE` without the `ON DUPLICATE KEY` part, e.g. `INSERT IGNORE INTO xpo.user_permanent_gift (...) VALUES (...)` – BoltClock Jan 04 '11 at 17:18
  • 128
    Note that INSERT IGNORE also ignores other errors such as data conversion failures. – mjcopple Jan 04 '11 at 17:27
  • 38
    so i'll use ON DUPLICATE KEY UPDATE id=id. i want only to ignore key duplicates not any other kind of error. – ufk Jan 05 '11 at 09:22
  • 1
    One technique you could use would be to try inserting it first, and then if an error occurs, log the error and do a second `INSERT IGNORE` query. – redolent Sep 03 '14 at 22:38
  • 1
    Which one is faster, `INSERT IGNORE` or `ON DUPLICATE KEY UPDATE id=id`? – FBB May 06 '15 at 08:38
  • @FBB Likely `INSERT IGNORE` (as it requires no update to the row), but you'd want to benchmark. – ceejayoz May 06 '15 at 13:55
  • 1
    It was a simple benchmarking but based on this [source](http://www.eddieoneverything.com/programming/mysql-performance-tip-on-duplicate-key-is-faster-than-insert-ignore-but-failing-with-a-duplicate-key-error-is-a-lot-faster.php), `ON DUPLICATE KEY UPDATE id=id` is faster. – jagc Feb 01 '16 at 07:25
  • My problem (in perl land with DBH) is that UPDATE id=id looks like an affected row, so I can't tell the difference between a genuine insert and a safely avoided collision. Anyone got a notion on how to count only the new rows? – Joshua Eric Turcotte Oct 14 '16 at 18:47
  • @JoshuaEricTurcotte http://dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html – ceejayoz Oct 14 '16 at 19:17
  • 1
    id=id reports no rows affected (mysql 5.6), so it seem it is being handled intelligently – Jordan Morris May 16 '18 at 04:33
  • 8
    beware INSERT IGNORE will increment the auto-increment column even if the row is not inserted – WorM Nov 20 '18 at 11:01
  • 1
    Can also confirm INSERT IGNORE in some situations will increment the AUTO_INCREMENT value even though the ID was never actually used due to the duplicate and the IGNORE. – Jon Marnock Feb 11 '19 at 04:03
  • Preferably don't use INSERT IGNORE, because (a) it still generates and logs the errors messages, simply downgrading them to warnings, and (b) it affects more than just unique/primary key clashes. It's semantically more like an error-suppression clause than what you actually want, which is "INSERT if no matching row already exists". Shame that there's no actual "ON DUPLICATE KEY DO NOTHING" syntax. – Doin Aug 26 '19 at 12:42
  • @Doin `ON DUPLICATE KEY 1=1` works fine, I believe. – ceejayoz Aug 26 '19 at 12:53
  • @ceejayoz what you just wrote gives a syntax error: First, you left out the word `UPDATE`, and secondly it expects one or more assignments to table columns, and `1=1` isn't a valid assignment. But yes, nitpicking aside, what I was trying to say was "Preferably use the 2nd option from this answer rather than the first one". Despite the awkward and unintuitive syntax, the `ON DUPLICATE KEY UPDATE column=column` method is *the only semantically correct way* to achieve the desired result. – Doin Aug 26 '19 at 16:25
  • `INSERT IGNORE` is OK for when you're entering data directly into the database yourself (on a command line or similar), or in other situations when rough-and-ready data insertion with minimum fuss is all you need. But as part of a DB-based application, it's *very, very* rarely what you really want: you should think carefully before using it instead of the alternative(s). (Note also the `REPLACE INTO` command, which has a slightly different but related functionality). – Doin Aug 26 '19 at 16:37
  • If `id` is a string, and your collation is case-insensitive, I think this has potential of changing the case when there's a duplicate. – mpen Dec 01 '19 at 02:46
  • 2
    @mpen No, it doesn't work that way: The `UPDATE` part of the statement applies to the existing row only, so `column=column` sets `column` *to its existing value*, i.e. no change. If you *wanted* to update it to the new value (allowing e.g., a change of case for an id column), then you'd use either `column=VALUES(column)`, or since MySQL v8.0.19 `column=new.column` where "new" is an alias you've given your to-be-inserted row(s). See https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html. – Doin Mar 22 '20 at 11:40
  • This answer seems to imply that INSERT IGNORE causes autoincrement while ON DUPLICATE KEY does not. They both cause autoincrement. – DAG Aug 22 '20 at 14:31
19

HOW TO IMPLEMENT 'insert if not exist'?

1. REPLACE INTO

pros:

  1. simple.

cons:

  1. too slow.

  2. auto-increment key will CHANGE(increase by 1) if there is entry matches unique key or primary key, because it deletes the old entry then insert new one.

2. INSERT IGNORE

pros:

  1. simple.

cons:

  1. auto-increment key will not change if there is entry matches unique key or primary key but auto-increment index will increase by 1

  2. some other errors/warnings will be ignored such as data conversion error.

3. INSERT ... ON DUPLICATE KEY UPDATE

pros:

  1. you can easily implement 'save or update' function with this

cons:

  1. looks relatively complex if you just want to insert not update.

  2. auto-increment key will not change if there is entry matches unique key or primary key but auto-increment index will increase by 1

4. Any way to stop auto-increment key increasing if there is entry matches unique key or primary key?

As mentioned in the comment below by @toien: "auto-increment column will be effected depends on innodb_autoinc_lock_mode config after version 5.1" if you are using innodb as your engine, but this also effects concurrency, so it needs to be well considered before used. So far I'm not seeing any better solution.

Kim
  • 3,985
  • 4
  • 29
  • 53
  • auto-increment column will be effected depends on `innodb_autoinc_lock_mode` config after version 5.1 – toien Mar 10 '20 at 07:40
1

Use ON DUPLICATE KEY UPDATE ...,
Negative : because the UPDATE uses resources for the second action.

Use INSERT IGNORE ...,
Negative : MySQL will not show any errors if something goes wrong, so you cannot handle the errors. Use it only if you don’t care about the query.

Abdul Aziz Al Basyir
  • 844
  • 8
  • 23