掘金 后端 ( ) • 2024-05-06 10:09
node_koa连接mysql，结合jwt实现登录注册（查库、写库）
jwt koa mysql node

yma16-logo

⭐背景

大家好，我是yma16，本文分享node_koa连接mysql，结合jwt实现登录注册（查库、写库）。

功能：使用jwt实现邮箱验证码注册账号，然后登录。

效果：https://yongma16.xyz/back-front/

注册：

image.png

登录：

image.png

noe_koa系列往期文章

node_koa后端——初始化配置jwt和swagger的koa框架

node_koa后端——连接redis缓存数据

node_koa后端——生成验证码存入redis并使用smtp发送邮件🎈

node_koa封装smtp，使用coze调用api发送html邮件（gmail/outlook）

⭐后端node-koa

登录注册简化为数据库的查询和写入

登录：使用字段匹配查询sql是否存在

注册：在sql中添加字段

✏️ 安装mysql依赖

npm安装mysql库

npm install mysql

mysql的基本连库运行sql用法

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});
 
connection.connect();
 
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});
 
connection.end();

✏️ 封装mysql查询方法

封装查询sql的方法，先建立连接然后执行sql再断开连接。

创建utils/sql/index.js文件

const mysql  = require('mysql');

const config ={
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'threejs_data'
};

const execMysql=(sql)=>{
    return new Promise((resolve,reject)=>{
        // 连接mysql
        const connection = mysql.createConnection(config);
        try{
            connection.query(sql, function (error, results) {
                if (error){
                    reject(error)
                };

                resolve(results)
            });


        }
        catch (e) {
            console.log(JSON.stringify(e))
            reject(e)
        }
        finally {
            connection.end()
        }
    })
};

module.exports={
    execMysql
}

✏️ 创建用户表user

