node+express框架中连接使用mysql(经验总结)

$ npm install -g express-generator$ express -e project

$ npm install$ DEBUG=node-blog:* npm start

npm install mysql --save-dev

//mysql配置文件mysql = {    host: "xx.xxx.xx.xxx", //这是数据库的地址    user: "xxx", //需要用户的名字    password: "xxx", //用户密码 ，如果你没有密码，直接双引号就是    database: "xxx" //数据库名字  } //好了，这样我们就能连接数据库了  module.exports = mysql; //用module.exports暴露出这个接口，mysql连接池配置：//mysql连接池配置文件var mysql = require('mysql');var $dbConfig = require('../config/mysql');//注意改成自己项目中mysql配置文件的路径// 使用连接池，避免开太多的线程，提升性能var pool = mysql.createPool($dbConfig);/** * 对query执行的结果自定义返回JSON结果 */function responseDoReturn(res, result, resultJSON) {  if (typeof result === 'undefined') {    res.json({      code: '201',      msg: 'failed to do'    });  } else {    res.json(result);  }};/** * 封装query之sql带不占位符func */function query(sql, callback) {  pool.getConnection(function(err, connection) {    connection.query(sql, function(err, rows) {      callback(err, rows);      //释放链接      connection.release();    });  });}/** * 封装query之sql带占位符func */function queryArgs(sql, args, callback) {  pool.getConnection(function(err, connection) {    connection.query(sql, args, function(err, rows) {      callback(err, rows);      //释放链接      connection.release();    });  });}//exportsmodule.exports = {  query: query,  queryArgs: queryArgs,  doReturn: responseDoReturn}

let express = require('express');let mysql = require('../common/basicConnection');let qibu_task = {  index: '',  value: '',  list: `SELECT * from qibu_task;`, //列表查询  insert(args) {    qibu_task.index = '';    qibu_task.value = '';    args = filter(['id', 'task', 'name', 'created_at'], args)    for (let key in args) {      qibu_task.index = `${qibu_task.index}${key},`      let re = /^[0-9]+.?[0-9]*/;      if (re.test(args[key])) {        qibu_task.value = `${qibu_task.value}${args[key]},`      } else {        qibu_task.value = `${qibu_task.value}'${args[key]}',`      }    }    qibu_task.index = qibu_task.index.substr(0, qibu_task.index.length - 1);    qibu_task.value = qibu_task.value.substr(0, qibu_task.value.length - 1);    return `INSERT INTO qibu_task (${qibu_task.index}) VALUES(${qibu_task.value})`;  }, //按需增加  select(index, value) {    return `SELECT * from qibu_task where ${index}=${value};` //按需查询  },  delete(index, value) {    return `DELETE from qibu_task where ${index}=${value};` //按需删除  },  update(index, args) { //提交修改    if (index in args) {      qibu_task.value = '';      args = filter(['id', 'task', 'name', 'created_at'], args)      for (let key in args) {        let re = /^[0-9]+.?[0-9]*/;        if (re.test(args[key])) {          qibu_task.value = `${qibu_task.value}${key}=${args[key]},`        } else {          qibu_task.value = `${qibu_task.value}${key}='${args[key]}',`        }      }      qibu_task.value = qibu_task.value.substr(0, qibu_task.value.length - 1)      return `UPDATE qibu_task SET ${qibu_task.value} WHERE ${index}=${args[index]};`    }  },};//参数过滤function filter(arguments, obj) {  let newObj = {}  arguments.forEach(every => {    if (every in obj) {      newObj[every] = obj[every]    }  });  return newObj;};module.exports = qibu_task;