轉(zhuǎn)載：?https://www.cnblogs.com/jiqing9006/p/5937733.html
淺談數(shù)據(jù)庫用戶表結(jié)構(gòu)設(shè)計疲陕，第三方登錄
說起用戶表方淤，大概是每個應(yīng)用/網(wǎng)站立項動工（碼農(nóng)們）考慮的第一件事情。用戶表結(jié)構(gòu)的設(shè)計蹄殃，算是整個后臺架構(gòu)的基石携茂。如果基石不穩(wěn)，待到后面需求跟進了發(fā)現(xiàn)不能應(yīng)付诅岩，回過頭來反復(fù)修改用戶表讳苦，要大大小小作改動的地方也不少带膜。與其如此，不妨設(shè)計用戶表之初就考慮可拓展性鸳谜，爭取不需要太多額外代價的情況下一步到位膝藕。
先前設(shè)計
id
username
password
用戶名加上密碼，解決簡單需求咐扭，留個id作為其他表的外鍵芭挽。當(dāng)然，那時候密碼還可能是明文存儲草描，好點的知道m(xù)d5览绿。
后來呢，隨著業(yè)務(wù)需求的拓展穗慕，要加個用戶狀態(tài) status 判斷用戶是否被封禁饿敲，注冊時間和注冊IP地址、上次登錄時間和IP地址備查（并衍生出登錄記錄表逛绵，用來判斷是否異地登錄等怀各，在此不表），用戶角色/權(quán)限 role （又衍生出用戶角色權(quán)限關(guān)系术浪，還是另文討論）瓢对，業(yè)務(wù)也需要個人的個人信息如真實姓名、地址等也一股腦往上添加胰苏，現(xiàn)在形成了一個很完整的用戶關(guān)系表硕蛹。
id
username
password
realname
address
…
status
role
register_time
register_ip
login_time
login_ip
現(xiàn)在問題來了，進入Web2.0時代硕并，微博開放了第三方網(wǎng)站登錄法焰，用微博帳號就能登錄我們的網(wǎng)站，老板說倔毙，這個我們得要埃仪。加個微博用戶登錄表吧，當(dāng)然陕赃，得和我們自己的用戶表關(guān)聯(lián)卵蛉，這個微博用戶信息表如下：
id 自增ID
user_id 關(guān)聯(lián)本站用戶ID
uid 微博唯一ID
access_token
access_expire
這還不算完，QQ又開放用戶登錄了么库，一下子要接入好多家第三方登錄了傻丝，只能就著“微博用戶信息表”繼續(xù)加類型加判斷，如果是每個第三方登錄都新建一個表诉儒，肯定會瘋的桑滩。
時代變了，進入了移動互聯(lián)網(wǎng)時代，怎么也得支持個手機號登錄吧运准？所以現(xiàn)在每家標配都是：用戶名/郵箱/手機號登錄幌氮，外加一系列微博、微信等第三方登錄胁澳。表結(jié)構(gòu)如下：
用戶表
id
username
phone
…
用戶第三方登錄表
id
user_id
app_type
app_user_id
access_token
…
用戶在輸入框輸入用戶名/郵箱/手機號和密碼之后该互，后臺判斷是郵箱、手機號或是用戶名韭畸，再根據(jù)條件查詢是否為特定用戶宇智。
這個表結(jié)構(gòu)能夠承載未來一段時間的業(yè)務(wù)需求了。如果說某天冒出了一個新的登錄方式胰丁，比如身份證號登錄随橘，怎么辦？繼續(xù)在用戶表加字段锦庸？我覺得有更好的選擇机蔗。
改進版
無論username+password，還是phone+password甘萧，都是一種用戶信息+密碼的驗證形式萝嘁；再來理解第三方登錄，其實它也是用戶信息+密碼的形式扬卷，用戶信息即第三方系統(tǒng)中的ID（第三方登錄一定會給一個在他們系統(tǒng)中的唯一標識）牙言，密碼即access_token，只不過是一種有使用時效定期修改的密碼怪得。所以我們把它抽象出了用戶基礎(chǔ)信息表加上用戶授權(quán)信息表的形式咱枉。
用戶基礎(chǔ)信息表 users
id
nickname
avatar
用戶授權(quán)信息表 user_auths
id
user_id
identity_type 登錄類型（手機號 郵箱 用戶名）或第三方應(yīng)用名稱（微信 微博等）
identifier 標識（手機號 郵箱 用戶名或第三方應(yīng)用的唯一標識）
credential 密碼憑證（站內(nèi)的保存密碼，站外的不保存或保存token）
這個系統(tǒng)最大的特色就是徒恋，用戶信息表不保存任何密碼蚕断，不保存任何登錄信息（如用戶名、手機號因谎、郵箱）基括，只留有昵稱颜懊、頭像等基礎(chǔ)信息财岔。所有和授權(quán)相關(guān)（且基本前端展示無關(guān)的），都放在用戶信息授權(quán)表河爹，用戶信息表和用戶授權(quán)表是一對多的關(guān)系匠璧。說起來太抽象，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|
說說具體處理咸这，用戶發(fā)來郵箱/用戶名/手機號和密碼請求登錄的時候夷恍，依然是先判斷類型，以某用戶使用了手機號登錄為例媳维，使用 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吓揪。假設(shè)與微信服務(wù)器通信不被劫持的情況下無需判斷憑證問題亲怠。
通過這個表結(jié)構(gòu)設(shè)計，使許多原來糾結(jié)的問題瞬間解決柠辞，說說優(yōu)點吧
一团秽，站內(nèi)登錄類型無限拓展，代碼改動小钾腺。如果真要支持身份證登錄了徙垫，只要少許幾處改動，無需修改表結(jié)構(gòu)放棒。
二姻报，第三方登錄類型可用工場模式批量拓展，新增第三方登錄類型的開發(fā)成本降到最低间螟。
三吴旋，原來條件下，應(yīng)用需要驗證手機號是否已驗證和郵箱是否已驗證厢破，需要相對應(yīng)多一個字段如 phone_verified 和 email_verified荣瑟，如今只要在user_auths表中增加一個統(tǒng)一的verified字段，每種登錄方式都可以直觀看到是否已驗證情況摩泪“恃妫基于信任第三方登錄的數(shù)據(jù)準確性，默認第三方登錄都是已驗證见坑。如果用戶修改登錄手機號或登錄郵箱嚷掠，也能清晰跟蹤每一步的完成度。
四荞驴，可按需綁定任意數(shù)量的同類型登錄方式不皆，即一個用戶可以綁定多個微信，可以有多個郵箱熊楼，可以有多個手機號霹娄，是不是很贊？當(dāng)然你也可以限制一種登錄方式只有一條記錄。
五犬耻，在user_auths添加相應(yīng)的時間和IP地址踩晶，就可以更加完整地跟蹤用戶的使用習(xí)慣，比如枕磁，已經(jīng)不使用微博登錄兩年多合瓢，已經(jīng)綁定微信300天
六，即使完全使用第三方帳號登錄透典，可在前端做到“無需注冊本站帳號”的效果晴楔。過去許多網(wǎng)站雖然支持第三方帳號登錄，但出于留存用戶等原因峭咒，第一次微博登錄回來税弃，讓你再填寫一套他們網(wǎng)站的郵箱、密碼等信息凑队，也就失去了微博登錄的最大意義则果。從技術(shù)上說腌乡，原有的結(jié)構(gòu)導(dǎo)致除了在微博用戶表建立一個條目外榔袋，必須在用戶表建立一條對應(yīng)的條目，而且一般情況下不能讓用戶表里的郵箱或者用戶名和密碼留空张咳。用戶體驗好的叫惊，郵箱自動生成 微博ID@id.weibo.sina.com 款青，密碼則隨機生成。至于體驗不好的霍狰，只能說早知道還不如不用微博登錄呢抡草！現(xiàn)在呢，我們的這個用戶表結(jié)構(gòu)則完全沒有這樣的困擾蔗坯，只要微博提供的昵稱和頭像地址就可以生成這個用戶康震，再關(guān)聯(lián)他的微博登錄記錄。而且我們的表結(jié)構(gòu)意味著宾濒，用戶可以解除他的所有登錄方式腿短，于是這個賬戶變徹底變成了沒法登錄的僵尸（解決辦法是在代碼里加一個限制，至少保留一條user_auths的記錄）绘梦。如果你非得得到用戶的郵箱橘忱，那么每次登錄的時候看到他不存在一條identify_type為email的記錄，則彈窗彈死他谚咬，讓他趕快填郵箱鹦付，否則啥都別干尚粘。
七择卦，提升了邏輯思維能力。抽象出事物本質(zhì)是碼農(nóng)必備職業(yè)素養(yǎng)，通過對用戶表結(jié)構(gòu)的學(xué)習(xí)研究秉继，提高了鄙人的各方面技能祈噪，從此寫代碼一路順風(fēng)順水…
八，如果你說郵箱和手機號就是用戶信息的組成部分尚辑，他們依然需要體現(xiàn)在users表中作為前端展示辑鲤？沒問題，users表盡管拓展杠茬，users表里依然有email,phone月褥，但他們僅僅作為“展示用途”，和昵稱瓢喉、頭像宁赤、或者性別這些屬性沒有本質(zhì)區(qū)別。在用戶信息表與用戶授權(quán)登錄拆分后栓票，用戶信息表可以隨時增加任意字段决左，加星座，加生日走贪，都沒問題佛猛，只需要在前端展示時多幾個輸入框，錄入時多幾行代碼坠狡，與用戶登錄相關(guān)的問題做到最大程度解耦继找。
有利必有弊，說說缺點逃沿。
一码荔，原先的用戶判斷由1次SQL變成2次SQL請求。
二感挥，用戶同時存在郵箱缩搅、用戶名、手機號等多種站內(nèi)登錄方式時触幼，改密碼時必須一起改硼瓣，否則就變成了郵箱+新密碼，手機號+舊密碼訪問了置谦，肯定是很詭異的情況堂鲤。如果考慮到這一點，又要在user_auths表中新增一個表示站內(nèi)登錄方式或第三方登錄方式的標識字段媒峡。
三瘟栖，代碼量增加了，有些情況下邏輯判斷增加了谅阿，難度增大了半哟。舉個例子酬滤，無論用戶是否已登錄，無論用戶是否已注冊過寓涨，都是點擊同一鏈接前往微博第三方授權(quán)后返回盯串，可能出現(xiàn)幾種情況：1，該微博在本站未注冊過戒良，很好体捏，直接給他注冊關(guān)聯(lián)并登錄；2糯崎，該微博已經(jīng)在本站存在几缭，當(dāng)前用戶未登錄，直接登錄成功沃呢；3奏司，該微博未在本站注冊，但當(dāng)前用戶已經(jīng)登錄并關(guān)聯(lián)的是另一個微博帳號樟插，作何處理取決于是否允許綁定多個微博帳號韵洋；4，該微博未在本站注冊過黄锤，當(dāng)前用戶已登錄搪缨，嘗試進行綁定操作；5鸵熟，該微博已經(jīng)注冊副编，用戶又已使用該帳號登錄，為何他重復(fù)綁定自己- -. 6流强，該微博已經(jīng)在本站存在痹届，但當(dāng)前用戶已經(jīng)登錄并關(guān)聯(lián)的是另一個微博帳號，作何處理打月？切換用戶或是報錯队腐？（畫一個流程圖能更好描述這個問題）這個問題與采用的數(shù)據(jù)結(jié)構(gòu)沒有關(guān)系，只是在做第三方帳號注冊登錄時遇到的各種情況奏篙，在此一并整理柴淘。
