221

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*'
Improve this question
1

2 Answers 2

Reset to default
462

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Right on, good answer. INSERT IGNORE is bad news!
@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.
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: dev.mysql.com/doc/refman/5.7/en/…
@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".
@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.
|
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

Improve this answer

8 Comments

Dood Im gonna need you to elaborate on that one?
Replace will DELETE the conflicting rows before replacing them. If you have foreign keys, pointing to the deleted rows you are in trouble.
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)..
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.
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.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.