在MySQL中,"REPLACE"和"INSERT ... ON DUPLICATE KEY UPDATE"之间有什么实际区别? [英] What are practical differences between `REPLACE` and `INSERT ... ON DUPLICATE KEY UPDATE` in MySQL?
问题描述
我需要的是使用特定键(实际上是复合键)设置记录的所有字段的值,如果还没有使用该键的记录,则插入记录.
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
.
REPLACE
seems as meant to do the job, but at the same time its manual page suggests
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.
这篇关于在MySQL中,"REPLACE"和"INSERT ... ON DUPLICATE KEY UPDATE"之间有什么实际区别?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!