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

If you use INSERT..IGNORE, errors that occur while executing the INSERT statement are treated as warnings instead. One thing which is not explicitly mentioned is that INSERT..IGNORE will cause invalid values will be adjusted to the closest values when inserted (whereas invalid values would cause the query to abort if the IGNORE keyword was not used).

Something important to add: When using INSERT IGNORE and you do have key violations, MySQL does NOT raise a warning! If you try for instance to insert 100 records at a time, with one faulty one, you would get in interactive mode:

Query OK, 99 rows affected (0.04 sec)
Records: 100  Duplicates: 1  Warnings: 0

As you see: No Warnings! This behavior is even wrongly described in the official MySQL Documentation. If your script needs to be informed, if some records have not been added (due to key violations) you have to call mysql_info() and parse it for the "Duplicates" value.

ON DUPLICATE KEY UPDATE is not really in the standard. It's about as standard as REPLACE is. See SQL_MERGE

If you use both INSERT IGNORE and ON DUPLICATE KEY UPDATE in the same statement, the update will still happen if the insert finds a duplicate key. In other words, the update takes precedence over the ignore. However, if the ON DUPLICATE KEY UPDATE clause itself causes a duplicate key error, that error will be ignored.

This can happen if you have more than one unique key, or if your update attempts to violate a foreign key constraint

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.

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.