wordpress网站导入数据库,遵义城乡住房建设厅网站,网站开发相关技术发展,北京最新封闭小区一个带有用户系统的应用最基本登录方式是站内账号登录#xff0c;但这种方式往往不能满足我们的需求。现在的应用基本都有站内账号、email、手机和一堆第三方登录#xff0c;那么如果需要支持这么多种登录方式#xff0c;或者还有银行卡登录、身份证登录等等更多的登录方式但这种方式往往不能满足我们的需求。现在的应用基本都有站内账号、email、手机和一堆第三方登录那么如果需要支持这么多种登录方式或者还有银行卡登录、身份证登录等等更多的登录方式我们的数据表应该怎么设计才更合理呢需求分析实现多种登录方式并且除了站内账号登录方式以外的登录方式都能够进行绑定和解绑或者更换绑定。如果按照传统的数据表设计我们用户表会存储用户的账号和密码等授权相关的字段类似下面idusernamepasswordnicknamesex...123456idusernamepasswordnicknamesex...但是如果登录方式非常多的情况下这种数据表结构不再适用。那么应该怎么设计呢在查阅了一些资料后本渣渣终于有了一个自我感觉很合理的设计方式。首先一个用户不管有多少种登录方式用户还是只有那一个用户但登录方式却有多种。这就形成了一对多的关系一个用户对应多个登录方式。所以我们就可以把用户表拆分成2张表一张表存储用户基本的数据另一张表存储登录授权相关的数据。我们可以向下面这样设计usersidnicknamesexageemailmobilestatus...12345678idnicknamesexageemailmobilestatus...user_authsid # 自增iduser_id # users表对应的ididentity_type # 身份类型(站内username 邮箱email 手机mobile 或者第三方的qq weibo weixin等等)identifier # 身份唯一标识(存储唯一标识比如账号、邮箱、手机号、第三方获取的唯一标识等)credential # 授权凭证(比如密码 第三方登录的token等)verified # 是否已经验证(存储 1、0 来区分是否已经验证通过)123456id#自增iduser_id#users表对应的ididentity_type#身份类型(站内username邮箱email手机mobile或者第三方的qqweiboweixin等等)identifier#身份唯一标识(存储唯一标识比如账号、邮箱、手机号、第三方获取的唯一标识等)credential#授权凭证(比如密码第三方登录的token等)verified#是否已经验证(存储1、0来区分是否已经验证通过)这样我们创建一个用户首先需要创建一条 users 表的用户基础信息记录和一条或者多条 user_auths 表的授权记录。注意修改密码时也需要同时修改多条 user_auths 记录保证需要密码的登录方式凭证需要同步更新。而第三方的授权凭证和需要密码的授权凭证则不需要同步。代码实现这里我使用 laravel 来实现简单的用户注册、登录、修改密码等操作仅供参考。首先创建2张数据表结构如下userspublic function up(){Schema::create(users, function (Blueprint $table) {$table-increments(id);$table-string(nickname, 30)-default(宝宝)-comment(昵称);$table-string(say)-nullable()-comment(心情寄语);$table-string(avatar, 50)-default(uploads/user/avatar.jpg)-comment(头像);$table-string(mobile, 11)-nullable()-comment(手机号码);$table-string(email, 50)-nullable()-comment(邮箱);$table-tinyInteger(sex)-default(0)-comment(性别 0女 1男);$table-tinyInteger(status)-default(1)-comment(状态 1可用 0 不可用);$table-tinyInteger(is_admin)-default(0)-comment(是否是管理员);$table-tinyInteger(qq_binding)-default(0)-comment(QQ登录是否绑定);$table-tinyInteger(weixin_binding)-default(0)-comment(微信登录是否绑定);$table-tinyInteger(weibo_binding)-default(0)-comment(微博登录是否绑定);$table-tinyInteger(email_binding)-default(0)-comment(邮箱登录是否绑定);$table-tinyInteger(phone_binding)-default(0)-comment(手机登录是否绑定);$table-timestamps();});}1234567891011121314151617181920publicfunctionup(){Schema::create(users,function(Blueprint$table){$table-increments(id);$table-string(nickname,30)-default(宝宝)-comment(昵称);$table-string(say)-nullable()-comment(心情寄语);$table-string(avatar,50)-default(uploads/user/avatar.jpg)-comment(头像);$table-string(mobile,11)-nullable()-comment(手机号码);$table-string(email,50)-nullable()-comment(邮箱);$table-tinyInteger(sex)-default(0)-comment(性别 0女 1男);$table-tinyInteger(status)-default(1)-comment(状态 1可用 0 不可用);$table-tinyInteger(is_admin)-default(0)-comment(是否是管理员);$table-tinyInteger(qq_binding)-default(0)-comment(QQ登录是否绑定);$table-tinyInteger(weixin_binding)-default(0)-comment(微信登录是否绑定);$table-tinyInteger(weibo_binding)-default(0)-comment(微博登录是否绑定);$table-tinyInteger(email_binding)-default(0)-comment(邮箱登录是否绑定);$table-tinyInteger(phone_binding)-default(0)-comment(手机登录是否绑定);$table-timestamps();});}user_authspublic function up(){Schema::create(user_auths, function (Blueprint $table) {$table-increments(id);$table-integer(user_id)-index()-comment(用户id);$table-string(identity_type)-comment(登录类型(手机号phone 邮箱email 用户名username)或第三方应用名称(微信weixin 微博weibo 腾讯QQqq等));$table-string(identifier)-unique()-index()-comment(标识(手机号 邮箱 用户名或第三方应用的唯一标识));$table-string(credential)-nullable()-comment(密码凭证(站内的保存密码站外的不保存或保存token));$table-tinyInteger(verified)-default(0)-comment(是否已经验证);$table-timestamps();});}123456789101112publicfunctionup(){Schema::create(user_auths,function(Blueprint$table){$table-increments(id);$table-integer(user_id)-index()-comment(用户id);$table-string(identity_type)-comment(登录类型(手机号phone 邮箱email 用户名username)或第三方应用名称(微信weixin 微博weibo 腾讯QQqq等));$table-string(identifier)-unique()-index()-comment(标识(手机号 邮箱 用户名或第三方应用的唯一标识));$table-string(credential)-nullable()-comment(密码凭证(站内的保存密码站外的不保存或保存token));$table-tinyInteger(verified)-default(0)-comment(是否已经验证);$table-timestamps();});}实现注册功能创建站内账号一个用户 一个站内账号登录授权。public function register(Request $request){// 已经登录则直接跳转if (Session::has(user)) {return redirect()-route(admin.index);}if ($request-method() GET) {return view(admin.user.register);}// 验证表单$validator Validator::make($request-all(), [identifier [required, between:6,16, unique:user_auths],credential [required, between:6,16, confirmed],], [identifier.required 用户名为必填项,identifier.unique 用户名已经存在,identifier.between 用户名长度必须是6-16,credential.required 密码为必填项,credential.between 密码长度必须是6-16,credential.confirmed 两次输入的密码不一致,]);if ($validator-fails()) {return back()-withErrors($validator);}// 创建用户$user new User();$user-save();// 创建授权$userAuth new UserAuth();$userAuth-user_id $user-id;$userAuth-identity_type username;$userAuth-identifier $request-identifier;$userAuth-credential bcrypt($request-credential);$userAuth-save();return redirect()-route(admin.login);}1234567891011121314151617181920212223242526272829303132333435363738394041publicfunctionregister(Request$request){// 已经登录则直接跳转if(Session::has(user)){returnredirect()-route(admin.index);}if($request-method()GET){returnview(admin.user.register);}// 验证表单$validatorValidator::make($request-all(),[identifier[required,between:6,16,unique:user_auths],credential[required,between:6,16,confirmed],],[identifier.required用户名为必填项,identifier.unique用户名已经存在,identifier.between用户名长度必须是6-16,credential.required密码为必填项,credential.between密码长度必须是6-16,credential.confirmed两次输入的密码不一致,]);if($validator-fails()){returnback()-withErrors($validator);}// 创建用户$usernewUser();$user-save();// 创建授权$userAuthnewUserAuth();$userAuth-user_id$user-id;$userAuth-identity_typeusername;$userAuth-identifier$request-identifier;$userAuth-credentialbcrypt($request-credential);$userAuth-save();returnredirect()-route(admin.login);}实现登录站内账号、邮箱、手机号码登录方式。public function login(Request $request){// 已经登录则直接跳转if (Session::has(user)) {return redirect()-route(admin.index);}if ($request-method() GET) {return view(admin.user.login);}// 验证表单$validator Validator::make($request-all(), [identifier [required, exists:user_auths],credential [required, between:6,16],], [identifier.exists 用户不存在,identifier.required 用户名为必填项,credential.required 密码为必填项,credential.between 密码长度必须是6-16,]);if ($validator-fails()) {return back()-withErrors($validator);}// 查询授权记录 - 查询3种登录方式的授权记录$userAuth UserAuth::where(identifier , $request-identifier)-whereIn(identity_type, [username, phone, email])-first();if (isset($userAuth) Hash::check($request-credential, $userAuth-credential)) {// 查询用户表$user User::find($userAuth-user_id);if ($user-status 0) {return back()-with(errors, 用户已经被禁用);}if ($user-is_admin 0) {return back()-with(errors, 普通用户禁止登陆后台);}Session::put(user, $user);return redirect()-route(admin.index);} else {return back()-with(errors, 管理员密码错误);}}123456789101112131415161718192021222324252627282930313233343536373839404142434445publicfunctionlogin(Request$request){// 已经登录则直接跳转if(Session::has(user)){returnredirect()-route(admin.index);}if($request-method()GET){returnview(admin.user.login);}// 验证表单$validatorValidator::make($request-all(),[identifier[required,exists:user_auths],credential[required,between:6,16],],[identifier.exists用户不存在,identifier.required用户名为必填项,credential.required密码为必填项,credential.between密码长度必须是6-16,]);if($validator-fails()){returnback()-withErrors($validator);}// 查询授权记录 - 查询3种登录方式的授权记录$userAuthUserAuth::where(identifier,$request-identifier)-whereIn(identity_type,[username,phone,email])-first();if(isset($userAuth)Hash::check($request-credential,$userAuth-credential)){// 查询用户表$userUser::find($userAuth-user_id);if($user-status0){returnback()-with(errors,用户已经被禁用);}if($user-is_admin0){returnback()-with(errors,普通用户禁止登陆后台);}Session::put(user,$user);returnredirect()-route(admin.index);}else{returnback()-with(errors,管理员密码错误);}}实现修改密码站内登录、邮箱登录、手机登录同步修改。public function modifyPassword(Request $request){if ($request-method() GET) {return view(admin.user.modify);}// 验证输入字段$validator Validator::make($request-all(), [credential required|between:6,20|confirmed,], [credential.required 新密码不能为空!,credential.between 新密码必须在6-20位之间,credential.confirmed 新密码和确认密码不一致,]);if ($validator-fails()) {return back()-withErrors($validator);}// 判断当前Session里的用户是否还有效$user Session::get(user);if (! isset($user)) {return redirect()-route(admin.login)-with(errors, 登录超时);}// 查询用户权限表,修改密码$userAuths UserAuth::where(user_id, $user-id)-whereIn(identity_type, [username, email, phone])-get();if (count($userAuths) Hash::check($request-credential_o, $userAuths[0]-credential)) {UserAuth::where(user_id, $user-id)-whereIn(identity_type, [username, email, phone])-update([credential bcrypt($request-credential)]);return back()-with(errors, 修改密码成功!);}return back()-with(errors, 原密码错误!);}123456789101112131415161718192021222324252627282930313233343536373839publicfunctionmodifyPassword(Request$request){if($request-method()GET){returnview(admin.user.modify);}// 验证输入字段$validatorValidator::make($request-all(),[credentialrequired|between:6,20|confirmed,],[credential.required新密码不能为空!,credential.between新密码必须在6-20位之间,credential.confirmed新密码和确认密码不一致,]);if($validator-fails()){returnback()-withErrors($validator);}// 判断当前Session里的用户是否还有效$userSession::get(user);if(!isset($user)){returnredirect()-route(admin.login)-with(errors,登录超时);}// 查询用户权限表,修改密码$userAuthsUserAuth::where(user_id,$user-id)-whereIn(identity_type,[username,email,phone])-get();if(count($userAuths)Hash::check($request-credential_o,$userAuths[0]-credential)){UserAuth::where(user_id,$user-id)-whereIn(identity_type,[username,email,phone])-update([credentialbcrypt($request-credential)]);returnback()-with(errors,修改密码成功!);}returnback()-with(errors,原密码错误!);}例子中路由相关代码直接无视如果后期需要新增或删除登录方式只需要新增或删除 user_auths 表中的记录。如果是判断邮箱、手机是否已经验证也只是操作 user_auths 表中的 verified 字段即可。
