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.
