MySQL 多对多模式设置

发布于 2024-11-04 07:06:59 字数 1574 浏览 2 评论 0原文

我正在设置一个包含帖子和标签的 mysql 数据库，如下所示：

posts    
+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
[...]

tags
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| tag   | varchar(255) | NO   | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

post_tag_map
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| post_id    | int(11) | NO   | PRI | NULL    |       |
| tag_id     | int(11) | NO   | PRI | NULL    |       |
+------------+---------+------+-----+---------+-------+

标签将在多个帖子之间共享； “red”可能会被第 5 篇和第 10 篇文章使用。

我的问题是：如果标签被多个帖子使用，我该如何防止删除它，如果没有则删除它？

注意：我正在使用外键，我认为它可以解决这个问题，但它似乎不起作用：

CREATE TABLE `post_tag_map` (
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`post_id`,`tag_id`),
  FOREIGN KEY (`post_id`) REFERENCES posts(`id`),
  FOREIGN KEY (`tag_id`) REFERENCES tag(`id`)
)

原文

I'm setting up a mysql db with posts and tags that looks like this:

posts    
+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
[...]

tags
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| tag   | varchar(255) | NO   | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

post_tag_map
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| post_id    | int(11) | NO   | PRI | NULL    |       |
| tag_id     | int(11) | NO   | PRI | NULL    |       |
+------------+---------+------+-----+---------+-------+

The tags will be shared between multiple posts; 'red' may be used by post 5 and 10.

My question is: how do I prevent the deletion of a tag if it is being used by more than one post and delete it if it isn't?

Note: I am using Foreign Keys which I thought would take care of this issue but it doesn't seem to be working:

CREATE TABLE `post_tag_map` (
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`post_id`,`tag_id`),
  FOREIGN KEY (`post_id`) REFERENCES posts(`id`),
  FOREIGN KEY (`tag_id`) REFERENCES tag(`id`)
)

分享到QQ

分享到微博

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

发布评论

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

以可爱出名 2024-11-11 07:06:59

您可以使用这样的 delete 语句一次性删除所有表。

DELETE FROM post_tag_map, posts, tags
WHERE post.id = post_tag_map.post_id
AND tags.id = post_tag_map.tag_id
AND tags.id = 256;

然而，MySQL 不保证删除发生的顺序。如果您有外键，它们可能会阻止删除的发生。

因此，要么不要使用FOREIGN KEY **或**使用ON DELETE CASCADE`子句声明它们。

请记住，MyISAM 不支持外键，因此您只能进行多表删除。

有关多表删除的更多信息：http://dev.mysql.com/doc /refman/5.1/en/delete.html

You can delete all tables in one go using a delete statement like this.

DELETE FROM post_tag_map, posts, tags
WHERE post.id = post_tag_map.post_id
AND tags.id = post_tag_map.tag_id
AND tags.id = 256;

However MySQL makes no guarantees about the order that the deletes will take place in. If you have foreign keys, those may prevent the delete from taking place.

So either do not use FOREIGN KEY **or** declare them with aON DELETE CASCADE` clause.

Remember MyISAM does not support foreign keys, so there you only have the multitable delete.

More about multitable deletes here: http://dev.mysql.com/doc/refman/5.1/en/delete.html

回复收藏 0 原文

新一帅帅 2024-11-11 07:06:59

您需要像这样声明外键：

FOREIGN KEY (post_id) REFERENCES posts(id)
   ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tag(id)
   ON DELETE CASCADE

“删除级联”将启动自动删除。请注意，级联不会“向上”传播链接表的另一侧。如果删除标签，则只有 post_tag_map 中的匹配记录会消失，但会保留它们所附加的帖子。

You'd want to declare the foreign keys like this:

FOREIGN KEY (post_id) REFERENCES posts(id)
   ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tag(id)
   ON DELETE CASCADE

the 'on delete cascade' is what will initiate the auto-delete. Note that the cascade will NOT propagate "up" the other side of the link table. If you delete a tag, only the matching records in post_tag_map will vanish, but leave the posts they were attached to alone.

回复收藏 0 原文