node数据库链接之链接池

创建链接池模块并暴露接口

var mysql = require("mysql");  //$ npm install mysql

var pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'xiaomi',  //数据库名称
  port: 3306  //端口号
});
  //链接池
var query = function (sql, params, callback) {
  pool.getConnection(function (err, conn) {
    if (err) {
      callback(err, null, null);
    } else {

      conn.query(sql, params,function (qerr, vals, fields) {
        //释放连接
        conn.release();
        //事件驱动回调
        callback(qerr, vals, fields);
      });
    }
  });
};

module.exports = query; //暴露接口

引入模块添加（插入）信息

var query=require("./mysql.js");
// =====================================使用连接池代码
var  addSql = 'INSERT INTO search(Id,key1,rst) VALUES(0,?,?)';
var  addSqlParams = ['小小鸟', 'https://c.yts.com'];
query(addSql,addSqlParams,function(err,vals,fields){
  //do something
  console.log('添加成功');
});

查询信息

var query=require("./mysql.js");
// ===============================未用连接池代码
// connection.query('SELECT * from search where Rst > 10', function (error, results, fields) {
//   if (error){
//     console.log(error);
//   }else{
//     console.log('The results is: ', results);
//     // console.log('The fields is: ', fields);
//   }
// })

// 使用连接池代码 
query("SELECT * from search where Rst > ?",[10],function(err,vals,fields){
    //do something
    console.log('The results is: ', vals);
});

改动信息

var query=require("./mysql.js");
var modSql = 'UPDATE search SET Key1 = ?,Rst = ? WHERE Id = ?';
var modSqlParams = ['oppo111', '56',11];
//改
query(modSql,modSqlParams,function (err, result) {
   if(err){
         console.log('[UPDATE ERROR] - ',err.message);
         return;
   }        
  console.log('--------------------------UPDATE---------------');
  console.log('UPDATE affectedRows',result);
  console.log('-------------------------------------------------\n\n');
});

删除信息

var query=require("./mysql.js");
var delSql = 'DELETE FROM search where id > ?';
var delSqlParam = [10];
//删
query(delSql,delSqlParam,function (err, result) {
        if(err){
          console.log('[DELETE ERROR] - ',err.message);
          return;
        }        
 
       console.log('--------------------------DELETE----------------');
       console.log('DELETE affectedRows',result.affectedRows);
       console.log('---------------------------------------------\n\n');  
});