Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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

NickFNickF USAPosts: 132Member

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?

Tagged:

Comments

  • dr_itondr_iton Posts: 4Member

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

Sign In or Register to comment.