在 MySQL 中存储多对多关系的最佳方式? [英] Best way to store a many-to-many relationship in MySQL?

问题描述

假设我有一个包含posts"和tags"表的简单数据库.帖子可以有多个标签，标签可以属于多个帖子.

Let's say I have a simple database with tables 'posts' and 'tags'. Posts can have many tags and tags can belong to many posts.

构建数据库的最佳方式是什么?我想过使用列表/序列化:

What is the best way to structure the database? I thought of using a list/serialize:

tags
idx tag_id, str tag_name

posts
idx post_id, str title, list tag_ids

或者有另一个带有关联的表.问题是使用这个我什至不知道如何构建查询以在收到帖子时提取关联的标签名称.

OR having another table with the associations. Problem is using this I don't even know how to structure the query to pull the associated tag names when I get a post.

posts
idx post_id, str title

post_tags
fk post_id, fk tag_id

其实我一个都不喜欢.有没有更好的办法?

I actually I don't like either of them. Is there a better way?

推荐答案

post_tags 是在数据库中实现多对多关系的正确方法.

The post_tags is the proper means of implementing a many to many relationship in the database.

我对您发布的内容所做的唯一补充是，其中的两列都应该是主键，以确保没有重复项.

The only addition I'd make to what you posted is that both columns in it should be the primary key to ensure there are no duplicates.

这篇关于在 MySQL 中存储多对多关系的最佳方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！