What are practical differences between `REPLACE` and `INSERT ... ON DUPLICATE KEY UPDATE` in MySQL?
Solution 1
REPLACE
internally performs a delete and then an insert. This can cause problems if you have a foreign key constraint pointing at that row. In this situation the REPLACE
could fail or worse: if your foreign key is set to cascade delete, the REPLACE
will cause rows from other tables to be deleted. This can happen even though the constraint was satisfied both before and after the REPLACE
operation.
Using INSERT ... ON DUPLICATE KEY UPDATE
avoids this problem and is therefore prefered.
Solution 2
To answer the question in terms of performance, I did a test using both the methods
Replace Into involves:
1.Try insert on the table
2. If 1 fails, delete row and insert new row
Insert on Duplicate Key Update involves:
1.Try insert on table
2.If 1 fails, update row
If all the steps involved are inserts, there should be no difference in performance. The speed has to depend on the number of updates involved. Worst case is when all the statements are updates
I have tried both the statements on my InnoDB table involving 62,510 entries (only updates). On camparing speeds:
Replace Into: 77.411 seconds
Insert on Duplicate Key Update: 2.446 seconds
Insert on Duplicate Key update is almost 32 times faster.
Table Size: 1,249,250 rows with 12 columns on an Amazon m3.medium
Solution 3
When using REPLACE
instead of INSERT ... ON DUPLICATE KEY UPDATE
, I sometimes observe key locking or deadlock problems when multiple queries arrive quickly for a given key. The atomicity of the latter (in addition to not causing cascade deletes) is all the more reason to use it.
Solution 4
In what particular cases can REPLACE be preferred over INSERT ... ON DUPLICATE KEY UPDATE and vice versa?
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 - see corresponding bullet point on this page of the MySQL Reference Manual.
Fortunately, I was able to use REPLACE
instead of INSERT...ON DUPLICATE KEY UPDATE
within my after insert trigger to achieve the desired outcome of replicating changes to a FEDERATED table.
Solution 5
If you don't list all the columns, I think REPLACE
will reset any unmentioned columns with their default values in the replaced rows. ON DUPLICATE KEY UPDATE
will leave unmentioned columns unchanged.
Ivan
Updated on September 13, 2020Comments
-
Ivan over 3 years
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
seems as meant to do the job, but at the same time its manual page suggestsINSERT ... ON DUPLICATE KEY UPDATE
.What of them should I better choose and why?
The only "side effect" of
REPLACE
that comes into my mind is that it would increment autoincrement values (fortunately I don't use any) whileINSERT ... ON DUPLICATE KEY UPDATE
probably wouldn't. What are the other practical differences to take in mind? In what particular cases canREPLACE
be preferred overINSERT ... ON DUPLICATE KEY UPDATE
and vice versa? -
Ivan over 12 yearsGood answer, but in the actual case of mine this problem is not going to be met. The chance of collision can though be considered 50/50. What should I choose then? And as
INSERT ... ON DUPLICATE KEY UPDATE
looks considerably "better" then in what particular cases can "REPLACE" be a better choice? -
Nathan Stretch almost 11 yearsI've done a fair bit of research and as far as I can tell, there is no common reason to use REPLACE instead of INSERT ... ON DUPLICATE KEY UPDATE. It's essentially a legacy feature. Unless there's some particular reason why your code relies on rows being deleted and re-added, with the associated effects on indexes and auto-increment values, there doesn't appear to be any reason to use it.
-
radtek almost 7 yearsOn
REPLACE
will update your PK auto-increment value if it does aDELETE
andINSERT
. Which is exactly what I want. I do not want the consumer to find the record under the same PK, so they get no rows. When I want them to find it (actual update), I useUPDATE
-
radtek almost 7 yearsCool stats, did you try
Insert on Duplicate Key Replace
? Was it slower? -
LemonPi almost 6 yearsSo the other half of the question: when would you prefer
REPLACE
overINSERT ... ON DUPLICATE KEY UPDATE
? Why would anINSERT
+DELETE
ever be preferred over anUPDATE
? -
izogfif over 3 yearsBeware:
INSERT IGNORE
query will finish successfully (and will issue a warning) if a foreign constraint fails! If you want to catch an error like this, better useON DUPLICATE KEY UPDATE
withoutIGNORE
. -
izogfif over 3 years@radtek you can only write
ON DUPLICATE KEY UPDATE
, you can't writeON DUPLICATE KEY REPLACE
. If you want to update all values of existing row upon duplicate key, you have to writeON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), ...
- you have to list all columns manually. -
radtek over 3 yearsI know I was just asking what was faster and looks like update is.
-
Payel Senapati over 2 yearswhat is the purpose of
REPLACE
command then if it is so useless and only causes problems? Shouldn't this feature be depreciated then?