node.js:同时执行多条SQLs,且只有一个回调

nodejs-mysql-queries 解决node中，多条sql执行，最后一条成功后回调问题

下面是检索到的好文章~
https://github.com/zzzhan/nodejs-mysql-queries

安装
$ npm install mysql-queries --save

How to Use

Init mysql-queries to somewhere,such as app.js of Express, like this:

var options = {
    host: 'localhost',
    port: 3306,
    user: 'db_user',
    password: 'password',
    database: 'db_name'
};

require('mysql-queries').init(options);

Use it to some other module, like this:

Execute SQLs directly

var sqlclient = require('mysql-queries'),
  sqls = ['SELECT * FROM prod_unit WHERE NAME=? limit 1',
  'INSERT INTO prod_unit(name) values(?)',
  'INSERT INTO product(name, type_id, unit_id, price) VALUES(?, ?, ?, ?)'];
  
sqlclient.queries(sqls,
  [[data.unit_name],[data.unit_name],[data.name,data.type_id,data.unit_id,data.price]], 
  function(err, results){
    if(!!err) {
      console.log(err);
    } else {
    //If not error, the "results" is the results of the SQLs as array.
      console.log(results);
    }
  });

Execute SQLs with condiction

qlclient.queries(sqls,
  [[data.unit_name],[data.unit_name],[data.name,data.type_id,data.unit_id,data.price]], {
  skip:function(i, arg, results) {
    var skip = false;
    switch(i) {
      case 1:
      //handle second SQL
      //Execute the second SQL depending on the first SQL result.
      skip = results[0].length!==0;
      break;
    case 2:
      //If the second SQL executed, passing the "insertId" to the third SQL as parameter.
      if(results[0].length===0) {
        arg[2]=results[1].insertId;
      }
      break;
    }
    return skip;
  }
}, function(err, results){
  if(!!err) {
    console.log(err);
  } else {
    //If not error, the "results" is the results of the SQLs as array.
    console.log(results);
  }
});

Execute only one SQL

sqlclient.query('SELECT * FROM prod_unit', function(err, result){
  if(!!err) {
    console.log(err);
  } else {
    console.log(result);
  }
});

Features

Less code when executing multiple SQLs
Support transaction of connection
Support connection pool
Auto release the connection

Running Tests

With your correct configured of MySQL on ./test/mysql.json, running tests is as simple as:

npm test

使用mysql-queries说明

  //实际使用中可以在应用启动时进行初始化(只需执行一次)
    require('mysql-queries').init(conf);
    //执行多条SQLs
    var mq = require('mysql-queries');
    mq.queries(sqls,
      [[params.unit_name],[params.unit_name],[params.name,params.type_id,params.unit_id,params.price]], function(err, results){
      if(err) {
        console.log(err);
      } else {
        //"results"为数组,其为多条SQL的执行结果.
        console.log(results);
      }
    });

进阶用法--根据前面SQL的执行结果,判断是否执行当前SQL

例如上述SQLs,在新增商品时允许自定义单位，如果单位不存在，新建商品单位。否则，直接新增商品。

mq.queries(sqls,
      [[params.unit_name],[params.unit_name],[params.name,params.type_id,params.unit_id,params.price]], {
      skip:function(i, arg, results) {//skip判断是否忽略当前SQL的执行,返回true忽略,false不忽略
        var skip = false;
        //i为SQLs的索引号
        switch(i) {
          case 1:
          //根据第1条SQL的结果，判断是否忽略执行第2条SQL
          skip = results[0].length!==0;
          break;
        case 2:
          //如果第二条被执行，其结果中的"insertId"将作为第3条SQL的参数
          if(results[0].length===0) {
            arg[2]=results[1].insertId;
          }
          break;
        }
        return skip;
      }
    }, function(err, results){
      if(err) {
        console.log(err);
      } else {
        //"results"为数组,其为多条SQL的执行结果.
        console.log(results);
      }
    });

另外

mysql-queries除了上述对代码的简化外, 其还采用了数据源和事务处理, 从而提高了开发效率和实用性，给数据库操作带来了很大的便利。