java - 删除链接的 MySQL 表中的行并递减另一个表中的计数

Question

我正在尝试创建(基于 Java 的)消息传递网络应用程序。消息使用 MySQL 使用三个表存储:

• 'messages':有两列，id 主键 ('idmessage') 和消息 ('message')。
• 'tags':用户还输入与其消息关联的标签。这有两列 - 标签(也是主键)和计数('count')。
• 'message_tag_link':由于消息和标签之间存在多对一关系，因此我有第三个链接表。这有两列，消息的外键 ('fk_idmessage') 和标签表的 ID ('fk_tag')。

将消息插入数据库工作正常。我在 Java 中使用以下几行:

pst1 = connection.prepareStatement("INSERT INTO messages(message) VALUES (?)");
pst2 = connection.prepareStatement("INSERT INTO tags(tag,count) VALUES (?,1) ON     DUPLICATE KEY UPDATE count = count + 1");
pst3 = connection.prepareStatement("INSERT INTO message_tag_link(fk_idmessage,fk_tag) VALUES (? , ?)");

可以看出，每次创建重复的标签条目时，pst2 中的“计数”都会增加。我正在尝试弄清楚如何完全删除一条消息。我在链接表的列中有关于我的外键的 on delete cascade 描述，但我不确定下一步该做什么。对于给定的'messageid'，我需要:

1. 删除“消息”表中的一行
2. 删除“message_tag_link”表中的关联行
3. 当消息删除成功时，减少“标签”表中的计数值，或者如果计数变为零，则完全删除该行。

是否可以使用 Java PreparedStatement 在 MySQL 中完成所有这些操作？非常感谢任何指针。我是一个完全的 MySQL 新手，所以请多多包涵!

Answer 1

这些查询应该可以满足您的需要。 ？在每个查询中应该是要删除的消息 ID

//Update count
UPDATE tags SET `count` = `count` - 1 WHERE tag_id IN (SELECT fk_tag FROM message_tag_link WHERE fk_idMessage = ?);
//Remove link
DELETE FROM message_tag_link WHERE fk_idmessage = ?;
DELETE FROM messages WHERE id = ?;
//Remove unused tags
DELETE FROM tags WHERE `count` = 0;

正如您所发现的那样，维护计数可能很烦人。更好的办法是使用链接本身。

SELECT COUNT(fk_idmessage) AS msgcount FROM message_tag_link WHERE fk_tag = ?;

您还可以添加 CASCADE，以便在删除消息时标记链接也被删除(参见 here)。但是要小心这种行为)。

此解决方案仍然留下没有消息的标签，但您可以使用查询删除这些标签:

DELETE FROM tags t WHERE NOT EXISTS (SELECT 1 FROM nessage_tag_link WHERE t.tag_id = fk_tag)