MySQL中`REPLACE`和`INSERT ... ON DUPLICATE KEY UPDATE`之间的实际区别是什么?
我需要的是用一个特定的键(键实际上是复合的)设置记录的所有字段的值，如果还没有这样的键的记录，则插入该记录.
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 似乎是旨在完成这项工作，但同时其手册页建议INSERT ... ON DUPLICATE KEY UPDATE.
我应该更好地选择哪些?为什么?
What of them should I better choose and why?
我想到的 REPLACE 的唯一副作用"是它会在 INSERT ... ON DUPLICATE KEY 时增加自动增量值(幸运的是我没有使用任何值)UPDATE 可能不会.需要考虑的其他实际差异是什么?在哪些特定情况下，REPLACE 优于 INSERT ... ON DUPLICATE KEY UPDATE，反之亦然?
The only "side effect" of REPLACE that comes into my mind is that it would increment autoincrement values (fortunately I don't use any) while INSERT ... ON DUPLICATE KEY UPDATE probably wouldn't. What are the other practical differences to take in mind? In what particular cases can REPLACE be preferred over INSERT ... ON DUPLICATE KEY UPDATE and vice versa?
推荐答案
REPLACE 在内部执行删除然后插入.如果您有指向该行的外键约束，这可能会导致问题.在这种情况下，REPLACE 可能会失败或更糟:如果您的外键设置为级联删除，REPLACE 将导致其他表中的行被删除.即使在 REPLACE 操作之前和之后都满足了约束，也会发生这种情况.
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.
使用 INSERT ... ON DUPLICATE KEY UPDATE 避免了这个问题，因此是首选.
Using INSERT ... ON DUPLICATE KEY UPDATE avoids this problem and is therefore prefered.
相关文章