建表的sql如下

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80016
 Source Host           : localhost:3306
 Source Schema         : threejs_data

 Target Server Type    : MySQL
 Target Server Version : 80016
 File Encoding         : 65001

 Date: 05/05/2024 17:40:51
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '加密的密码',
  `real_pwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '真实密码',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'token',
  `lasted_login_time` datetime(0) NULL DEFAULT NULL COMMENT '最近登录时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

✏️ 注册逻辑实现

账号密码和邮箱验证码传入，写入user表之后生成一个token

const Router = require('koa-router');
const router = new Router();
const {execMysql}=require('../../utils/mysql/index')
const {decrypt}=require('../../utils/aes/index')
const jwtToken = require("jsonwebtoken");
const {uuid,getCurrentTime}=require('../../utils/index');

const {getRedisKey}=require('../../utils/redis/index');

//appKey
const {appKey}=require('../../common/const')


// 注册
router.post('/register', async (ctx) => {
   try{
       // 解析参数
       const bodyParams =  ctx.request.body
       const {username,password,emailCode} = bodyParams;

       if(!username||!password){
           return ctx.body = {
               code: 0 ,
               msg:'username or password is null'
           };
       }
       const emailRedisCode=await getRedisKey(username)
       if(emailCode!==emailRedisCode){
           return ctx.body = {
               code: 0 ,
               msg:'email code is error'
           };
       }
       // 查询重复
       const search=await execMysql(`select count(1) as total from user where username='${username}';`)
       console.log('search',search)
       if(search[0].total>0){
           return ctx.body = {
               code: 0 ,
               msg:'user is exist'
           };
       }
       // id 唯一字符
       const id= uuid()

       const create_time=getCurrentTime()
       console.log('password',password)
       const real_pwd=await decrypt(password)
       console.log('real_pwd',real_pwd)
       // 插入 数据
       const createRes=await execMysql(`INSERT INTO user (id,username,password,real_pwd,create_time) VALUES ('${id}', '${username}','${password}','${real_pwd}','${create_time}');`)

       // 更新token update_time
       const token=jwtToken.sign(
           {
               username,
               password
           },
           appKey, // secret
           { expiresIn: 24 * 60 * 60 } // 60 * 60 s
       )
       const update_time=getCurrentTime()
       const tokenRes=await execMysql(`update user set token='${token}', update_time='${update_time}' where username='${username}';`)
       ctx.body = {
           code:200,
           data:{
               createSqlData:createRes,
               tokenSqlData:tokenRes
           },
           msg:' insert success',
           token:token
       };
   }
   catch (e) {
       ctx.body = {
           code:0,
           msg:JSON.stringify(e)
       };
   }
});

✏️ 登录功能实现

账号密码去查库然后获取token去登录

const Router = require('koa-router');
const router = new Router();
const {execMysql}=require('../../utils/mysql/index')
const {decrypt}=require('../../utils/aes/index')
const jwtToken = require("jsonwebtoken");
const {uuid,getCurrentTime}=require('../../utils/index');

const {getRedisKey}=require('../../utils/redis/index');

//appKey
const {appKey}=require('../../common/const')


// 获取token
router.post('/token/gen', async (ctx) => {
    try{
        // 解析参数
        const bodyParams =  ctx.request.body
        const {username,password} = bodyParams;
        const real_pwd=await decrypt(password);
        // 查询 用户
        const search=await execMysql(`select count(1) as total from user where username='${username}' and real_pwd='${real_pwd}';`);
        console.log('search',search)
        if(search[0].total>0){
            // 更新token update_time
            const token=jwtToken.sign(
                {
                    username,
                    password
                },
                appKey, // secret
                { expiresIn: 24 * 60 * 60 } // 60 * 60 s
            )
            const update_time=getCurrentTime()
            // 更新token
            const tokenRes=await execMysql(`update user set token='${token}', update_time='${update_time}' where username='${username}';`)
            // 配置token
            const AUTHORIZATION='Authorization'
            ctx.set(AUTHORIZATION)
            return ctx.body = {
                code:200,
                msg:'gen token  success',
                token:token
            };
        }

        return ctx.body = {
            code:0,
            token:'',
            msg:' gen token fail',
        };
    }
    catch (e) {
        ctx.body = {
            code:0,
            msg:e
        };
    }
});

// token 登录
router.post('/token/login',async (ctx) => {
    try{
        // 解析参数
        const bodyParams =  ctx.request.body
        const {token} = bodyParams;
        const payload = jwtToken.verify(token, appKey);
        const {username,password} =payload
        const real_pwd=await decrypt(password);
        // 查询 用户
        const search=await execMysql(`select count(1) as total from user where username='${username}' and real_pwd='${real_pwd}';`)
        console.log(search)
        if(search[0].total>0){
            const last_login_time=getCurrentTime()
            // last_login_time  登录时间
            const tokenRes=await execMysql(`update user set lasted_login_time='${last_login_time}' where username='${username}' and password='${password}';`)
            return ctx.body = {
                code:200,
                msg:'login success',
                data:{
                    username
                }
            };
        }

        ctx.body = {
            code:0,
            msg:' login fail',
        };
    }
    catch (e) {
        console.log('e',e)
        ctx.body = {
            code:0,
            msg:JSON.stringify(e)
        };
    }
});

💖完整登录注册代码块

const Router = require('koa-router');
const router = new Router();
const {execMysql}=require('../../utils/mysql/index')
const {decrypt}=require('../../utils/aes/index')
const jwtToken = require("jsonwebtoken");
const {uuid,getCurrentTime}=require('../../utils/index');

const {getRedisKey}=require('../../utils/redis/index');

//appKey
const {appKey}=require('../../common/const')


// 注册
router.post('/register', async (ctx) => {
   try{
       // 解析参数
       const bodyParams =  ctx.request.body
       const {username,password,emailCode} = bodyParams;
       console.log('emailCode',emailCode)
       console.log('emailCode',emailCode)

       if(!username||!password){
           return ctx.body = {
               code: 0 ,
               msg:'username or password is null'
           };
       }
       const emailRedisCode=await getRedisKey(username)
       console.log('emailRedisCode',emailRedisCode)
       if(emailCode!==emailRedisCode){
           return ctx.body = {
               code: 0 ,
               msg:'email code is error'
           };
       }
       // 查询重复
       const search=await execMysql(`select count(1) as total from user where username='${username}';`)
       console.log('search',search)
       if(search[0].total>0){
           return ctx.body = {
               code: 0 ,
               msg:'user is exist'
           };
       }
       // id 唯一字符
       const id= uuid()

       const create_time=getCurrentTime()
       console.log('password',password)
       const real_pwd=await decrypt(password)
       console.log('real_pwd',real_pwd)
       // 插入 数据
       const createRes=await execMysql(`INSERT INTO user (id,username,password,real_pwd,create_time) VALUES ('${id}', '${username}','${password}','${real_pwd}','${create_time}');`)

       // 更新token update_time
       const token=jwtToken.sign(
           {
               username,
               password
           },
           appKey, // secret
           { expiresIn: 24 * 60 * 60 } // 60 * 60 s
       )
       const update_time=getCurrentTime()
       const tokenRes=await execMysql(`update user set token='${token}', update_time='${update_time}' where username='${username}';`)
       ctx.body = {
           code:200,
           data:{
               createSqlData:createRes,
               tokenSqlData:tokenRes
           },
           msg:' insert success',
           token:token
       };
   }
   catch (e) {
       ctx.body = {
           code:0,
           msg:JSON.stringify(e)
       };
   }
});

// 获取token
router.post('/token/gen', async (ctx) => {
    try{
        // 解析参数
        const bodyParams =  ctx.request.body
        const {username,password} = bodyParams;
        const real_pwd=await decrypt(password);
        // 查询 用户
        const search=await execMysql(`select count(1) as total from user where username='${username}' and real_pwd='${real_pwd}';`);
        console.log('search',search)
        if(search[0].total>0){
            // 更新token update_time
            const token=jwtToken.sign(
                {
                    username,
                    password
                },
                appKey, // secret
                { expiresIn: 24 * 60 * 60 } // 60 * 60 s
            )
            const update_time=getCurrentTime()
            // 更新token
            const tokenRes=await execMysql(`update user set token='${token}', update_time='${update_time}' where username='${username}';`)
            // 配置token
            const AUTHORIZATION='Authorization'
            ctx.set(AUTHORIZATION)
            return ctx.body = {
                code:200,
                msg:'gen token  success',
                token:token
            };
        }

        return ctx.body = {
            code:0,
            token:'',
            msg:' gen token fail',
        };
    }
    catch (e) {
        ctx.body = {
            code:0,
            msg:e
        };
    }
});

// token 登录
router.post('/token/login',async (ctx) => {
    try{
        // 解析参数
        const bodyParams =  ctx.request.body
        const {token} = bodyParams;
        const payload = jwtToken.verify(token, appKey);
        const {username,password} =payload
        const real_pwd=await decrypt(password);
        // 查询 用户
        const search=await execMysql(`select count(1) as total from user where username='${username}' and real_pwd='${real_pwd}';`)
        console.log(search)
        if(search[0].total>0){
            const last_login_time=getCurrentTime()
            // last_login_time  登录时间
            const tokenRes=await execMysql(`update user set lasted_login_time='${last_login_time}' where username='${username}' and password='${password}';`)
            return ctx.body = {
                code:200,
                msg:'login success',
                data:{
                    username
                }
            };
        }

        ctx.body = {
            code:0,
            msg:' login fail',
        };
    }
    catch (e) {
        console.log('e',e)
        ctx.body = {
            code:0,
            msg:JSON.stringify(e)
        };
    }
});



module.exports = router;

登录成功

image.png

⭐结束

本文分享到这结束，如有错误或者不足之处欢迎指出！

image.png

👍 点赞，是我创作的动力！

⭐️ 收藏，是我努力的方向！

✏️ 评论，是我进步的财富！

💖 最后，感谢你的阅读！