简介

记录一下用Express操作MySQL，我用的是Express的脚手架。可以用以下命令生成,因为纯写api，所以没有模板。

cnpm i express-generator -g
express myproject

开始操作

先创建一个路由文件，存放于/routes/users.js

//users.js
var express = require('express');
var router = express.Router();
router.get('/',(req,res,next)=>{
    res.send('hello world')
})
//app.js
var usersRouter = require('./routes/users');
app.use('/users', usersRouter);

建立数据库连接

新建一个db目录，在db目录中新建DBConfig.js

//DBConfig.js
module.exports = {
    mysql: {
        host: '127.0.0.1',
        user: '',
        password: '',
        database: '', 
        port: 3306
    }
};

安装mysql依赖，npm install mysql,再回到users.js

var mysql = require('mysql')
var dbConfig = require('../db/DBConfig')
var pool = mysql.createPool(dbConfig.mysql)
//一个测试的接口如下
router.get('/', function (req, res, next) {
    let getAll = "SELECT * FROM egg where e_id=" + req.body.e_id
    pool.getConnection((err, connection) => {
        connection.query(getAll, (err, result) => {
            if (result) {
                res.json(result)
            }
        })
    })
    // res.send('hello world');
});

CURD语句

//查
let sql= "SELECT * FROM egg where e_id=" + req.body.e_id
//增
let sql = 'insert into egg(e_id,e_name) values(?,?) '
pool.getConnection((err, con) => {
    con.query(sql, [11, req.body.f_account], (err, result) => {

    })
})
//改 -- 查询的时候，中文需转码一下
let sql = "update egg set e_id='" + req.body.id + "'where e_id=" + req.body.e_id
pool.getConnection((err, con) => {
    con.query(sql, [11], (err, result) => {

    })
})
//删
let sql = "delete from egg where e_id=" + req.body.e_id