mysql 自动递增 id 与组合字段主键

Question

我很难弄清楚使用什么作为我的主键。

我的 table :

| gender | age   | value | date updated | page id(the forgein key) |
| M      | 15-24 | 100   | some date    | 1
| M      | 25-34 | 120   | some date    | 1
| M      | 35-44 | 110   | some date    | 1
| F      | 15-24 | 190   | some date    | 1
| F      | 25-34 | 230   | some date    | 1

现在我需要添加一个主键。我可以添加一个自动递增的 id 字段并使其成为 pk，但该 id 不会用作 forgein 键或另一个表中的任何其他内容，因此添加它是没有用的。

我还可以将页面、性别和年龄结合起来并使它们成为主键，但我不确定这样做有什么优势。我尝试用谷歌搜索了一段时间，但仍然不确定该怎么做。

Answer 1

请阅读MySQL的文档:

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values. With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns.

If your table is big and important, but does not have an obvious column or set of columns to use as a primary key, you might create a separate column with auto-increment values to use as the primary key. These unique IDs can serve as pointers to corresponding rows in other tables when you join tables using foreign keys.

感谢@AaronDigulla 的解释...:

Necessary? No. Used behind the scenes? Well, it's saved to disk and kept in the row cache, etc. Removing will slightly increase your performance (use a watch with millisecond precision to notice).

But ... the next time someone needs to create references to this table, they will curse you. If they are brave, they will add a PK (and wait for a long time for the DB to create the column). If they are not brave or dumb, they will start creating references using the business key (i.e. the data columns) which will cause a maintenance nightmare.

Conclusion: Since the cost of having a PK (even if it's not used ATM) is so small, let it be.

根据我的经验和知识，如果您不定义主键，数据库将创建一个隐藏的主键。因此，在您的情况下，最好的解决方案是无论如何都要创建它。