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

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


  • 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.

    I hope this helps you.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion