2023年7月27日发(作者：)

浅谈数据库⽤户表结构设计本⽂转载⾃1. 导语说起⽤户表，⼤概是每个应⽤/⽹站⽴项动⼯（码农们）考虑的第⼀件事情。⽤户表结构的设计，算是整个后台架构的基⽯。如果基⽯不稳，待到后⾯需求跟进了发现不能应付，回过头来反复修改⽤户表，要⼤⼤⼩⼩作改动的地⽅也不少。与其如此，不妨设计⽤户表之初就考虑可拓展性，争取不需要太多额外代价的情况下⼀步到位。2. 先前设计idusernamepassword⽤户名加上密码，解决简单需求，留个id作为其他表的外键。当然，那时候密码还可能是明⽂存储，好点的知道md5。后来呢，随着业务需求的拓展，要加个⽤户状态 status 判断⽤户是否被封禁，注册时间和注册IP地址、上次登录时间和IP地址备查（并衍⽣出登录记录表，⽤来判断是否异地登录等，在此不表），⽤户⾓⾊/权限 role （⼜衍⽣出⽤户⾓⾊权限关系，还是另⽂讨论），业务也需要个⼈的个⼈信息如真实姓名、地址等也⼀股脑往上添加，现在形成了⼀个很完整的⽤户关系表。idusernamepasswordrealnameaddress…statusroleregister_timeregister_iplogin_timelogin_ip现在问题来了，进⼊Web2.0时代，微博开放了第三⽅⽹站登录，⽤微博帐号就能登录我们的⽹站，⽼板说，这个我们得要。加个微博⽤户登录表吧，当然，得和我们⾃⼰的⽤户表关联，这个微博⽤户信息表如下：id ⾃增IDuser_id 关联本站⽤户IDuid 微博唯⼀IDaccess_tokenaccess_expire这还不算完，QQ⼜开放⽤户登录了，⼀下⼦要接⼊好多家第三⽅登录了，只能就着“微博⽤户信息表”继续加类型加判断，如果是每个第三⽅登录都新建⼀个表，肯定会疯的。时代变了，进⼊了移动互联⽹时代，怎么也得⽀持个⼿机号登录吧？所以现在每家标配都是：⽤户名/邮箱/⼿机号登录，外加⼀系列微博、微信等第三⽅登录。表结构如下：⽤户表idusernameemailphone…⽤户第三⽅登录表iduser_idapp_typeapp_user_idaccess_token…⽤户在输⼊框输⼊⽤户名/邮箱/⼿机号和密码之后，后台判断是邮箱、⼿机号或是⽤户名，再根据条件查询是否为特定⽤户。这个表结构能够承载未来⼀段时间的业务需求了。如果说某天冒出了⼀个新的登录⽅式，⽐如⾝份证号登录，怎么办？继续在⽤户表加字段？我觉得有更好的选择。3. 改进版⽆论username+password，还是phone+password，都是⼀种⽤户信息+密码的验证形式；再来理解第三⽅登录，其实它也是⽤户信息+密码的形式，⽤户信息即第三⽅系统中的ID（第三⽅登录⼀定会给⼀个在他们系统中的唯⼀标识），密码即access_token，只不过是⼀种有使⽤时效定期修改的密码。所以我们把它抽象出了⽤户基础信息表加上⽤户授权信息表的形式。⽤户基础信息表 usersidnicknameavatar⽤户授权信息表 user_authsiduser_ididentity_type 登录类型（⼿机号 邮箱 ⽤户名）或第三⽅应⽤名称（微信 微博等）identifier 标识（⼿机号 邮箱 ⽤户名或第三⽅应⽤的唯⼀标识）credential 密码凭证（站内的保存密码，站外的不保存或保存token）这个系统最⼤的特⾊就是，⽤户信息表不保存任何密码，不保存任何登录信息（如⽤户名、⼿机号、邮箱），只留有昵称、头像等基础信息。所有和授权相关（且基本前端展⽰⽆关的），都放在⽤户信息授权表，⽤户信息表和⽤户授权表是⼀对多的关系。说起来太抽象，show me the _auths|id|user_id|identity_type|identifier|credential||1|1|email|***************|password_hash(密码)||2|1|phone|138****8888|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天即使完全使⽤第三⽅帐号登录，可在前端做到“⽆需注册本站帐号”的效果。过去许多⽹站虽然⽀持第三⽅帐号登录，但出于留存⽤户等原因，第⼀次微博登录回来，让你再填写⼀套他们⽹站的邮箱、密码等信息，也就失去了微博登录的最⼤意义。从技术上说，原有的结构导致除了在微博⽤户表建⽴⼀个条⽬外，必须在⽤户表建⽴⼀条对应的条⽬，⽽且⼀般情况下不能让⽤户表⾥的邮箱或者⽤户名和密码留空。⽤户体验好的，邮箱⾃动⽣成 微博***********.，密码则随机⽣成。⾄于体验不好的，只能说早知道还不如不⽤微博登录呢！现在呢，我们的这个⽤户表结构则完全没有这样的困扰，只要微博提供的昵称和头像地址就可以⽣成这个⽤户，再关联他的微博登录记录。⽽且我们的表结构意味着，⽤户可以解除他的所有登录⽅式，于是这个账户变彻底变成了没法登录的僵⼫（解决办法是在代码⾥加⼀个限制，⾄少保留⼀条user_auths的记录）。如果你⾮得得到⽤户的邮箱，那么每次登录的时候看到他不存在⼀条identify_type为email的记录，则弹窗弹死他，让他赶快填邮箱，否则啥都别⼲。提升了逻辑思维能⼒。抽象出事物本质是码农必备职业素养，通过对⽤户表结构的学习研究，提⾼了鄙⼈的各⽅⾯技能，从此写代码⼀路顺风顺⽔…如果你说邮箱和⼿机号就是⽤户信息的组成部分，他们依然需要体现在users表中作为前端展⽰？没问题，users表尽管拓展，users表⾥依然有email,phone，但他们仅仅作为“展⽰⽤途”，和昵称、头像、或者性别这些属性没有本质区别。在⽤户信息表与⽤户授权登录拆分后，⽤户信息表可以随时增加任意字段，加星座，加⽣⽇，都没问题，只需要在前端展⽰时多⼏个输⼊框，录⼊时多⼏⾏代码，与⽤户登录相关的问题做到最⼤程度解耦。有利必有弊，说说缺点。原先的⽤户判断由1次SQL变成2次SQL请求。⽤户同时存在邮箱、⽤户名、⼿机号等多种站内登录⽅式时，改密码时必须⼀起改，否则就变成了邮箱+新密码，⼿机号+旧密码访问了，肯定是很诡异的情况。如果考虑到这⼀点，⼜要在user_auths表中新增⼀个表⽰站内登录⽅式或第三⽅登录⽅式的标识字段。代码量增加了，有些情况下逻辑判断增加了，难度增⼤了。举个例⼦，⽆论⽤户是否已登录，⽆论⽤户是否已注册过，都是点击同⼀链接前往微博第三⽅授权后返回，可能出现⼏种情况：1，该微博在本站未注册过，很好，直接给他注册关联并登录；2，该微博已经在本站存在，当前⽤户未登录，直接登录成功；3，该微博未在本站注册，但当前⽤户已经登录并关联的是另⼀个微博帐号，作何处理取决于是否允许绑定多个微博帐号；4，该微博未在本站注册过，当前⽤户已登录，尝试进⾏绑定操作；5，该微博已经注册，⽤户⼜已使⽤该帐号登录，为何他重复绑定⾃⼰- -. 6，该微博已经在本站存在，但当前⽤户已经登录并关联的是另⼀个微博帐号，作何处理？切换⽤户或是报错？（画⼀个流程图能更好描述这个问题）这个问题与采⽤的数据结构没有关系，只是在做第三⽅帐号注册登录时遇到的各种情况，在此⼀并整理。