MySQL SQL 中的 REPLACE 与 INSERT

REPLACE versus INSERT in SQL

提问by jcm

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

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

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

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

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.

在这种情况下，我想用另一个人替换我的记录，但保留唯一的 id。问题是我必须在事务中执行 DELETE/INSERT 以使其原子化，或者我需要执行完整的更新。

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

任何人都可以向我解释执行 UPDATE 的问题是什么，以及我们何时可以选择 REPLACE 而不是 UPDATE？

The UPDATE code:

更新代码：

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

Here is the REPLACE code:

这是替换代码：

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

EDIT: 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?

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

回答by Patrick Hofman

According to the documentation, the difference is:

根据文档，不同之处在于：

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.

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

So what it does:

那么它的作用是：

• Try to match the row using one of the available indexes;
• If the row doesn't exist already: add a new one;
• If the row exists already: delete the existing row and add a new one afterwards.

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

When might using this become useful over separate insertand updatestatements?

什么时候使用 this 比单独的insertandupdate语句有用？

• 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).

• 您可以安全地调用它，而不必担心现有行（一个语句对两个）；
• 如果您希望在inserting/时删除相关数据updating，您可以使用replace：它也删除所有相关数据）；
• 当触发器需要触发时，你期望一个insert（不好的理由，好吧）。

回答by FooLman

First Replace isn't widely understood in all database engines.

并非所有数据库引擎都广泛理解 First Replace。

Second replace inserts/updates a record based on the primary key. While with update you can specify more elaborate conditions:

第二次替换基于主键插入/更新记录。使用更新时，您可以指定更详细的条件：

UPDATE person SET first_name = 'old ' + first_name WHERE age > 50

Also UPDATE won't create records.

UPDATE 也不会创建记录。

回答by Rajen Raiyarela

Update will change the existing records value in table based on particular condition. So you can change one or many records in single query.

更新将根据特定条件更改表中的现有记录值。因此，您可以在单个查询中更改一条或多条记录。

Insert or Replace will insert a new record if records is not present in table else will replace. Replace will only work if and only if you provide the primary key value in the insert or replace query. If you forget to add primary key field value than a new record will created in table.

如果表中不存在记录，则插入或替换将插入新记录，否则将替换。仅当您在插入或替换查询中提供主键值时，替换才会起作用。如果您忘记添加主键字段值，则会在表中创建新记录。

Case example:-

案例示例：-

Update: You have a calculation of wages to be done based on a formula using the column values. In this case you will always use update query as using one single query you can update multiple records.

更新：您需要根据使用列值的公式计算工资。在这种情况下，您将始终使用更新查询，因为使用一个查询可以更新多条记录。

Insert or Replace: Already mentioned in the link you shared.

插入或替换：已在您共享的链接中提及。

回答by Stanislovas Kala?nikovas

UPDATEwill have no effect if the row does not exist.

UPDATE如果该行不存在，则无效。

Where as the INSERTor REPLACEwill insert if the row doesn't exists or replace the values if it does.

如果行不存在，则INSERTorREPLACE将插入，如果存在则替换值。