Node服务连接Mysql数据库

新建一个express项目；

express myNodeDome

进入文件夹

cd myNodeDome

下载依赖

npm  i

首先下载mysql包

npm i mysql

新建Db.js

//引入mysql包
const mysql = require('mysql')
//链接配置
var connection = mysql.createConnection({
    host: 'localhost', //ip
    user: 'root', //用户名
    password: '123456',//密码
    database: 'test' //数据库名
});

connection.connect();//建立链接
//进行查询
connection.query('SELECT * from user where id =2', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results);
});

查寻语句

SELECT * from user where id =2

在app.js中引用测试，因为项目运行时会自动走一遍引入的js，所以直接会运行；

var Db = require('./public/javascripts/Db')

数据库信息

查询结果：

//改
connection.query('update user set username="666", password="66666" where id=1', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results);
});
//查
connection.query('SELECT * from user where id =2', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results);
});
//增
connection.query('insert into user values (null,8868,8886)', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results);
});
//删
connection.query('delete from  user where id=1', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results);
});

封装sql语句

第一个参数是sql，第二个参数是一个回调函数

/**
 * 
 * @param {String} str sql语句
 */
const DBFun = (str,fun) => {
    connection.query(str, function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ', results);
        fun(results)
    });
}
//promise形式封装
const DBFunPro = (str) => {
    console.log("=========开始查找sql============");
    try {
        return new Promise((resolve, reject) => {
            connection.query(str, function (error, results, fields) {
                if (error) {
                    reject([{ data: "数据库查询报错" }])
                };
                resolve(results)
            });
        })
    } catch{
        console.log("请注意sql报错")
    }
}
module.exports={
    DBFun,DBFunPro 
}

使用：

var express = require('express');
const { v4: uuidv4 } = require('uuid');
const md5 = require('md5');
var router = express.Router();
var Db = require('../public/javascripts/Db')
var endMassage = require('../public/javascripts/endMassage')

//queryId  通过id查询 user 用户信息
router.get('/queryId', function (req, res, next) {
  let { id } = req.query
  Db.DBFun(`select * from user where id=${id}`, (data) => {
    res.send(endMassage(data))
  })
});

//登录注册接口  有账号 直接登录  没账号 注册 
router.get('/login', function (req, res, next) {
  let { username, password } = req.query
  Db.DBFun(`select username,password,uuid from user where username='${username}'`, (data) => {
    if (data.length) {
      if (data[0].password == md5(password)) res.send(endMassage({ data: "登录成功",code:1 }))
      else res.send(endMassage({ data: "密码错误", code: 0, uuidStr: data[0].uuid}))
      
    } else {
      let uuidStr = uuidv4()
      let passwordStr = md5(password)
      Db.DBFun(`insert into user(password,username,uuid) values ('${passwordStr}','${username}','${uuidStr}') `, (insertInfo) => {
        res.send(endMassage({ data: "注册成功", uuidStr }))
      })
    }
  })
});
router.post('/postInputItem', (req, res) => {
  let { name, sex, tel, uuid } = req.body
  Db.DBFun(`select uuid from user_info where uuid='${uuid}'`, (data) => {
    if (data.length > 0) {
      Db.DBFun(`update  user_info set  name='${name}',sex='${sex}',tel='${tel}' where uuid='${uuid}'`, (data) => {
        res.send(endMassage({ data: "信息更新成功" }))
      })
    } else {
      Db.DBFun(`insert into user_info(uuid,name,sex,tel) values ('${uuid}','${name}','${sex}','${tel}')`, (data) => {
        res.send(endMassage({ data: "信息插入成功" }))
      })
    }
  })
})
module.exports = router;