mysql自动递增意义_在MySQL数据透视表中具有自动递增的主键有什么好处？

Imagine we have three tables in a MySQL database:

posts

categories

category_post

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

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

id (primary key, auto-incrementing, big integer)

category_id

post_id

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

My question is:

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.

A many-to-many relationship table is best done something like

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;

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

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

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.