更新MySQL表，删除旧记录。

发布于 2024-10-16 18:51:08 字数 436 浏览 2 评论 0原文

我有一个关联的 MySQL 表 (user_category)，用于存储用户对类别的偏好。它有一个 UserId 列和一个 CategoryCode 列。如果用户对旅行（trvl）类别和免费物品（free）类别感兴趣，那么记录将如下所示。

UserId    CategoryCode
1         trvl
1         free

当用户更新其类别偏好时，更新此记录的最佳方式是什么？我认为最简单的方法就是

DELETE FROM user_category WHERE UserId = 1;
INSERT INTO user_category (UserId,CategoryCode) VALUES (1,'catx'),(1,'catx'),(1,'catx')

“catx”是他们感兴趣的新类别。

原文

I have an associative MySQL table (user_category) that stores users preferneces for categories. It has a UserId column and a CategoryCode column. If a user was interested in the travel(trvl) category and the free stuff(free)category then the record would look like this.

UserId    CategoryCode
1         trvl
1         free

What is the best way to update this record when the user updates their category preferences?
I thought the easiest way would be to just

DELETE FROM user_category WHERE UserId = 1;
INSERT INTO user_category (UserId,CategoryCode) VALUES (1,'catx'),(1,'catx'),(1,'catx')

Where 'catx' are the new categories that they are interested in.

分享到QQ

分享到微博

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

发布评论

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

傲性难收 2024-10-23 18:51:08

有时，最简单的解决方案就是最好的解决方案。这就是其中之一；）

回复收藏 0 原文

水水月牙 2024-10-23 18:51:08

这取决于实施。如果您可以将新选择放入表结构中，则此查询允许您仅删除不再有效的首选项并仅插入新首选项。如果您需要同时为多个用户进行批量操作，它也适用。

-- Delete just the preferences that are no longer valid
DELETE  user_category 
from    user_category a left join
        NewSelection b on a.UserId=b.UserId and a.CategoryCode=b.CategoryCode
WHERE   b.CategoryCode is null

-- Insert just the new preferences
INSERT INTO user_category (UserId,CategoryCode) 
SELECT  a.UserId,a.CategoryCode
FROM    NewSelection a left join
        user_category b on a.UserId=b.UserId and a.CategoryCode=b.CategoryCode
where   b.CategoryCode is null

It depends on the implementation. If you can put the new selection a Table structure, this query allows you to delete just de preferences that are no longer valid and insert just the new preferences. It also works if you need to operate batch for many users at the same time.

-- Delete just the preferences that are no longer valid
DELETE  user_category 
from    user_category a left join
        NewSelection b on a.UserId=b.UserId and a.CategoryCode=b.CategoryCode
WHERE   b.CategoryCode is null

-- Insert just the new preferences
INSERT INTO user_category (UserId,CategoryCode) 
SELECT  a.UserId,a.CategoryCode
FROM    NewSelection a left join
        user_category b on a.UserId=b.UserId and a.CategoryCode=b.CategoryCode
where   b.CategoryCode is null

回复收藏 0 原文

顾挽 2024-10-23 18:51:08

delete from user_category where UserId = 1 and CategoryCode not in (x, y, z)
insert into user_category values (1, x) on duplicate key update CategoryCode = x

假设您的主键是 UserId、CategoryCode。

delete from user_category where UserId = 1 and CategoryCode not in (x, y, z)
insert into user_category values (1, x) on duplicate key update CategoryCode = x

Assume that your primary key is UserId, CategoryCode.

回复收藏 0 原文

~没有更多了~