MySQL“好” 如果找不到则插入行，如果找到则更新它的方法

发布于 2024-07-17 11:52:36 字数 1131 浏览 7 评论 0 原文

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

cars 表（同样，只是一个例子）： id, user_id, car_make, car_model

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

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 行时。例如，它可能是这样的（当然这完全是编造的）：

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

这样，如果 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'

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

原文

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 (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')
}

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'

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

指尖上的星空 2024-07-24 11:52:36

您可以使用“替换为”或“插入...重复密钥更新”。我相信第二个就是你想要的，但在某些情况下 REPLACE INTO 很方便。

回复收藏 0 原文

淡淡の花香 2024-07-24 11:52:36

来自我的其他 Stack Overflow 答案：

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

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

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

所有版本的 MySQL 都支持此功能。有关详细信息，请参阅 MySQL 参考手册页面 <代码>插入...重复密钥更新

From my other Stack Overflow answer:

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

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

The initial INSERT statement 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 UPDATE statement (someothervalue = 3) is executed.

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

回复收藏 0 原文