数据库设计：数据库中的用户表必须有哪些字段？
我正在尝试为 MySQL 设计一个用户表。
现在，我的用户表看起来像这样，
users (
BIGINT id,
VARCHAR(?) username,
VARCHAR(?) password,
VARCHAR(254) email,
DATETIME last_login,
DATETIME data_created
)
我还应该包含哪些其他字段以及为什么需要它们？
我应该从上面排除哪些字段，为什么？
我应该为用户名和密码分配多少个字符，为什么？
我应该使用 BIGINT 作为 id 吗？
预先感谢您的帮助。
额外 我将使用社交网站的表格，因此“用户”指的是世界各地的人们。
I am trying to design a user table for MySQL.
for now, my user table looks like this
users (
BIGINT id,
VARCHAR(?) username,
VARCHAR(?) password,
VARCHAR(254) email,
DATETIME last_login,
DATETIME data_created
)
what other fields should I also include and why do I need them?
what fields should I exclude from above and why?
how many characters should I allocate for username and password, and why?
should I use BIGINT for id?
Thank you in advance for your helps.
ADDED
I am going to use the table for social web site, so 'users' mean people around the world.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
一些评论：
BIGINT 很好。我假设您将其用作代理键。在这种情况下，将其声明为
BIGINT id 主键自动递增，
每当你执行插入操作时，Mysql 都会自动为你的 id 分配一个唯一的 int 值（不要为此字段指定任何值）。切勿尝试通过选择最大 id 并向其添加 1 来实现此行为（我已经见过很多次了）。
用户名和密码的长度：这其实没什么大不了的，只需选择一个长度即可。我倾向于将这些东西标准化为 255 长度的 varchar，但这并不是基于任何经验。
将您的表称为“用户”，而不是“用户”。从概念上讲，表实现实体的方式与类实现实体的方式相同。您可能会创建一个名为“user”的类或数据结构，并且表名称应与此相对应。
我创建的每个表都有两个时间戳：“created”和“last_updated”。你已经成功了一半:)
我不认为我会将last_login 存储在用户表中，这可能是您想要登录到单独表中的内容。将所有登录事件（登录、注销、尝试失败、帐户锁定等）存储在日志表中是一个好主意。这将使您更好地了解系统正在执行的操作。
A few comments:
BIGINT is fine. I assume you're using it as a surrogate key. In that case, declare it as
BIGINT id primary key auto_increment,
Mysql will automatically allocate a unique int value to your id whenever you do an insert (don't specify any value for this field). Never try to implement this behaviour by selecting the max id and adding 1 to it (I've seen this so many times).
Length of username and password: this is no big deal really, just pick a length. I tend to standardise on 255 length varchars for these things but that's not based on anything empirical.
Call your table "user", not "users". Conceptually, a table implements an entity in the same way that a class implements an entity. You will likely create a class or data structure called "user" and the table name should correspond to this.
Every table that I create has two timestamps, "created" and "last_updated". You're halfway there:)
I don't think I would store last_login in the user table, this is likely to be something that you will want to log in a separate table. It's a good idea to store all login events (login, logout, failed attempt, account lock etc.) in a logging table. This will give you much better visibility of what the system has been doing.
1/ 用户名和密码：自行决定您希望它们有多大。
2/ BIGINT 很好，尽管整数可能就足够了。但也应将其设置为 UNSIGNED 并且可能设置为 AUTO_INCRMENT。
3/尝试使您的用户表尽可能小：
其余的，您放入额外的表：
这样，您的用户表将仅在添加或删除新用户或有人更改时更改他的密码，比某人登录时出现的频率低。这可以实现更好的表缓存（当您写入表时，MySQL 会清除表缓存）。
1/ Username and password: decide for yourself how large you want these to be.
2/ BIGINT is fine, even though an integer probably suffices. But make it UNSIGNED and probably AUTO_INCREMENT, too.
3/Try keeping your Users table as small as possible:
The rest, you put in extra tables:
This way, your users table will only change when a new user is added or deleted, or when someone changes his password, which is less frequently then when someone logs in. This allows for better table caching (MySQL clears the table cache when you write to the table).
所有这一切都取决于您自己的规格。对于用户名，如果您愿意，可以采用 100，对于密码，可以采用您要使用的散列函数的长度（MD5 为 32）。
更常见的是在表的主键上使用 INTEGER(10) 和 AUTO_INCRMENT。
您可能想询问姓名、出生日期、居住地等。您认为您要求用户提供的所有数据对于您正在构建的平台来说都应该是重要的。
All that just depends on your own specs. For username you could take 100 if you like, for password take the length of the hashing function you want to use (32 for MD5).
It's more common to use INTEGER(10) with AUTO_INCREMENT on the primary key of a table.
You might want to ask for a name, surname, birth date, place of living, etc. Think that all the data you ask the user for should be somehow important to the platform that you are building.
如果您使用类似 POS 系统、银行系统、大学管理系统（其中不同的名称），则应在用户表中添加角色和权限字段用户使用您的开发应用程序。
角色应根据要求具有 VARCHAR、ENUM 或 SET 数据类型。
权限应具有 SET 数据类型或将用户映射到权限的单独表。
You should add roles and permissions fields in the user table if you work on a like POS system, Banking system, University management system where different designations of users use your development application.
Roles should have VARCHAR, ENUM, or SET datatype based on requirements.
Permissions should have SET datatype or a separate table that maps users to permissions.