为什么我们在用户表中应该有一个ID列? [英] Why we should have an ID column in the table of users?

问题描述

很明显，我们已经有了关于每个用户的另一个唯一信息，即用户名.那么，为什么我们需要每个用户另一个独特的事物呢?为什么还要为每个用户都有一个ID?如果我们省略id列会怎样?

It's obvious that we already have another unique information about each user, and that is username. Then, why we need another unique thing for each user? Why should we also have an id for each user? What would happen if we omit the id column?

推荐答案

即使您的用户名是唯一的，使用额外的id列而不使用varchar作为主键也没有什么好处.

Even if your username is unique, there are few advantages to having an extra id column instead of using the varchar as your primary key.

• 有些人更喜欢使用整数列作为主键，以用作永远不需要更改的代理键，即使其他列可能会更改.尽管也没有什么可以阻止自然主键更改的，但是您必须使用级联外键约束来确保相关表中的外键与任何此类更改同步更新.

• Some people prefer to use an integer column as the primary key, to serve as a surrogate key that never needs to change, even if other columns are subject to change. Although there's nothing preventing a natural primary key from being changeable too, you'd have to use cascading foreign key constraints to ensure that the foreign keys in related tables are updated in sync with any such change.

主键是32位整数而不是varchar，可以节省空间.在其他所有引用用户表的表中的int或varchar外键列之间进行选择可能是一个很好的理由.

The primary key being a 32-bit integer instead of a varchar can save space. The choice between a int or a varchar foreign key column in every other table that references your user table can be a good reason.

如果将新行添加到索引的末尾，则将其插入到索引的中间比插入主键索引的效率要高一些. MySQL表中的索引通常是B + Tree数据结构，您可以研究它们以了解它们的性能.

Inserting to the primary key index is a little bit more efficient if you add new rows to the end of the index, compared to of wedging them into the middle of the index. Indexes in MySQL tables are usually B+Tree data structures, and you can study these to understand how they perform.

某些应用程序框架更喜欢约定，即数据库中的每个表都有一个名为id的主键列，而不是使用自然键或复合键.遵循这样的约定可以使某些编程任务更简单.

Some application frameworks prefer the convention that every table in your database has a primary key column called id, instead of using natural keys or compound keys. Following such conventions can make certain programming tasks simpler.

这些问题都不是破坏交易的事情.使用自然键还有其他优点:

None of these issues are deal-breakers. And there are also advantages to using natural keys:

• 如果您按用户名查找行的次数要多于按id搜索的行，则最好选择用户名作为主键，并利用InnoDB的索引组织存储的优势.如果可能的话，将主查询列设为主键，因为在InnoDB中主键查找效率更高(应该在MySQL中使用InnoDB).

• If you look up rows by username more often than you search by id, it can be better to choose the username as the primary key, and take advantage of the index-organized storage of InnoDB. Make your primary lookup column be the primary key, if possible, because primary key lookups are more efficient in InnoDB (you should be using InnoDB in MySQL).

正如您所注意到的，如果您已经对用户名进行了唯一的限制，那么保留不必要的额外id列似乎会浪费存储空间.

As you noticed, if you already have a unique constraint on username, it seems a waste of storage to keep an extra id column you don't need.

使用自然键意味着外键包含人类可读的值，而不是任意的整数id.这样，查询就可以使用外键值，而不必为真实"值联接回父表.

Using a natural key means that foreign keys contain a human-readable value, instead of an arbitrary integer id. This allows queries to use the foreign key value without having to join back to the parent table for the "real" value.

重点是，没有规则可以涵盖100％的案件.我经常建议您保持选项打开，甚至在单个数据库中也要使用自然键，复合键和代理键.

The point is that there's no rule that covers 100% of cases. I often recommend that you should keep your options open, and use natural keys, compound keys, and surrogate keys even in a single database.

我在我的书" SQL反模式:避免数据库的陷阱"一章中介绍了代理键的一些问题.编程.

这篇关于为什么我们在用户表中应该有一个ID列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！