2

I'm new to MySQL, but I have reasonable experience with SQL Server. I know in SQL Server I should be very cautious about using MERGE statements because they can lead to some problems. Are there any special concerns I should have when using ON DUPLICATE KEY UPDATE? Is it considered a best practice in the MySQL community to stick with separate INSERT/UPDATE statements like in SQL Server or is it fine to use ON DUPLICATE KEY UPDATE broadly for simple upserts?

1
  • Many flavors of INSERT 'burn' auto-inc ids when the insert is not performed; IIRC, IODKU does that when it updates. – Rick James Dec 6 '16 at 21:42
5

First of all:

  • Yes, it is fine and common practice. For simple upserts, I would say it is best practice.

There are some rather rare cases to worry about and things that may bite you - especially considering that you come from the SQL Server world:

  • When you use it against a table with two or more UNIQUE constraints. In those cases, you can't easily set to have different behaviours for different constraint violations (while it is possible with the MERGE statement).
    An INSERT that violates 1 of the 2 unique constraints will go to the UPDATE clause. If you want different behaviour depending on which constraint is violated, it will be hard to implement (possibly with some complex CASE expressions but may not be feasible in all scenarios).

  • A big difference with SQL Server (and all other major SQL implementations) is that UNIQUE constraints are checked after every row INSERT or UPDATE and not as the SQL standard says at the end of statement (and don't even think about deferred constraint checks).

    The problem with that is for an INSERT ... SELECT ... ON DUPLICATE KEY UPDATE ...; statement, the order of how rows are processed matters. If the SELECT creates 2 rows to be inserted that conflict on a UNIQUE constraint, the first one to be processed will be inserted and the second will fall to the UPDATE clause (and the order of processing will of course depend on the execution plan). This means non-deterministic result on the state of the table after the statement. So one has to be really careful when writing such statements (and any UPDATE statement really).

  • Related to the previous point, INSERT ... ON DUPLICATE KEY UPDATE statements are marked unsafe for statement based replication, so if you use them you are restricted to row or mixed based replication.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.