更新MySQL表，删除旧记录。

Question

我有一个关联的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'是他们感兴趣的新类别。

- andrew

---

有没有选项可以向表中添加主键？然后您可以更新表并设置一个ID字段。然后，您就可以操作单独的记录了。虽然这需要更多的初始工作，但您的数据管理会变得更加容易。 - dmcnelis

@dmcnelis 这里有一个主键。主键是(PRIMARY KEY(UserId,CategoryCode))。-1 意味着建议我对表进行不必要的去规范化。 - andrew

Answer 1

有时候最简单的解决方案也是最好的。这就是其中之一 ;)

- Ivan

---

好的观点。我唯一的担忧是如果查询失败并且我删除了记录但没有更新。不过我同意，我的解决方案可能是最好的。有时候我会问这种问题，希望能介绍一些我从未见过的新颖有趣的mysql类型。 - andrew

缺点：在InnoDB中进行不必要的删除操作会导致一些明显的开销，在MyISAM中仍然会创建增加的负载和值的散布。 - Jeff Ferland

@andrew：然而，如果您使用InnoDB并在事务中执行所有操作（强烈推荐），任何一种方法都将产生相同的最终效果。 - Jeff Ferland

Answer 2

这取决于具体的实现方式。如果您可以将新的选择放入表结构中，此查询将允许您仅删除不再有效的偏好设置并仅插入新的偏好设置。如果您需要同时为多个用户批量操作，它也可以起作用。

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

- pcofre

---

提供一个有趣的解决方案，加1分。对于非常大的表格，我认为只删除需要更新的记录并仅插入需要插入的记录是最有效的，因为这个查询可以实现。不过，我的想法可能完全错误。 - andrew

Answer 3

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。

- Jeff Ferland

---

UserId不是主键，因为样本记录允许该用户有多行。 - The Scrum Meister

但如果存在“ON DUPLICATE KEY do nothing”的话，那就更好了。 - andrew

@Scrum Meister他明确表示，假设主键是（UserId，CategoryCode），而不是UserId。 - andrew

@andrew 1) 我相信答案已经被编辑了，2) on duplicate key update 没有任何有用的作用。请使用 INSERT IGNORE。 - The Scrum Meister

@Scrum Meister：此答案未记录任何编辑。不使用“insert ignore”有好处...忽略将错误转换为警告，这仍然会导致警告。我认为无效更新、忽略和无效替换的性能应该是相等的。 - Jeff Ferland