在MySQL数据透视表中使用自动递增的主键有什么好处?

本文介绍了在MySQL数据透视表中使用自动递增的主键有什么好处?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设我们在MySQL数据库中有三个表:

Imagine we have three tables in a MySQL database:

• 帖子
• 类别
• category_post

帖子和类别之间存在一对多的关系，因此单个帖子可能具有多个类别.

There is a one-to-many relationship between posts and categories so that a single post may have many categories.

category_post 表是类别和帖子之间的数据透视表，并具有以下列:

The category_post table is the pivot table between categories and posts and has the following columns:

• id(主键，自动递增，大整数)
• category_id
• post_id

我们还假设 category_post 表中有1,000,000行.

Let's also imagine that we have 1,000,000 rows in our category_post table.

我的问题是:

在 category_post 表中具有 id 列是否对性能有好处?还是只占用了额外的空间?

Is there any performance benefit to having the id column in the category_post table or does it just take up extra space?

推荐答案

帖子和类别可能是多对多的，而不是一对多的.

Posts and categories is probably many-to-many, not one-to-many.

最好使用多对多关系表

CREATE TABLE a_b ( a_id ... NOT NULL, b_id ... NOT NULL, PRIMARY KEY (a_id, b_id), INDEX(b_id, a_id) -- include this if you need to go both directions ) ENGINE = InnoDB;

这样，您将自动在两个方向上进行聚类"查找，并且避免了不必要的表人工ID.

With that, you automatically get "clustered" lookups both directions, and you avoid the unnecessary artificial id for the table.

(顺便说一句，N.B.，一个隐式PK是6个字节，而不是8个字节.JeremyCole在该主题上发表了一篇冗长的文章.)

(By the way, N.B., an implicit PK is 6 bytes, not 8. There is a lengthy post by Jeremy Cole on the topic.)

一对多关系不需要此额外表.而是在另一个表中有一个ID.例如，一个城市表将在其中包含该国家/地区的ID.

A one-to-many relationship does not need this extra table. Instead, have one id inside the other table. For example, a City table will have the id for the Country in it.