1 依赖模块:
2
3 mysql:https://github.com/felixge/node-mysql npm install mysql --save
4 async:https://github.com/caolan/async npm install async --save (ps: async模块可换成其它Promise模块如bluebird、q等) 因为Node.js的mysql模块本身对于事务的封装过于简单,而且直接使用会有很严重callback hell,故我们封装了两个方法,一个用来初始化sql & params,一个用来执行事务。
5
6 初始化sql & params:
7 function _getNewSqlParamEntity(sql, params, callback) {
8 if (callback) {
9 return callback(null, {
10 sql: sql,
11 params: params
12 });
13 }
14 return {
15 sql: sql,
16 params: params
17 };
18 }
19
20 如果你要执行多条sql语句,则需要:
21 var sqlParamsEntity = [];
22 var sql1 = "insert table set a=?, b=? where 1=1";
23 var param1 = {a:1, b:2};
24 sqlParamsEntity.push(_getNewSqlParamEntity(sql1, param1));
25
26 var sql2 = "update ...";
27 sqlParamsEntity.push(_getNewSqlParamEntity(sql1, []));
28
29 //...更多要事务执行的sql
30
31
32 然后我在我自己的dbHelper.js里封装了execTrans的函数,用来执行事务:
33 var mysql = require('mysql');
34 var async = require("async");
35
36 module.exports = {
37 execTrans: execTrans,
38 }
39
40 var pool = mysql.createPool({
41 host: "mysql host",
42 user: "mysql login user",
43 password: "mysql login pwd",
44 database: "target db name",
45 connectionLimit: 10,
46 port: "mysql db port",
47 waitForConnections: false
48 });
49
50 function execTrans(sqlparamsEntities, callback) {
51 pool.getConnection(function (err, connection) {
52 if (err) {
53 return callback(err, null);
54 }
55 connection.beginTransaction(function (err) {
56 if (err) {
57 return callback(err, null);
58 }
59 console.log("开始执行transaction,共执行" + sqlparamsEntities.length + "条数据");
60 var funcAry = [];
61 sqlparamsEntities.forEach(function (sql_param) {
62 var temp = function (cb) {
63 var sql = sql_param.sql;
64 var param = sql_param.params;
65 connection.query(sql, param, function (tErr, rows, fields) {
66 if (tErr) {
67 connection.rollback(function () {
68 console.log("事务失败," + sql_param + ",ERROR:" + tErr);
69 throw tErr;
70 });
71 } else {
72 return cb(null, 'ok');
73 }
74 })
75 };
76 funcAry.push(temp);
77 });
78
79 async.series(funcAry, function (err, result) {
80 console.log("transaction error: " + err);
81 if (err) {
82 connection.rollback(function (err) {
83 console.log("transaction error: " + err);
84 connection.release();
85 return callback(err, null);
86 });
87 } else {
88 connection.commit(function (err, info) {
89 console.log("transaction info: " + JSON.stringify(info));
90 if (err) {
91 console.log("执行事务失败," + err);
92 connection.rollback(function (err) {
93 console.log("transaction error: " + err);
94 connection.release();
95 return callback(err, null);
96 });
97 } else {
98 connection.release();
99 return callback(null, info);
100 }
101 })
102 }
103 })
104 });
105 });
106 }
107
108 这样就可以执行事务了:
109 execTrans(sqlParamsEntity, function(err, info){
110 if(err){
111 console.error("事务执行失败");
112 }else{
113 console.log("done.");
114 }
115 })