MySQL“好”方法在未找到时插入一行，如果找到则更新它

MySQL "good" way to insert a row if not found, or update it if it is found

提问by DivideByHero

Very often, I want to run a query on one of my users where I want a row stored and associated with that user, in a 1-to-1 relationship. So let's say (this is just an arbitrary example), that I have a table that keeps track of a user's car, along with some info about the car. Each user can have either 0 or 1 cars. If the user has no car, there is no entry in the table for that user.

很多时候，我想对我的一个用户运行查询，其中我希望以 1 对 1 的关系存储一行并与该用户关联。所以让我们说（这只是一个随意的例子），我有一个表格来跟踪用户的汽车，以及一些关于汽车的信息。每个用户可以拥有 0 辆或 1 辆汽车。如果用户没有汽车，则表中没有该用户的条目。

cars table (again, just an example): id, user_id, car_make, car_model

汽车表（同样，只是一个例子）：id、user_id、car_make、car_model

So, when I update this table, I always end up doing something like this (pseudo-code):

所以，当我更新这个表时，我总是做这样的事情（伪代码）：

result = SELECT * FROM cars WHERE user_id=5
if (num_rows(result)>0){
    UPDATE cars SET car_make='toyota', car_model='prius' WHERE user_id=5
}else{
    INSERT INTO cars (user_id, car_make, car_model) VALUES (5, 'toyota', 'prius')
}

How can I make this into one elegant statement that works "atomically"? What happens if, in another process, the row is REMOVED between the SELECT and UPDATE statements? My UPDATE statement will fail where the INSERT statement should have run. And I feel like I need to do two similar (but different) statements to accomplish the same thing! What I need is some statement that will assure me that the data I want exists in the table, especially when I only want 1 row that satisfies my requirement. For example, it might be something like (this is totally made-up of course):

我怎样才能把它变成一个“原子地”工作的优雅语句？如果在另一个进程中，行在 SELECT 和 UPDATE 语句之间被 REMOVED 会发生什么？我的 UPDATE 语句将在 INSERT 语句应该运行的地方失败。而且我觉得我需要做两个相似（但不同）的语句来完成同样的事情！我需要的是一些声明，以确保我想要的数据存在于表中，尤其是当我只想要 1 行满足我的要求时。例如，它可能是这样的（当然这完全是编造的）：

MAKE SURE A ROW IN cars WHERE user_id=5 IS SET WITH car_make='toyota', car_model='prius'

That way, if user_id of 5 exists already, it will be updated, otherwise, it will be inserted. Also, if I changed the requirements, for example to say that each user can have zero or one cars of a given car_make, then I could further specify that:

这样，如果 user_id 5 已经存在，它会被更新，否则，它会被插入。此外，如果我更改了要求，例如说每个用户可以拥有给定 car_make 的零辆或一辆汽车，那么我可以进一步指定：

MAKE SURE A ROW IN cars WHERE user_id=5 AND car_make='toyota' IS SET WITH car_model='prius'

I hope my question makes sense! How can I improve this basic insert-if-not-found or update-if-found operation that comes up so often? Thanks for any help!

我希望我的问题是有道理的！如何改进这种经常出现的基本插入如果未找到或更新如果找到操作？谢谢你的帮助！

回答by mishac

You can use "REPLACE INTO" or "INSERT… ON DUPLICATE KEY UPDATE". I believe the second is what you want, but there are situations where REPLACE INTO is convenient.

您可以使用“ REPLACE INTO”或“ INSERT…ON DUPLICATE KEY UPDATE”。我相信第二个是你想要的，但在某些情况下 REPLACE INTO 很方便。

回答by defines

From my other Stack Overflow answer:

从我的其他 Stack Overflow 回答中：

If you want to do this in a single statement, I would recommend using the INSERT ... ON DUPLICATE KEY UPDATEsyntax, as follows:

如果您想在单个语句中执行此操作，我建议使用以下INSERT ... ON DUPLICATE KEY UPDATE语法：

INSERT INTO table (id, someothervalue) VALUES (1, 'hi mom')
  ON DUPLICATE KEY UPDATE someothervalue = 'hi mom';

The initial INSERTstatement will execute if there is no existing record with the specified key value (either primary key or unique). If a record already exists, the following UPDATEstatement (someothervalue = 3) is executed.

INSERT如果不存在具有指定键值（主键或唯一键）的现有记录，则将执行初始语句。如果记录已存在，则执行以下UPDATE语句 ( someothervalue = 3)。

This is supported in all versions of MySQL. For more info, see the MySQL Reference Manual page for INSERT ... ON DUPLICATE KEY UPDATE

这在所有版本的 MySQL 中都受支持。有关更多信息，请参阅MySQL 参考手册页面INSERT ... ON DUPLICATE KEY UPDATE

回答by Andrew Barnett

This is called an UPSERT (UPdate or inSERT). There are a number of SO questions about it you can search for. See Wikipedia

这称为 UPSERT（UP日期或SERT）。您可以搜索许多关于它的 SO 问题。参见维基百科

EDIT:MySQL 4.1+ supports INSATE (INSert or updATE), which should get you the same thing, as long as you have a primary key. MySQL Manual

编辑：MySQL 4.1+ 支持 INSATE（INSert 或 upd ATE），只要你有一个主键，它应该会给你同样的东西。MySQL 手册

回答by Darryl E. Clarke

I think it's easier to switch it around. Try to insert, then update.

我认为切换它更容易。尝试插入，然后更新。

MySQL specifically has a clause 'ON DUPLICATE KEY'

MySQL 特别有一个子句“ON DUPLICATE KEY”

INSERT INTO cars (fields) VALUES (values) ON DUPLICATE KEY UPDATE ...

INSERT INTO汽车（字段）VALUES（值）在重复密钥更新...

This of course requires you to have proper unique keys setup.

这当然需要您有适当的唯一键设置。

回答by Darryl E. Clarke

an example of how I have used ON DUPLICATE KEY UPDATE: INSERT INTO registry (name, value) VALUES ('" . $key . "', '" . $val . "') ON DUPLICATE KEY UPDATE value= '" . $val . "'"

我如何使用 ON DUPLICATE KEY UPDATE 的示例： INSERT INTO registry (name, value) VALUES ('" . $key . "', '" . $val . "') ON DUPLICATE KEY UPDATE value='" . $ val . "'"

回答by juan

In Oracle you have Merge.

在 Oracle 中，您有Merge。

No idea on MySql, but the term might give you something else to search.

不知道 MySql，但这个词可能会给你其他东西来搜索。