MySQL MySQL中`REPLACE`和`INSERT ... ON DUPLICATE KEY UPDATE`之间的实际区别是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9168928/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What are practical differences between `REPLACE` and `INSERT ... ON DUPLICATE KEY UPDATE` in MySQL?
提问by Ivan
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.
我需要的是使用特定键（该键实际上是复合键）设置记录的所有字段的值，如果还没有具有此类键的记录，则插入该记录。
REPLACEseems as meant to do the job, but at the same time its manual page suggests
INSERT ... ON DUPLICATE KEY UPDATE.
REPLACE似乎是为了完成这项工作，但同时它的手册页建议
INSERT ... ON DUPLICATE KEY UPDATE.
What of them should I better choose and why?
我应该更好地选择哪些，为什么？
The only "side effect" of REPLACEthat comes into my mind is that it would increment autoincrement values (fortunately I don't use any) while INSERT ... ON DUPLICATE KEY UPDATEprobably wouldn't. What are the other practical differences to take in mind? In what particular cases can REPLACEbe preferred over INSERT ... ON DUPLICATE KEY UPDATEand vice versa?
REPLACE我想到的唯一“副作用”是它会增加自动增量值（幸运的是我没有使用任何值），而INSERT ... ON DUPLICATE KEY UPDATE可能不会。需要考虑的其他实际差异是什么？在哪些特定情况下可以REPLACE优先考虑INSERT ... ON DUPLICATE KEY UPDATE，反之亦然？
回答by Mark Byers
REPLACEinternally 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 REPLACEcould fail or worse: if your foreign key is set to cascade delete, the REPLACEwill cause rows from other tables to be deleted. This can happen even though the constraint was satisfied both before and after the REPLACEoperation.
REPLACE在内部执行删除然后插入。如果您有指向该行的外键约束，这可能会导致问题。在这种情况下，REPLACE可能会失败或更糟：如果您的外键设置为级联删除，REPLACE则会导致其他表中的行被删除。即使在REPLACE操作之前和之后都满足约束，这也可能发生。
Using INSERT ... ON DUPLICATE KEY UPDATEavoids this problem and is therefore prefered.
使用INSERT ... ON DUPLICATE KEY UPDATE避免了这个问题，因此是首选。
回答by katrix
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
Replace Into 涉及：
1.Try insert on the table
2. 如果 1 失败，删除行并插入新行
Insert on Duplicate Key Update 涉及：
1.Try insert on table 2.If
1 失败，更新行
如果所有涉及的步骤都是插入，性能应该没有区别。速度必须取决于所涉及的更新数量。最坏的情况是所有语句都是更新
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
我已经尝试了 InnoDB 表上的两个语句，涉及 62,510 个条目（仅更新）。在露营速度上：
替换为：77.411 秒
在重复密钥更新时插入：2.446 秒
Insert on Duplicate Key update is almost 32 times faster.
Table Size: 1,249,250 rows with 12 columns on an Amazon m3.medium
表大小：Amazon m3.medium 上 1,249,250 行，12 列
回答by Andrew Mao
When using REPLACEinstead 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.
当使用REPLACE代替 时INSERT ... ON DUPLICATE KEY UPDATE，当多个查询快速到达给定键时，我有时会观察到键锁定或死锁问题。后者的原子性（除了不会导致级联删除）是使用它的更多理由。
回答by w5m
In what particular cases can REPLACE be preferred over INSERT ... ON DUPLICATE KEY UPDATE and vice versa?
在哪些特定情况下，REPLACE 优于 INSERT ... ON DUPLICATE KEY UPDATE，反之亦然？
I've just found out the hard way that in the case of tables with a FEDERATED storage engine INSERT...ON DUPLICATE KEY UPDATEstatements 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 pageof the MySQL Reference Manual.
我刚刚发现了一种困难的方法，即在INSERT...ON DUPLICATE KEY UPDATE接受带有 FEDERATED 存储引擎语句的表的情况下，如果重复键失败（错误 1022：无法写入；表中的重复键...）发生违规 - 请参阅MySQL 参考手册此页面上的相应要点。
Fortunately, I was able to use REPLACEinstead of INSERT...ON DUPLICATE KEY UPDATEwithin my after insert trigger to achieve the desired outcome of replicating changes to a FEDERATED table.
幸运的是，我能够在插入后触发器中使用REPLACE而不是使用INSERT...ON DUPLICATE KEY UPDATE来实现将更改复制到 FEDERATED 表的预期结果。
回答by Barmar
If you don't list all the columns, I think REPLACEwill reset any unmentioned columns with their default values in the replaced rows. ON DUPLICATE KEY UPDATEwill leave unmentioned columns unchanged.
如果您没有列出所有列，我认为REPLACE将在替换的行中使用默认值重置任何未提及的列。ON DUPLICATE KEY UPDATE将保持未提及的列不变。
回答by Isaac Fife
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?
替换好像是在key已经存在的情况下做了两个操作。也许这意味着两者之间存在速度差异？
(INSERT)one update vs one delete + one insert(REPLACE)
(INSERT) 一次更新 vs 一次删除 + 一次插入 (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/
编辑：我暗示替换可能更慢实际上是完全错误的。好吧，无论如何，根据这篇博客文章...... http://www.tokutek.com/2010/07/why-insert-on-duplicate-key-update-may-be-slow-by-incurring-disk-seeks /
回答by user3522775
"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."
“在重复键错误的情况下，存储引擎可能会执行 REPLACE 作为更新而不是删除加插入，但语义是相同的。”
回答by Dan Marsh
REPLACE seems to be necessary sometimes because INSERT IGNORE doesn't seem to work with data transformations.
REPLACE 有时似乎是必要的，因为 INSERT IGNORE 似乎不适用于数据转换。
If I do this, I only set largestCityPop to itself:
如果我这样做，我只将 maximumCityPop 设置为它自己：
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
INSERT IGNORE INTO largeCities (stateID, maximumCityPop, statePop) SELECT stateID, MAX(city.pop) as largeCityPop, state.pop FROM city JOIN state on city.stateID = state.ID GROUP BY city.stateID ON DUPLICATE KEY UPDATE maximumCityPop = maximumCityPop
If I do this, I am using the GROUP function improperly:
如果我这样做，我将不正确地使用 GROUP 函数：
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)
INSERT IGNORE INTO largeCities (stateID, maximumCityPop, statePop) SELECT stateID, MAX(city.pop) as largeCityPop, state.pop FROM city JOIN state on city.stateID = state.ID GROUP BY city.stateID ON DUPLICATE KEY UPDATE maximumCityPop = MAX (city.pop)
And if I do this, MySQL won't recognize the column name:
如果我这样做，MySQL 将无法识别列名：
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
INSERT IGNORE INTO largeCities (stateID, maximumCityPop, statePop) SELECT stateID, MAX(city.pop) as largeCityPop, state.pop FROM city JOIN state on city.stateID = state.ID GROUP BY city.stateID ON DUPLICATE KEY UPDATE maximumCityPop = 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
INSERT IGNORE INTO maximumCities (stateID, maximumCityPop, statePop) SELECT * FROM (SELECT stateID, MAX(city.pop) as maximumCityPop, state.pop FROM city JOIN state on city.stateID = state.ID GROUP BY city.stateID) x ON复制密钥更新最大城市流行 = 最大城市流行
