MySQL：如果不存在则高级插入

发布于 2024-12-01 15:49:37 字数 738 浏览 0 评论 0原文

我有一个表，其中包含三列：user_id、设置和值。我正在尝试使用以下代码：

INSERT INTO 'user_settings'(user_id, setting, value)
VALUES (1234, setting_1, 500)
ON DUPLICATE KEY UPDATE user_id = 1234, setting = setting_1'

这在创建新设置时效果很好，并且不会生成重复的记录。当我想更改值时，问题就出现了 - 在上一个查询运行后，这将不起作用：

INSERT INTO 'user_settings'(user_id, setting, value)
VALUES (1234, setting_1, 999)
ON DUPLICATE KEY UPDATE user_id = 1234, setting = setting_1'

没有行受到影响。显然我错过了一些东西......

重要：我无法更改数据库（新的主键或其他内容）。

更新：看来我对ON DUPLICATE KEY的理解是错误的。但问题仍然存在——实现这一目标最有效的方法是什么？

在下面的评论中回答：“如果主（或唯一）键是（user_id，设置），则使用：... ON DUPLICATE KEY UPDATE value = 999”。

原文

I have a table with with essentially three columns: user_id, setting, and value. I'm trying to use the following code:

INSERT INTO 'user_settings'(user_id, setting, value)
VALUES (1234, setting_1, 500)
ON DUPLICATE KEY UPDATE user_id = 1234, setting = setting_1'

This works great when creating a new setting, and it doen't generate duplicate records. The problem comes when I want to change the value- this won't work after the previous query has run:

INSERT INTO 'user_settings'(user_id, setting, value)
VALUES (1234, setting_1, 999)
ON DUPLICATE KEY UPDATE user_id = 1234, setting = setting_1'

No rows are affected. Clearly I'm missing something...

IMPORTANT: I am not able to alter the database (new primary keys or something).

UPDATE: It seems my understanding of ON DUPLICATE KEY is wrong. But the question remains- what is the most efficient way way to accomplish this?

Answered in a comment below: "If the Primary (or Unique) key is (user_id, setting), then use: ... ON DUPLICATE KEY UPDATE value=999".

分享到QQ

分享到微博

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

发布评论

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

握住你手 2024-12-08 15:49:37

假设您在 user_id 上实际上有一个唯一的键，那么您将得到“没有行受到影响”，因为您没有在第二个查询中更改任何内容。我认为您想要做的是更新 value 字段：

INSERT INTO 'user_settings'(user_id, setting, value)
VALUES (1234, setting_1, 999)
ON DUPLICATE KEY UPDATE setting = setting_1,value=999

如果没有 value ，您只需设置 user_id 和设置字段与之前的值相同，MySQL不需要更新记录。

如果您在 user_id 上没有唯一密钥，则必须找到不同的方法，因为 ON DUPLICATE KEY UPDATE 将不会触发。

Assuming you actually have a unique key on user_id, you are getting "no rows affected" because you aren't changing anything in the second query. I think what you want to do is update the value field as well:

INSERT INTO 'user_settings'(user_id, setting, value)
VALUES (1234, setting_1, 999)
ON DUPLICATE KEY UPDATE setting = setting_1,value=999

Without value in there, you're just setting the user_id and the setting field to the same values they were before, and MySQL doesn't need to update the record.

If you don't have a unique key on user_id, you'll have to find a different approach, as the ON DUPLICATE KEY UPDATE won't trigger.

回复收藏 0 原文

~没有更多了~