在 MySQL 中，‘ REPLACE’和‘ INSERT... ON DUPLICATE KEY UPDATE’有什么实际的区别？

我需要的是用一个特定的键设置一个记录的所有字段的值(实际上这个键是复合的) ，如果还没有具有这样一个键的记录，则插入这个记录。

REPLACE 似乎是要做这项工作，但同时它的手册页建议 INSERT ... ON DUPLICATE KEY UPDATE .

我应该选择哪一个? 为什么？

我想到的唯一的“副作用”是 REPLACE会增加自增量值(幸运的是我没有使用任何值) ，而 INSERT ... ON DUPLICATE KEY UPDATE可能不会。还有什么其他的实际差异需要考虑？在哪些特殊情况下，REPLACE可能优于 INSERT ... ON DUPLICATE KEY UPDATE，反之亦然？

43116 次浏览
小开

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.

小开

Replace seems that it does two operations in the case that the key already exists. Perhaps that implies there is a speed difference between the two?

(INSERT)one update vs one delete + one insert(REPLACE)

EDIT: My implication that replace might 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/

小开

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.

小开

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.

小开

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.

小开

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

小开

"It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same."

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

小开

REPLACE seems to be necessary sometimes because INSERT IGNORE doesn't seem to work with data transformations.

If I do this, I only set largestCityPop to itself:

INSERT IGNORE INTO largestCities (stateID, largestCityPop, statePop) SELECT stateID, MAX(city.pop) as largestCityPop, state.pop FROM city JOIN state on city.stateID = state.ID GROUP BY city.stateID ON DUPLICATE KEY UPDATE largestCityPop = largestCityPop

If I do this, I am using the GROUP function improperly:

INSERT IGNORE INTO largestCities (stateID, largestCityPop, statePop) SELECT stateID, MAX(city.pop) as largestCityPop, state.pop FROM city JOIN state on city.stateID = state.ID GROUP BY city.stateID ON DUPLICATE KEY UPDATE largestCityPop = MAX(city.pop)

And if I do this, MySQL won't recognize the column name:

INSERT IGNORE INTO largestCities (stateID, largestCityPop, statePop) SELECT stateID, MAX(city.pop) as largestCityPop, state.pop FROM city JOIN state on city.stateID = state.ID GROUP BY city.stateID ON DUPLICATE KEY UPDATE largestCityPop = city.largestCityPop

This works, but seems just plain ugly:

INSERT IGNORE INTO largestCities (stateID, largestCityPop, statePop) SELECT * FROM (SELECT stateID, MAX(city.pop) as biggestCityPop, state.pop FROM city JOIN state on city.stateID = state.ID GROUP BY city.stateID) x ON DUPLICATE KEY UPDATE largestCityPop = biggestCityPop


提交答案