MySQL“好”如果找不到插入行的方法，或者如果找到则更新它 [英] MySQL "good" way to insert a row if not found, or update it if it is found

问题描述

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

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.

cars table（再次只是一个例子）：
id，user_id ，car_make，car_model

cars table (again, just an example): 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')
}

我如何将其作为原子性的优雅陈述？如果在另一个进程中，SELECT和UPDATE语句之间的行被删除，会发生什么？我的UPDATE语句将失败，其中INSERT语句应该运行。我觉得我需要做两个类似（但不同的）语句来完成同样的事情！我需要的是一些声明，将确保我想要的数据存在于表中，特别是当我只需要满足我的要求的1行时。例如，它可能是这样的（当然这完全是由...组成）：

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):

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

user_id已存在，它将被更新，否则将被插入。另外，如果我改变了要求，例如说每个用户可以拥有一个给定car_make的零个或者一个车，那么我可以进一步指出：

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:

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!

推荐答案

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

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.

这篇关于MySQL“好”如果找不到插入行的方法，或者如果找到则更新它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！