SQL 中的 REPLACE 与 INSERT [英] REPLACE versus INSERT in SQL

问题描述

我正在做以下 SQL 教程:http://sql.learncodethehardway.org/book/ex11.html

I am doing the following SQL tutorial: http://sql.learncodethehardway.org/book/ex11.html

在这个练习中，作者在第二段说:

and in this exercise the author says in the second paragraph:

在这种情况下，我想用另一个人替换我的记录，但是保留唯一 ID.问题是我必须要么做一个删除/插入在事务中使其原子化，否则我需要进行完整的更新.

In this situation, I want to replace my record with another guy but keep the unique id. Problem is I'd have to either do a DELETE/INSERT in a transaction to make it atomic, or I'd need to do a full UPDATE.

谁能向我解释执行 UPDATE 的问题是什么，以及何时我们可能会选择 REPLACE 而不是 UPDATE?

Could anyone explain to me what the problem is with doing an UPDATE, and when we might choose REPLACE instead of UPDATE?

更新代码:

UPDATE person SET first_name = "Frank", last_name = "Smith", age = 100
    WHERE id = 0;

这是替换代码:

REPLACE INTO person (id, first_name, last_name, age)
    VALUES (0, 'Frank', 'Smith', 100);

我想我的另一个问题是你为什么要执行 DELETE/INSERT 而不是引用部分中讨论的 UPDATE?

I guess another question I have is why would you ever do a DELETE/INSERT instead of just an UPDATE as is discussed in the quoted section?

推荐答案

根据 文档，区别在于:

REPLACE 的工作方式与 INSERT 完全一样，除了如果表中的旧行与 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，则在插入新行之前删除旧行.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

它的作用是:

• 尝试使用可用索引之一匹配行；
• 如果该行不存在:添加一个新的；
• 如果该行已经存在:删除现有行，然后添加一个新行.

什么时候使用它比单独的 insert 和 update 语句有用?

When might using this become useful over separate insert and update statements?

• 您可以安全地调用它，而不必担心现有行(一个语句对两个)；
• 如果你想在插入/更新时删除相关数据，你可以使用replace:它也会删除所有相关数据)；
• 当触发器需要触发时，你期望一个 insert(不好的理由，好吧).

• You can safely call this, and you don't have to worry about existing rows (one statement vs. two);
• If you want related data to be removed when inserting / updating, you can use replace: it deletes all related data too);
• When triggers need to fire, and you expect an insert (bad reason, okay).

这篇关于SQL 中的 REPLACE 与 INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！