检查用户是否激活他/她的帐户的最有效方法？
我希望我的用户在登录之前激活他们的帐户。注册后，他们会收到一封包含激活链接的电子邮件，如下所示：
http://www.blabla.com/[电子邮件受保护]&token=Aisd23uNMAu53932asdDasd82AS
当然，每当有人登录时，我必须检查该用户是否已激活他/她的帐户。我可以想出两种方法来解决这个问题，要么在我的“用户”表中添加一个额外的列，每当用户激活时该列就会设置为空，如下所示：
-----------------------------------------------
| id | username | password | activation_token |
-----------------------------------------------
| 1 | user1 | blabla | |
-----------------------------------------------
| 2 | user1 | blabla | asd232DA34qADJs2 |
-----------------------------------------------
然后，每当用户登录时，我都会提取activation_token以及用户信息或者我可以有一个单独的表，其中仅包含激活令牌，然后每次用户登录时都会将其加入到“用户”表中：
--------------------------------------
| id | account_id | activation_token |
--------------------------------------
| 1 | 37 | dsad2428491dka98 |
--------------------------------------
| 2 | 2 | asd232DA34qADJs2 |
--------------------------------------
那么哪一个最有效？感谢您抽出时间。
编辑：感谢您的所有精彩回复
I want my users to activate their accounts before they are able to login. They are sent an email after registration containing an activation link, something like this:
http://www.blabla.com/[email protected]&token=Aisd23uNMAu53932asdDasd82AS
Of course, whenever someone logs in, I have to check wether or not that user has activated his/her account. I can think of 2 ways to solve this problem, either have an extra column in my 'users' table, which is set to empty whenever a user activates like so:
-----------------------------------------------
| id | username | password | activation_token |
-----------------------------------------------
| 1 | user1 | blabla | |
-----------------------------------------------
| 2 | user1 | blabla | asd232DA34qADJs2 |
-----------------------------------------------
Then I extract the activation_token along with the user-information whenever a users logs in. Or I could have a seperate table that contains activation tokens only, which is then joined on the 'users' table everytime a user logs in:
--------------------------------------
| id | account_id | activation_token |
--------------------------------------
| 1 | 37 | dsad2428491dka98 |
--------------------------------------
| 2 | 2 | asd232DA34qADJs2 |
--------------------------------------
So which one would be most efficient? Thanks for your time.
EDIT: Thanks for all the great responses
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
就个人而言，我会结合两者...
其中状态是
TINYINT(1)字段，其中 0 表示已停用的用户，1 表示已激活的用户。这样，您可以快速了解用户的“状态”...然后，将令牌存储在另一个表中（就像您已经拥有的一样）...这样，您不需要加入或检查未激活帐户时的字符串列...
Personally, I'd do a combination of the two...
Where the status is a
TINYINT(1)field which is 0 for deactivated users, and 1 for activated users. That way, you can tell really quickly the "status" of the user...Then, store the token in another table (just like you already have)... That way, you don't need to join, or check a string column when not activating the account...
使用第一个选项 - 将
isactivated列添加到USERS表中。不需要单独的表 - 这是一对一的关系。
Use the first option - add an
isactivatedcolumn to theUSERStable.There's no need for a separate table - this is a one-to-one relationship.
将令牌存储在 Users 表中而不是单独的表中意味着您不必在每个查询期间连接它们，这会稍微快一些。
此外，您不会存储 userId 并为该令牌表创建新的 Id，这将节省数据存储。
Storing the token in the Users table rather than a seperate table will mean that you don't have to join them during each query, which will be slightly quicker.
Also, you're not storing the userIds and creating a new Id for that tokens table, which will save on the data storage.
我将有一个整数字段“已激活”，默认为 0。当有人尝试进行身份验证时，您只会查找“已激活”帐户。我将身份验证令牌存储在一个单独的表中，就像您所描述的那样。
I would have an integer field, Activated, that is defaulted to 0. When someone attempts authentication, you would only look for Activated accounts. I store auth tokens in a separate table like you have described.
如果关系是 1-1（例如，激活表每个帐户 ID 有 1 行），那么采用完全规范化的 2 表方法就有点过分了。
这两种方法都不会有大问题，但单表方法更容易。
如果您采用双表方法，则应在用户表中存储“已激活”是/否标志，因此您无需出于用户登录目的而加入第二个表。
If the relationship is 1-1 (e.g. the activation table would have 1 row per account id), then doing a fully normalized 2-table approach is an overkill.
You would not have major problem with either approach but 1-table one is easier.
If you go with 2-table approach, you should store "activated" yes/no flag in the user table, so you don't need to join to a second table for user login purpose.
如果激活令牌仅用于验证“单击此处激活您的帐户”链接并且不再使用，那么在存储
char(32)的用户表中浪费空间是没有意义的（或无论它是什么）一次性使用的字段。将激活令牌放在一个单独的表中，当用户单击激活时，您的帐户激活脚本可以引用该表。激活完成后，您可以从该单独的表中删除令牌的记录。在用户表中放置一个“is_activated”布尔/位字段，您的登录脚本可以在登录过程中检查该字段（如果该字段为 null/false，则输出“嘿，您尚未激活”错误）。
当然，现在磁盘空间很便宜。即使一百万个用户每个拥有 32 个字符的激活令牌也只会“浪费”32meg 的空间。 TB 驱动器的价格低于 100 美元，相当于磁盘的 0.00305%，成本基本上为 0.00 美元（0.305 美分）。
If the activation token is only ever used to validate the 'click here to activate your account' link and is never used again, then there's no point in wasting space in your user table storing the
char(32)(or whatever it is) field for a one-time usage. Put the activation tokens in a seperate table that your account activation script can refer to when the user clicks through to activate. Once the activation's completed, you can delete the token's record from that seperate table.Put an 'is_activated' boolean/bit field in the user table that your login script can check during the login process (and output a "hey, you haven't activated yet" error if the field's null/false).
Of course, disk space is cheap these days. Even a million users each with a 32char activation token will only 'waste' 32meg of space. With a terabyte drive going for less than $100, that's 0.00305% of the disk, and essentially $0.00 cost (0.305 cents).
我认为不需要将激活令牌存储在数据库中。像 md5('users@email' . 'secret') 这样的东西就可以正常工作。至于用户状态，我同意其他人的观点，在用户表中使用单独的专用“状态”列。另一个优点是该列还可以存储其他状态（例如“禁止”；）
I don't think there's a need to store the activation token in DB. Something like md5('users@email' . 'secret') will work just fine. As for user status, i agree with others, use a separate dedicate "status" column in the users table. An additional advantage is that this column can store others stati as well (e.g. "banned" ;)
在我看来，激活码不应存储在用户表中，而是保留一个默认将其设置为关闭的标志。当用户单击任何激活链接时，更新表并将标志设置为打开。
登录之前检查该标志是否打开。
如果标志关闭，则用户尚未单击激活链接。然后您可以向用户提供错误消息。
如果标志打开，则用户可以成功登录。
In my opinion instead of activation code being stored in users table, keep a flag set it off by default. when a user clicks on any activation link, then update the table and set the flag on.
Before logging in check the flag is on or not.
If flag is off then the user has not clicked on the activation link. then you can give a error message to the user.
If flag is on then the user can log in successfully.