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 表(同样，只是一个例子):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 语句之间被 REMOVED 会发生什么?我的 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 5 已经存在，则更新，否则插入.此外，如果我更改了要求，例如说每个用户可以拥有给定 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" 或 "插入...在重复的密钥更新中".我相信第二种是你想要的，但在某些情况下 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屋！