What are the practical differences between `REPLACE` and` INSERT ... ON DUPLICATE KEY UPDATE` in MySQL?

Question

I need to set the values ​​of all fields of a record with a specific key (actually it is a composite) by inserting a record if there is no record with that key yet.

REPLACE seems to be intended to do the job, but at the same time, its manual page suggests INSERT ... ON DUPLICATE KEY UPDATE .

Which one is better for me to choose and why?

The only “side effect” of REPLACE that comes to my mind is that it will increase auto-increment values ​​(fortunately, I don't use them), and INSERT ... ON DUPLICATE KEY UPDATE will probably not. What are other practical differences to consider? In what specific cases could REPLACE be preferable to INSERT ... ON DUPLICATE KEY UPDATE and vice versa?

Answer 1

REPLACE internally removes and then inserts. This can cause problems if you have a foreign key constraint pointing to this line. In this situation, REPLACE may fail or worse: if your foreign key is set to cascade delete, REPLACE will delete rows from other tables. This can happen, although the restriction was met both before and after the REPLACE operation.

Using INSERT ... ON DUPLICATE KEY UPDATE avoids this problem and is therefore preferable.

Answer 2

To answer the question in terms of performance, I did a test using both methods

Replace Into includes: 1. Try to paste on the table
2. If 1 fails, delete the line and insert a new line

Paste into Duplicate Key Update includes: 1. Try to paste on the table
2. If 1 fails, update the line

If all steps include inserts, there should be no difference in performance. The speed should depend on the number of updates used. Worst case - when all statements are updated

I tried both statements in my InnoDB table including 62,510 records (updates only). At spreading speeds:
Replace: 77.411 seconds
Insert in renewal 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 Amazon m3.medium

Answer 3

When using REPLACE instead of INSERT ... ON DUPLICATE KEY UPDATE I sometimes observe key lock or deadlock problems when multiple requests arrive quickly for a given key. The atomicity of the latter (in addition to not causing cascading deletions) is all the more justified for its use.

Answer 4

In what specific cases could REPLACE be preferable to INSERT ... ON DUPLICATE KEY UPDATE and vice versa?

I just found that in the case of tables with flags, the INSERT...ON DUPLICATE KEY UPDATE storage INSERT...ON DUPLICATE KEY UPDATE are accepted, but it fails (with error 1022: cannot be written; duplicate key in the table ...) if a duplicate key is violated - see the corresponding mark on this page of the MySQL Reference Guide.

Fortunately, I was able to use REPLACE instead of INSERT...ON DUPLICATE KEY UPDATE inside my insert trigger to achieve the desired result of replicating changes to the FEDERATED table.

Answer 5

Replace it seems that it performs two operations in the event that the key already exists. Perhaps this means that there is a difference in speed between the two?

(INSERT) one update compared to one deletion + one insert (REPLACE)

EDIT: My suggestion that replacement may be slower is actually completely wrong. Well, according to this blog post anyway ... http://www.tokutek.com/2010/07/why-insert-on-duplicate-key-update-may-be-slow-by-incurring -disk-seeks /

Answer 6

If you do not list all the columns, I think REPLACE will reset any non-specified columns with their default values ​​in the replaced rows. ON DUPLICATE KEY UPDATE will leave unchanged columns unchanged.

Answer 7

"It is possible that in the event of an error with duplicate keys, the storage engine may perform REPLACE as an update, rather than inserting delete plus, but the semantics are the same."

http://dev.mysql.com/doc/refman/5.7/en/replace.html