建立基本表，整数自增主键是标准吗？
我有一个网络应用程序，我有一个用户的概念，它可能会进入用户表，例如：
table: user
username (varchar 32) | email (varchar 64) | fav_color | ...
我希望用户名和电子邮件是唯一的，这意味着我不能允许用户拥有相同的用户名，或者同一个电子邮件。我看到这种类型的示例表总是引入整数自动增量主键。
不知道为什么这样做，是为了以后通过外键加快查询速度吗？例如，假设我有另一个表，例如：
table: grades
username (foreign key?) | grade
使用用户名作为外键是否效率低下？我想做这样的查询：
SELECT FROM grades WHERE username = 'john'
所以我想对数据库进行整数查找会更快？：
SELECT FROM grades WHERE fk_user_id = 20431
谢谢
I've got a web app, I have a concept of users, which will probably go into a user table like:
table: user
username (varchar 32) | email (varchar 64) | fav_color | ...
I'd like username and email to be unique, meaning I can't allow users to have the same username, or the same email. I see example tables of this sort always introduce an integer auto-increment primary key.
Not sure why this is done, is it to somehow speed up queries by foreign keys later on? For example, let's say I have another table like:
table: grades
username (foreign key?) | grade
Is it inefficient to be using the username as a foreign key? I want to do queries like:
SELECT FROM grades WHERE username = 'john'
so I guess it'd be faster to do an integer lookup for the database instead?:
SELECT FROM grades WHERE fk_user_id = 20431
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
您所要求的在某种程度上是基于个人数据建模者判断的设计决策。就我个人而言，在这种情况下，我会包含自动递增整数主键。能够保证用户名（甚至电子邮件地址）不变是不寻常的。但是，您可以设计软件，以便相同的整数主键始终引用同一用户，而不管该用户记录可能发生哪些其他变化。
对用户名及其对应索引的唯一约束将有助于提高用户名查找的性能。如果您确实希望电子邮件地址是唯一的（主要是业务需求决策），您还可以对电子邮件地址设置 UNIQUE 约束。 MySQL 的默认数据库引擎中会忽略外键（不幸的是），因此我不会费心从数据建模的角度探讨它的好处。
编辑：
我想我会讨论一下外键现在强制执行的好处。是的，有更新依赖于外键的所有数据的规定（例如 ON UPDATE CASCADE）。然而，它们通常很少被理解并且被认为难以维护。通常更好的做法是让外键引用不变的内容，因此您的整数主键。
What you are asking is somewhat a design decision based on the judgment of the individual data modeler. Personally, in this case, I would include the auto-incremented integer primary key. It is unusual to be able to guarantee that username (and even more so e-mail address) will be unchanging. However, you can design your software so that the same integer primary key always refers to the same user, regardless of what else may change about that user record.
What would help the performance of username lookups would be a UNIQUE constraint on username with an index that corresponds to it. If you really want e-mail addresses to be unique (mostly a business requirement decision), you could also put a UNIQUE constraint on e-mail address. Foreign keys are ignored in the default database engine in MySQL (unfortunately), so I won't bother going into the benefits there from a data modeling perspective.
Edit:
I guess I will go into the benefits for foreign keys if they are being enforced now. Yes, there are provisions for updating all the data that depends on a foreign key (such as ON UPDATE CASCADE). However, they are often poorly understood and viewed as difficult to maintain. It is usually a better practice to have a foreign key refer to something unchanging, hence your integer primary key.
我的建议是，经过多年的数据库建设，
当字符不代表现实世界中的任何内容时，仅使用字符作为 PK。
现实世界是一个混乱的地方，一旦你使用其中的PK，你就变成了一个斜坡。
相信我。
（而且速度也有提升）。
问候，
//t
My advice, after years of db-building
only use chars as PK when they don't represent anything in the real world.
The real world is a caotic place, and as soon as you use PK's from it, you're one a slope.
Just trust me.
(and there's a speed gain too).
regards,
//t
它本身不一定是“标准”，但它快速、简单、方便，并且通常能够抵抗业务关键变化。
另请参阅：每个表上自动增量键的优点和缺点
It may not necessarily be a "standard" per se, but it is quick, easy, convenient and generally resistant to business key changes.
See also: Pros and cons of autoincrement keys on every table
随着应用程序的发展，整数作为主键将使您的生活变得更加轻松。对您的用户名和/或电子邮件使用索引来优化查询。
Integers as the primary key will make your life a lot easier down the road as your application evolves. Use an index on your username and/or email for the query optimization.
我喜欢整数键，因为：
I like integer keys because:
整数列上的索引比大字符值上的索引执行得更快。将主键放在窄标识列上是最佳解决方案。
Indexes on Integer columns perform faster than on large character values. Having the primary key on the narrow identity column is the most optimal solution.
使用真实世界的数据作为外键是非常有问题的，并且“效率低下”，因为它们违反了引用完整性。您认为用户名和电子邮件是唯一的并且永远不会改变吗？几乎可以肯定你是错的。阅读关于 自然键
整数自动递增主键会更快，但这不是使用它们的原因。它们之所以被使用是因为它们有效。使用它们。
Using real-world data as foreign keys is very problematic, and "inefficient" because they violate referential integrity. You think that user names and emails are unique and will never change? You are almost certainly wrong. Read an earlier question on natural keys
Integer auto-increment primary keys will be faster, but that's not why they're used. They're used because they work. Use them.