带有临时数据的 MySQL 表 - 包括主键？

Question

我正在构建一个新数据库，并且有几个包含临时数据的表。

例如:用户请求更改密码 - 存储一个 token ，然后将其删除。

目前我在这些表上有一个主键，它将从 1 向上自动递增。

AUTO_INCREMENT = 1;

我真的看不出这个主键有任何用处……我永远不会引用它，它只会变得更大。

像这样的表是否应该有主键？

Answer 1

Short answer: yes.

Long answer:

You need your table to be joinable on something If you want your tableto be clustered, you need some kind of a primary key. If your tabledesign does not need a primary key, rethink your design: mostprobably, you are missing something. Why keep identical records? InMySQL, the InnoDB storage engine always creates a PRIMARY KEY if youdidn't specify it explicitly, thus making an extra column you don'thave access to.

Note that a PRIMARY KEY can be composite.

If you have a many-to-many link table, you create the PRIMARY KEY onall fields involved in the link. Thus you ensure that you don't havetwo or more records describing one link.

Besides the logical consistency issues, most RDBMS engines willbenefit from including these fields in an UNIQUE index.

And since any PRIMARY KEY involves creating a UNIQUE index, you shoulddeclare it and get both logical consistency and performance.

Here是一个 SO 线程已经有相同的讨论。

有些人仍然喜欢接受您的意见。看看here

我个人的意见是你应该有主键，来标识或使行唯一。逻辑可以是您的程序逻辑。可以是自动增量或复合或任何它可以是。