使用用户名列而不是 id？
这是我在互联网上找到的用户管理数据库的设置（用伪代码编写）。虽然看起来完全没问题，但我不明白为什么 users 表有一个 id 以及一个唯一的非空 username 列。我不能只使用用户名作为id（主键）吗？
users
(
id integer primary key,
username varchar(100) not null unique key,
pwd varchar(50) not null
);
user_roles
(
user_id integer not null,
role_id integer not null,
unique key (user_id, role_id),
index(user_id)
);
roles
(
id integer primary key,
role varchar(100) not null unique key
);
This is a setup for a database for user management I've found somewhere on the internet (written in pseudocode). While it seems perfectly fine, I don't understand why the users table has an id as well as a unique, not null username column. Couldn't I just use the username as the id (primary key)?
users
(
id integer primary key,
username varchar(100) not null unique key,
pwd varchar(50) not null
);
user_roles
(
user_id integer not null,
role_id integer not null,
unique key (user_id, role_id),
index(user_id)
);
roles
(
id integer primary key,
role varchar(100) not null unique key
);
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我总是求助于 Kimberly L. Tripp 的 集群键的建议（默认情况下，它是 SQL Server 中的主键）。
她的标准是聚类键应该是：
I always fall back to Kimberly L. Tripp's recommendations for a clustering key (which, by default, is the primary key in SQL Server).
Her criteria are that the clustering key should be:
您可以（使用
user_id作为主键）。我看到的唯一障碍是：如果需要更改
user_id（Stephanie Smith 与某个名为 Johnson 的男人结婚并需要她user_id改变了），那么数据库维护就少了。在我工作的地方，几年前有人将我们的数据库设置为使用
user_id作为主键。现在改变这一点将是一场噩梦！另外，
int的搜索速度更快，并且可以自动生成。You could (use the
user_idas the primary key).The only snag I see is this: If ever a
user_idneeds to be changed (Stephanie Smith gets married to some guy named Johnson and needs heruser_idchanged), then it is less database maintenance.Where I work, someone setup our database to use
user_idas the primary key years ago. Changing that now would be a nightmare!Plus, an
intis faster to search and can be auto generated.是的，您可以使用用户名。问题是潜在不稳定的主键是一个痛苦，因为您需要将更改级联到所有 FK。
另外，在连接中使用整数往往会表现更好
您应该注意，造成关键波动性的因素有些主观
另外，正如 JNK 提到的，当聚集索引（通常也是 PK）是增量 int 时，可以进行优化，请参见 Kimberly L. Tripp 的文章 聚集索引争论仍在继续
Yes you could use username. The problem is that a potentially volatile primary keys are a pain because you need to cascade the change to all the FK's.
Also using integers in joins tend to perform better
You should note that what makes a key volatility is somewhat subjective
Also as JNK mentions there is an optimization to be had when a clustered indexes (typically also the PK) is an incremental int See Kimberly L. Tripp's article The Clustered Index Debate Continues
我认为你可以使用用户名作为主键。也许这个例子使用 id 列可以获得更好的性能。我猜搜索 Int Column 比搜索 Text Column 更快。另外，在 user_role 表中还有一个foreign_key user_id。在这种情况下，使用字符串（100）作为外键是没有意义的。
I think you can use the username as primary key. Maybe this example use the id column for a better performance. Is faster to search an Int Column than a text one, i guess. Also in user_role table there is a foreign_key user_id. Does not make sense use a string(100) in this case, as a foreign_key.
这会导致连接效率低下：
int比varchar(100)小得多，因此索引会更小、更快。通常主键是表的聚集索引。That would make joining to it inefficient:
ints are much smaller thanvarchar(100)so the index will be smaller, and quicker. Normally the Primary Key will be the Clustered index of the table.在高层次上，您在这里谈论自然键与代理键。有一些权衡，我认为这两点都已经提到过，但如果您想要更多阅读，我想说这个链接很好地总结了它
"自然键和代理键"
At the high level your talking Natural vs. Surrogate Keys here. There are trade offs, both of which I think have been mentioned, but if you want additional reading I'd say this link sums it up nicely
"Natural and Surrogate Keys"
基本上，ID 是存储在数据库中的自动增量值，用于在数据库脚本中引用，尽管用户名也是唯一列。最大的优点是您的用户不需要记住已分配给系统的任何数字即可登录帐户，而是可以选择自己独特的用户名并轻松交互。
Basically, the ID is an auto incremental value stored in the database used for referencing in DB scripts, though the username is also unique column. The biggest advantage is your user will not need to remember any numeric number that has been assigned to the system to log into the account rather they can choose their own unique usernames and interact easily.