node下使用mysql包操作数据库

先引入mysql

cnpm install mysql --save
const mysql = require('mysql')

一个连接

// create a new connection 
let conn = mysql.createConnection({
    host:'localhost',
    user:'wwl',
    password:'123',
    database:'test'
})
// sql query string
let _sql = 'select * from tb_test'
conn.query(_sql, (err,result,field) => {
    if(err) throw(err)
    console.log(result)
    //close connection
    conn.end()
})

新建连接池

//create a connection pool
let pool = mysql.createPool({
    host:'localhost',
    user:'wwl',
    password:'123',
    database:'test'
})
// get a connection from the pool
pool.getConnection( (err,conn) =>{
  if(err) console.log(err)
  //query string
  let _sql = 'select * from tb_test '
  conn.query(_sql,(err,result,field) => {
      if(err) console.log(err)
      console.log(result)
      //delete the connection   you can also use conn.release() to give it back
      conn.destroy()
  })
})
//close all connections
pool.end( (err) => {})

封装成Promise供Koa2使用

function createPromiseMysqlConnection(){
    return new Promise( (res,rej) => {
        // create a new connection 
        let conn = mysql.createConnection({
            host: 'localhost',
            user: 'wwl',
            password: '123',
            database: 'test'
        })
        // sql query string
        let _sql = 'select * from tb_test'
        conn.query(_sql, (err, result, field) => {
            if (err) rej(err)
            res(result)
            //close connection
            conn.end()
        })
      })
    }

再贴一个模块版

const mysql = require('mysql')


function mysqlOperator(){

}

mysqlOperator.prototype = {
    constructor:mysqlOperator,
    init,
    query,
    releaseConnection,
    destroyConnection,
    endPool
}
// init
function init(opt){
    return new Promise( (res,rej) => {
        this.pool = mysql.createPool(opt)
        this.pool.getConnection((err, connection) => {
            if (err) rej(err)
            this.connection = connection
            res()
        })
    })   
}

// query
function query(sql){
    return new Promise( (res,rej) => {
        this.connection.query(sql, (err, result, field) => {
            if (err) rej(err)
            res({ result, field })
        })
    })
}

// release connection
function releaseConnection(){
    this.connection.release()
}

//destroy connection
function destroyConnection(){
    this.connection.destroy()
}

//end pool
function endPool(){
    this.pool.end( (err) => console.log(err))
}

module.exports = mysqlOperator