Problem
What I need is to set the values of all the fields of a record with a particular key (the key is composite actually), inserting the record if there is no record with such a key yet.
REPLACE appears to be the right tool for the job, yet its manual page recommends INSERT… ON DUPLICATE KEY UPDATE.
Which one do you think I should pick, and why?
The only “side impact” that comes to me with REPLACE is that it will increment autoincrement values (which I don’t use), whereas INSERT… ON DUPLICATE KEY UPDATE would most likely not. What additional practical distinctions should you be aware of? When should REPLACE be used instead of INSERT… ON DUPLICATE KEY UPDATE, and when should it be used instead of INSERT… ON DUPLICATE KEY UPDATE?
Asked by Ivan
Solution #1
Internally, REPLACE does a removal followed by an insert. If you have a foreign key constraint pointing at that record, this can cause issues. In this case, the REPLACE may fail, or worse, if your foreign key is set to cascade delete, the REPLACE may result in the deletion of rows from other tables. Even if the constraint was satisfied both before and after the REPLACE operation, this can happen.
Using INSERT … ON DUPLICATE KEY UPDATE avoids this problem and is therefore prefered.
Answered by Mark Byers
Solution #2
In order to respond to the question in terms of performance, I conducted a test utilizing both methodologies.
Replace into entails the following steps: 1.Try inserting on the table 2. If step 1 fails, delete the row and replace it with a new one. Update row if insert on duplicate key fails: 1.Try insert on table 2.If insert on table fails, update row There should be no change in performance if all of the steps are performed. The speed will be determined on the quantity of updates. The worst-case scenario is that all of the statements are updated.
I tried both statements on a table with 62,510 items in InnoDB (only updates). In terms of camparing speeds: 77.411 seconds to replace Update: 2.446 seconds Insert on Duplicate Key
Insert on Duplicate Key update is almost 32 times faster.
On an Amazon m3.medium table, there are 1,249,250 rows and 12 columns.
Answered by katrix
Solution #3
When REPLACE is used instead of INSERT… When numerous queries arrive quickly for a given key, I occasionally experience key locking or deadlock difficulties on DUPLICATE KEY UPDATE. The latter’s atomicity (together with the fact that it does not cause cascade deletes) is all the more motivation to employ it.
Answered by Andrew Mao
Solution #4
I’ve just found out the hard way that in the case of tables with a FEDERATED storage engine INSERT…ON DUPLICATE KEY UPDATE statements are accepted, but fail (with an Error 1022: Can’t write; duplicate key in table…) If a duplicate-key violation occurs, read the MySQL Reference Manual’s related bullet point on this page.
Fortunately, I was able to achieve the intended result of replicating changes to a FEDERATED table by using REPLACE instead of INSERT…ON DUPLICATE KEY UPDATE within my after insert trigger.
Answered by w5m
Solution #5
If you don’t list all of the columns, I believe REPLACE will replace any unmentioned columns in the substituted rows with their default values. Unmentioned columns will be left untouched when using ON DUPLICATE KEY UPDATE.
Answered by Barmar
Post is based on https://stackoverflow.com/questions/9168928/what-are-practical-differences-between-replace-and-insert-on-duplicate-ke