What is the difference between “INSERT IGNORE” and “INSERT … ON DUPLICATE KEY UPDATE” in MySQL?

Question

What is the difference between INSERT IGNORE and INSERT…ON DUPLICATE KEY UPDATE. I would also like to know which one is preferred over the other. Can someone please help?

Answer 1

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

•Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.

•Inserting a NULL into a column with a NOT NULL constraint.

•Inserting a row to a partitioned table, but the values you insert don't map to a partition.

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

•A new auto-increment ID is allocated.

•Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.

•Triggers that fire on DELETE are executed unnecessarily.

•Side effects are propagated to replication slaves too.

Both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.

Answer 2

If you try to avoid duplication violation, then the difference is: INSERT IGNORE does not allow to specify an action (to alter something in a row) if unique constraint violation occures whereas for INSERT...ON DUPLICATE KEY UPDATE some action specifying is compulsory. Of course, you can specify fake update in INSERT...ON DUPLICATE KEY UPDATE (for example, SET id=id, where id is primary/unique key) - in this case there is no difference.

INSERT IGNORE also may ignore some another ignorable errors, rather than INSERT...ON DUPLICATE KEY UPDATE.