每个MySQL表都应该有一个自动递增的主键吗？ [英] Should every MySQL table have an auto-incremented primary key?

问题描述

• 我了解主键的值。


• 我了解索引的值。

每个 MySQL表是否应具有自动递增的主键（理想情况下具有INT字段类型）？ / p>

更新

@Raj More的答案似乎是最有效的。但是，我想到的问题是，此自动递增的主键ID将如何与其他表关联。例如：

表1

  ID |名|姓|电子邮件
 ---------------------------------------- 
 1 |约翰|母鹿| [e-mail] 
 2 |莎拉|存放[e-mail] 
 3 |迈克|兄弟| [e-mail] 
  

表2

  ID |会员编号|显示|地址
 -------------------------------------------- 
 1 | 1 |娱乐区| 123 street 
 2 | 3 | silly place llc | 944别墅博士
  

在上面的示例中，消费者可以来到该站点并选择注册免费产品/服务。如果消费者选择，他们可以提供其他信息（存储在表2中）以进行其他邮寄等。我看到的问题是这些表与主键自动递增字段之间的关系。在表2中，会员ID与表1的ID相关，但不清楚。放入表2的任何新信息将增加1，而不是所有所有消费者都将选择参与表2的必需数据。

解决方案方案

我不是代理密钥的忠实拥护者。我还没有看到一种情况，我希望对数据库的每个表都使用一个。

我会说否 。

阅读此答案： 代理vs-自然业务键

---

以上可能是

在一般情况下，关于替代键和自然键有很多问题和答案，所以我觉得这个问题是更像是重复。我的看法是代理键很好而且非常有用，主要是因为自然键可以在连接表链的低端导致非常大的主键-而且许多RDBMS不能很好地处理它，聚集索引变大等等。但是说 每个 MySQL表应该具有一个自动递增的主键。这是一个非常绝对的声明，我认为在某些情况下它们确实提供的很少甚至没有。

自从OP更新了问题之后，我将尝试就该特定主题发表评论。

p>

我认为这正是自动递增主键不仅无用而且会带来负值的情况。假设 table1 和 table2 处于 1：1 关系，会员ID 既可以是主键，又可以是外键到 table1 。

添加一个自动递增的id列会添加一个索引，如果它是一个聚集的索引（如InnoDB PK索引），则会增加会员ID 索引的大小。甚至，如果您具有这样的自动递增的id，则必须使用此 id （将表2联接到<$ c中的表），将table2联接到其他表$ c> 1：n 与table2的关系）和一些使用 memberid 的联接（ 1：n 与 table1 的关系）。如果您只有 memberid ，则可以使用 memberid 完成这两种类型的JOIN。

• I understand the value of primary keys.
• I understand the value of indexes.

Should every MySQL table have an auto-incremented primary key (ideally with INT field type)?

Update

@Raj More's answer seems most efficient. The issue when I think about it, however, is how this auto-incremented primary key ID will relate to other tables. For example:

table 1

ID  |   firstname  |   lastname | email
----------------------------------------
1   |    john      |   doe      | [e-mail]
2   |    sarah     |   stow     | [e-mail]
3   |    mike      |   bro      | [e-mail]

table 2

ID  | memberid |    display      |    address
--------------------------------------------
1   |    1     | funtime zone    |   123 street
2   |    3     | silly place llc |  944 villa dr 

In the example above, a consumer may come to the site and choose to register for a free product/service. If the consumer chooses, they are able to give additional information (stored in table 2) for additional mailing, etc. The problem I see is in how these tables relate to the 'primary key auto-incremented field'. In table 2, the 'memberid' relates to table 1's ID but this is not 'extremely' clear. Any new information placed into table 2 will increment by 1 whereas not all consumers will choose to participate in the table 2 required data.

解决方案

I am not a huge fan of surrogate keys. I have yet to see a scenario where I would prefer to use one for every table of a database.

I would say No.

Read up on this answer: surrogate-vs-natural-business-keys

---

The above may be seen as sarcastic or flaming (despite the surprisingly many upvotes) so it's deleted.

In the general case, there have been many questions and answers on surrogate and natural keys so I felt this question is more like a duplicate. My view is that surrogate keys are fine and very useful, mainly because natural keys can lead to very big primary keys in the low end of a chain of connected tables - and this is not handled well by many RDBMS, clustered indexes get big, etc. But saying that "every MySQL table should have an auto-incremented primary key" is a very absolute statement and I think there are cases when they really offer little or nothing.

Since the OP updated the question, I'll try to comment on that specific topic.

I think this is exactly a case where an autoincrementing primary key is not only useless but adds negative value. Supposing that table1 and table2 are in 1:1 relationship, the memberid can be both the Primary Key and a Foreign Key to table1.

Adding an autoincrementing id column adds one index and if it's a clustered one (like InnoDB PK indexes) increases the size of the memberid index. Even more, if you have such an auto-incrementing id, some JOIN of table2 to other tables will have to be done using this id (the JOINs to tables in 1:n relation to table2) and some using memberid (the JOINs to tables in 1:n relation to table1). If you only have memberid both these types of JOINs can be done using memberid.

这篇关于每个MySQL表都应该有一个自动递增的主键吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！