node-mysql操作transactions的实例

关于mysql的事务，可查看上一篇文章未尾>>

先更新一条数据

UPDATE user_info SET userName = 'kk' WHERE userId = 1;

SELECT * FROM user_info

 

现在的数据

 

写一个事务，只选中前面的语句执行：

 

SET AUTOCOMMIT=0;

START TRANSACTION;
SELECT * FROM user_info FOR UPDATE;
COMMIT;

如果使用node.js执行，控制台上会看到待执行的sql处于等待状态：

 
上面报错，是因为超时了。

如果执行commit后，再执行node.js脚本（执行的是一句更新sql--UPDATE user_info SET userName = ‘张一’ WHERE userId = 1）

 

这里主要用到了node-mysql-queues，它需要先安装node-mysql

可能因为版本问题吧，它的页面给出的操作方法已经失效了。正确的应该如下：

/**
 * Created with JetBrains WebStorm.
 */
var mysql = require('mysql');

var connection = mysql.createConnection({
    host : 'localhost',
    port : 3306,
    user : 'root',
    password : '123456',
    database : 'test',
    charset : 'UTF8_GENERAL_CI',
    debug : false
});

connection.connect();

//connection.query('CALL proc_simple(1, @a, @b);', function(err, rows, fields) {
//    if (err) {
//       throw err;
//    }
//
//    var results = rows[0];
//    var row = results[0];
//    console.log("userName：",row.uName, "　　count：", row.totalCount);
//});

var queues = require('mysql-queues');
const DEBUG = true;
queues(connection, DEBUG);

var trans = connection.startTransaction();
trans.query("UPDATE user_info SET userName = ? WHERE userId = ?", ["张一", 1], function(err, info) {
   if (err) {
       throw err;
       trans.rollback();
   } else {
       trans.commit(function(err, info) {
           console.log(info);
       });
   }
});
trans.execute();
console.log('execute');
//connection.end();

它还支持一次执行多条sql，其实就是把sql语句放入一个数组，然后循环执行该数组内的每条SQL。

var q = client.createQueue();
q.query(...); 
q.query(...);
q.execute();

正常使用的话，请参考如下的代码：

var mysql = require('mysql');
var client = mysql.createConnection({//createClient(已失效) -> createConnection
    user: 'root',
    password: 'root'
});
//Enable mysql-queues
var queues = require('mysql-queues');
const DEBUG = true;
queues(client, DEBUG);
//Start running queries as normal...
client.query(...);

//Now you want a separate queue?
var q = client.createQueue();
q.query(...); 
q.query(...);
q.execute();

client.query(...); //Will not execute until all queued queries (and their callbacks) completed.

//Now you want a transaction?
var trans = client.startTransaction();
trans.query("INSERT...", [x, y, z], function(err, info) {
    if(err)
        trans.rollback();
    else
        trans.query("UPDATE...", [a, b, c, info.insertId], function(err) {
            if(err)
                trans.rollback();
            else
                trans.commit();
        });
});
trans.execute();
//No other queries will get executed until the transaction completes
client.query("SELECT ...") //This won't execute until the transaction is COMPLETELY done (including callbacks)

//Or... as of version 0.3.0, you can do this...
var trans = client.startTransaction();
function error(err) {
    if(err && trans.rollback) {trans.rollback(); throw err;}
}
trans.query("DELETE...", [x], error);
for(var i = 0; i < n; i++)
    trans.query("INSERT...", [ y[i] ], error);
trans.commit(); //Implictly calls resume(), which calls execute()
/* In the case written above, COMMIT is placed at the end of the Queue, yet the
entire transaction can be rolled back if an error occurs. Nesting these queries
was not required. */

 

query方法的实现：

Connection.prototype.query = function(sql, values, cb) {
  this._implyConnect();

  var options = {};

  if (typeof sql === 'object') {
    // query(options, cb)
    options = sql;
    cb      = values;
    values  = options.values;

    delete options.values;
  } else if (typeof values === 'function') {
    // query(sql, cb)
    cb          = values;
    options.sql = sql;
    values      = undefined;
  } else {
    // query(sql, values, cb)
    options.sql    = sql;
    options.values = values;
  }

  options.sql = this.format(options.sql, values || []);

  if (!('typeCast' in options)) {
    options.typeCast = this.config.typeCast;
  }

  return this._protocol.query(options, cb);
};

如果第二个参数不是函数，调用format则转换sql，默认将？替换为数组参数的值（一一对应）

Connection.prototype.format = function(sql, values) {
  if (typeof this.config.queryFormat == "function") {
    return this.config.queryFormat.call(this, sql, values, this.config.timezone);
  }
  return SqlString.format(sql, values, this.config.timezone);
};

默认的format方法实现如下：

SqlString.format = function(sql, values, timeZone) {
  values = [].concat(values);

  return sql.replace(/\?/g, function(match) {
    if (!values.length) {
      return match;
    }

    return SqlString.escape(values.shift(), false, timeZone);
  });
};

也可以使用自定义的函数进行处理，在创建连接的时候，传入queryFormat参数即可。