211

I'm trying to finish this query; my tag field is set to UNIQUE and I simply want the database to ignore any duplicate tag. 

INSERT INTO table_tags (tag) VALUES ('tag_a'),('tab_b'),('tag_c')
ON DUPLICATE KEY IGNORE '*the offending tag and carry on*'

or even this would be acceptable

INSERT INTO table_tags (tag) VALUES ('tag_a'),('tab_b'),('tag_c')
ON DUPLICATE KEY UPDATE '*the offending tag and carry on*'
halfer
  • 19,824
  • 17
  • 99
  • 186
CodeChap
  • 4,132
  • 6
  • 30
  • 40
  • 1
    See also: [“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”](http://stackoverflow.com/q/548541/1402846). – Pang Apr 07 '13 at 11:17

2 Answers2

439

Would suggest NOT using INSERT IGNORE as it ignores ALL errors (ie its a sloppy global ignore). Instead, since in your example tag is the unique key, use:

INSERT INTO table_tags (tag) VALUES ('tag_a'),('tab_b'),('tag_c')
ON DUPLICATE KEY UPDATE tag=tag;

on duplicate key produces:

Query OK, 0 rows affected (0.07 sec)

yivi
  • 42,438
  • 18
  • 116
  • 138
thummper
  • 4,414
  • 2
  • 15
  • 2
  • 12
    Right on, good answer. INSERT IGNORE is bad news! – Boundless Sep 11 '12 at 20:36
  • 2
    @Boundless It really does depend, for example, I have a table that contains categories for a particular data entry in the database. On changes to these categories I would use an INSERT IGNORE containing all of these categories rather than testing to see the differences between these categories. – Jay Sep 25 '13 at 16:35
  • 6
    Warning: the `ON DUPLICATE KEY UPDATE foo=foo;` will increase your chances of getting deadlocks, because it will additionally hold Next-Key lock on your indexes. More: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks – Dzmitry Lazerka Mar 14 '17 at 07:13
  • 1
    @DzmitryLazerka Do you have any more information on this and whether it is still relevant? I've never experienced deadlocks in this case personally, and I'm skeptical that it would cause a deadlock when there are "0 rows affected". – Code Commander Jul 13 '17 at 18:35
  • 1
    @CodeCommander Yes, we had the deadlocks due to this on Google Cloud SQL. But you won't get deadlocks only because of using ON DUPLICATE KEY UPDATE query, you also need some other "cooperating" queries to get the deadlock. – Dzmitry Lazerka Jul 14 '17 at 19:51
  • A little more official information can be found at https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html (search for `ON DUPLICATE`) – Dzmitry Lazerka Jul 14 '17 at 20:01
  • 1
    Good point! It also introduces other problems like inserting WRONG values into the columns of "wrong" datatype. See examples here: https://medium.com/legacy-systems-diary/things-to-avoid-episode-1-insert-ignore-535b4c24406b – 0x49D1 Oct 11 '19 at 08:43
  • @thummper what is the functional difference of that to an `INSERT IGNORE`? In the end, both versions will ignore all uniqueness errors and not change any row? – Benni Nov 01 '22 at 14:48
21

Mysql has this handy UPDATE INTO command ;)

edit Looks like they renamed it to REPLACE

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168
  • 1
    Dood Im gonna need you to elaborate on that one? – CodeChap Mar 02 '10 at 21:19
  • Thanks for your help and time, Ivan Nevostruev solution seem to do the trick for me – CodeChap Mar 02 '10 at 21:27
  • 109
    Replace will DELETE the conflicting rows before replacing them. If you have foreign keys, pointing to the deleted rows you are in trouble. – nakhli Aug 07 '12 at 20:53
  • 7
    And You have to take into account potential performance hit. Instead of one read and potential one write You get read, write (delete row), write (insert row) and write(update index), so 1x read and 3x write (at best, if only one index is updated).. – matt Oct 18 '12 at 08:23
  • 1
    It's good to know about REPLACE. However another issue with using REPLACE is that if the row has an auto-incrementing primary key, and the REPLACE query is run many times, the primary key's increment will increase every time the query is run. – Erik Hansen Sep 15 '16 at 21:33
  • 4
    I would highly recommend avoiding this command at all costs. Performance wise, it totally sucks. Not sure why they even make this thing an option. The delete command takes resources, then you have to edit all the indexes, the unique ones taking the longest. I had code using this for a while and when I migrated to Insert ... On duplicate key update - the performance shot waaaaay up. Also, this command will eventually burn out all your auto increment primary keys prematurely. – photocode Feb 17 '17 at 17:40
  • Side-note: You need both write and also delete privileges for replace into to work – Oliver M Grech Jan 11 '19 at 13:11
  • It is easy to loose integrity as replace deletes entire row along with the IDs that might be auto increment . Avoid. – Chris Koston Dec 03 '19 at 21:38