Node链接MySQL数据库

1.配置数据库基本信息文件

let MYSQL_CONF, REDIS_CONF     //创建数据库

MYSQL_CONF = {                //MySQL数据库信息
    host: 'localhost',  //地址
    port: '3306',       // 端口
    user: 'root',       //账号
    password: 'root',   //密码
    database:'express',  //库
}

REDIS_CONF = {       
    port: 6379,
    host: '127.0.0.1'
}

module.exports = {    //导出
    MYSQL_CONF,
    REDIS_CONF
}

 

2.配置数据库

const mysql = require('mysql')                  //引入mysql
const { MYSQL_CONF } = require("../conf/db.js")     //引入mysql基本配置
const con = mysql.createPool(MYSQL_CONF)        //数据池

const exec = (sql) =>{                          //sql传入mysql语句
    return new Promise((resolve,reject)=>{      //新建一个promise
        if(!sql){                               //判断有无sql语句
            resolve({})                         //无sql语句返回{}
            return
        }
  
        con.query(sql,(err,res)=>{              //con.query()
            if(err){                
                reject(err)                     //判断如果有错误返回错误并且暂停
                return
            }
            resolve(res)                           //返回数据
        })
    })
}

module.exports = {
    exec,
    escape: mysql.escape                //导出mysql一个方法
}

 

3.封装方法

const { exec, escape } = require('../db/mysql.js')      //引入配置好的sql

const getList = async (type) => {      

    let sql = `
        select * from item where type = ${type}     
    `
    //只查看type对应的表
    try {
        return await exec(sql)                      //将sql语句发送到mysql.js文件进行判断
    } catch(e) { 
        return { code: -1, err: e }                 //失败返回code： -1 ，状态
    }
}

const getAddData = async (title, content, type) => {
    let sql = `
        insert into item (title, content, type, time) values (${escape(title)}, ${escape(content)}, ${escape(type)}, ${Date.now() / 1000});
    `
    //insert into item (要将数据添加进入的表) values (要添加的数据)
    try {
        let res = await exec(sql)       //判断语句
        if (res.insertId > 0) return { code: 0 }        //插入id大于0 ，code:0 
        return { code: -1 }
    } catch(e) { 
        return { code: -1, err: e }
    }
}

const getEditData = async (id, title, content, type) => {
    let sql = `
        update item set title = ${escape(title)}, content = ${escape(content)}, type = ${escape(type)}, time = ${Date.now() / 1000} where id = ${escape(id)};
    `
    try {
        let res = await exec(sql)        
        if (res.affectedRows > 0) return { code: 0 }        //数据库变动大于0 ，code:0
        return { code: -1 }
    } catch(e) { 
        return { code: -1, err: e }
    }
}

const getInfoData = async (id) => {
    let sql = `
        select * from item where id = ${escape(id)};
    `
    let res = await exec(sql)
    if (res.length == 0) return { code: -1 }    //查询到id所属信息 判断数组长度是否为空
    return { code: 0, list: res }
}

const getDelData = async (id) => {
    let sql = `
        delete from item where id = ${escape(id)};
    `
    let res = await exec(sql)
    if (res.affectedRows > 0) return { code: 0 }    //数据库变动大于0 ，code：0
    return { code: -1 }
}

module.exports = {          //导出
    getList,
    getAddData,
    getEditData,
    getInfoData,
    getDelData
}

 

4.使用方法及判断

引入body-parser

const express= require('express')
// 创建Router对象
const router= express.Router()
const { getList, getAddData, getEditData, getInfoData, getDelData } = require('../controller/article.js')//引入配置好的sql语句
// 使用Router的get方法
//get中参数（‘访问链接’，‘传输内容’）
router.post('/list', async (req, res, next) => {  //Pormise,
    let { type } = req.body     //req.body前端传来的数据
    if (typeof type !== 'number') return res.json({code: -1, message: '数据格式错误'})      //判断
    let result = await getList(type)    //使用封装好的sql方法
    if (result.code == -1) return res.json({code: -1, message: result.err})     //判断
    return res.json({code: 0, message: '数据请求成功', list: result})        //返回全部数据
})

router.post('/add', async (req, res, next) => {
    let { id, title, content, type } = req.body
    if (!title || !content || !type) return res.json({code: -1, message: '缺少参数'})
    if (!id) {
        // 新建
        let result = await getAddData(title, content, type) 
        if (result.code !== 0) return res.json({code: -1, message: '添加失败'})
        return res.json({code: 0, message: '添加成功'})
    } else {
        // 编辑
        let result = await getEditData(id, title, content, type)
        if (result.code !== 0) return res.json({code: -1, message: '编辑失败'})
        return res.json({code: 0, message: '编辑成功'})
    }
})

router.get('/info', async (req, res, next) => {
    let { id } = req.query          //get接收方式和post不同，get是req.query
    let result = await getInfoData(id)
    if (result.code !== 0) return res.json({code: 0, message: '当前为新增状态'})
    return res.json({code: 0, info: result.list[0]})    
})

router.post('/del', async (req, res, next) => {
    let { id } = req.body
    if (!id) return res.json({code: -1, message: '缺少参数'})
    let result = await getDelData(id)
    if (result.code !== 0) return res.json({code: -1, message: '删除失败'})
    return res.json({code: 0, message: '删除成功'})
})

module.exports = router 

直接请求接口