MySQL教程之浅谈数据库用户表结构设计
发布时间：2022-08-10 09:54:25 所属栏目：MySql教程 来源：互联网
导读：说起用户表，大概是每个应用/网站立项动工（码农们）考虑的第一件事情。用户表结构的设计，算是整个后台架构的基石。如果基石不稳，待到后面需求跟进了发现不能应付，回过头来反复修改用户表，要大大小小作改动的地方也不少。与其如此，不妨设计用户表之初就
|
说起用户表，大概是每个应用/网站立项动工（码农们）考虑的第一件事情。用户表结构的设计，算是整个后台架构的基石。如果基石不稳，待到后面需求跟进了发现不能应付，回过头来反复修改用户表，要大大小小作改动的地方也不少。与其如此，不妨设计用户表之初就考虑可拓展性，争取不需要太多额外代价的情况下一步到位。 先前设计 id username password 用户名加上密码，解决简单需求，留个id作为其他表的外键。当然，那时候密码还可能是明文存储，好点的知道md5。 后来呢，随着业务需求的拓展，要加个用户状态 status 判断用户是否被封禁，注册时间和注册IP地址、上次登录时间和IP地址备查（并衍生出登录记录表，用来判断是否异地登录等，在此不表），用户角色/权限 role （又衍生出用户角色权限关系，还是另文讨论），业务也需要个人的个人信息如真实姓名、地址等也一股脑往上添加，现在形成了一个很完整的用户关系表。 id username password realname address … status role register_time register_ip login_time login_ip 现在问题来了，进入Web2.0时代，微博开放了第三方网站登录，用微博帐号就能登录我们的网站，老板说，这个我们得要。加个微博用户登录表吧，当然，得和我们自己的用户表关联，这个微博用户信息表如下： id 自增ID user_id 关联本站用户ID uid 微博唯一ID access_token access_expire 这还不算完，QQ又开放用户登录了，一下子要接入好多家第三方登录了，只能就着“微博用户信息表”继续加类型加判断，如果是每个第三方登录都新建一个表，肯定会疯的。 时代变了，进入了移动互联网时代，怎么也得支持个手机号登录吧？所以现在每家标配都是：用户名/邮箱/手机号登录，外加一系列微博、微信等第三方登录。表结构如下： 用户表 id username phone … 用户第三方登录表 id user_id app_type app_user_id access_token … 用户在输入框输入用户名/邮箱/手机号和密码之后，后台判断是邮箱、手机号或是用户名，再根据条件查询是否为特定用户。 这个表结构能够承载未来一段时间的业务需求了。如果说某天冒出了一个新的登录方式，比如身份证号登录，怎么办？继续在用户表加字段？我觉得有更好的选择。 改进版 无论username+password，还是phone+password，都是一种用户信息+密码的验证形式；再来理解第三方登录，其实它也是用户信息+密码的形式，用户信息即第三方系统中的ID（第三方登录一定会给一个在他们系统中的唯一标识），密码即access_token，只不过是一种有使用时效定期修改的密码。所以我们把它抽象出了用户基础信息表加上用户授权信息表的形式。 用户基础信息表 users id nickname avatar 用户授权信息表 user_auths id user_id identity_type 登录类型（手机号 邮箱 用户名）或第三方应用名称（微信 微博等） identifier 标识（手机号 邮箱 用户名或第三方应用的唯一标识） credential 密码凭证（站内的保存密码，站外的不保存或保存token） 这个系统最大的特色就是，用户信息表不保存任何密码，不保存任何登录信息（如用户名、手机号、邮箱），只留有昵称、头像等基础信息。所有和授权相关（且基本前端展示无关的），都放在用户信息授权表，用户信息表和用户授权表是一对多的关系。说起来太抽象，show me the code. users |id|nickname|avatar| |1|慕容雪村|http://…/avatar.jpg| |2|魔力鸟|http://…/avatar2.jpg| |3|科比|http://…/avatar3.jpg| user_auths |id|user_id|identity_type|identifier|credential| |1|1|email|123@example.com|password_hash(密码)| |2|1|phone|13888888888|password_hash(密码)| |3|1|weibo|微博UID|微博access_token| |4|2|username|moliniao|password_hash(密码)| |5|3|weixin|微信UserName|微信token| 说说具体处理，用户发来邮箱/用户名/手机号和密码请求登录的时候，依然是先判断类型，以某用户使用了手机号登录为例，使用 SELECT * FROM user_auths WHERE type=’phone’ and identifier=’手机号’ 查找条目，如有，取出并判断password_hash(密码)是否和该条目的credential相符，相符则通过验证，随后通过user_id获取用户信息。 如果使用第三方登录，则只要判断 SELECT * FROM user_auths WHERE type=’weixin’ and identifier=’微信UserName’ ，如果有记录，则直接登录成功，使用新的token更新原token。假设与微信服务器通信不被劫持的情况下无需判断凭证问题。 通过这个表结构设计，使许多原来纠结的问题瞬间解决，说说优点吧 一，站内登录类型无限拓展，代码改动小。如果真要支持身份证登录了，只要少许几处改动，无需修改表结构。 二，第三方登录类型可用工场模式批量拓展，新增第三方登录类型的开发成本降到最低。 三，原来条件下，应用需要验证手机号是否已验证和邮箱是否已验证，需要相对应多一个字段如 phone_verified 和 email_verified，如今只要在user_auths表中增加一个统一的verified字段，每种登录方式都可以直观看到是否已验证情况。基于信任第三方登录的数据准确性，默认第三方登录都是已验证。如果用户修改登录手机号或登录邮箱，也能清晰跟踪每一步的完成度。 四，可按需绑定任意数量的同类型登录方式，即一个用户可以绑定多个微信，可以有多个邮箱，可以有多个手机号，是不是很赞？当然你也可以限制一种登录方式只有一条记录。 五，在user_auths添加相应的时间和IP地址，就可以更加完整地跟踪用户的使用习惯，比如，已经不使用微博登录两年多，已经绑定微信300天 六，即使完全使用第三方帐号登录，可在前端做到“无需注册本站帐号”的效果。过去许多网站虽然支持第三方帐号登录，但出于留存用户等原因，第一次微博登录回来，让你再填写一套他们网站的邮箱、密码等信息，也就失去了微博登录的最大意义。从技术上说，原有的结构导致除了在微博用户表建立一个条目外，必须在用户表建立一条对应的条目，而且一般情况下不能让用户表里的邮箱或者用户名和密码留空。用户体验好的，邮箱自动生成 微博ID@id.weibo.sina.com ，密码则随机生成。至于体验不好的，只能说早知道还不如不用微博登录呢！现在呢，我们的这个用户表结构则完全没有这样的困扰，只要微博提供的昵称和头像地址就可以生成这个用户，再关联他的微博登录记录。而且我们的表结构意味着，用户可以解除他的所有登录方式，于是这个账户变彻底变成了没法登录的僵尸（解决办法是在代码里加一个限制，至少保留一条user_auths的记录）。如果你非得得到用户的邮箱，那么每次登录的时候看到他不存在一条identify_type为email的记录，则弹窗弹死他，让他赶快填邮箱，否则啥都别干。 （编辑：滨州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
