1 //封装事务回滚函数 2 var mysql = require('mysql'); 3 var async = require("async"); 4 5 var pool = mysql.createPool({ 6 host: "localhost", 7 user: "root", 8 password: "123456", 9 database: "test", 10 connectionLimit: 10, 11 port: "3306", 12 waitForConnections: false 13 }); 14 15 function execTrans(sqlparamsEntities, callback) { 16 pool.getConnection(function (err, connection) { 17 if (err) { 18 return callback(err, null); 19 } 20 connection.beginTransaction(function (err) { 21 if (err) { 22 return callback(err, null); 23 } 24 //console.log("开始执行transaction,共执行" + sqlparamsEntities.length + "条数据"); 25 var funcAry = []; 26 sqlparamsEntities.forEach(function (sql_param) { 27 var temp = function (cb) { 28 var sql = sql_param.sql; 29 var param = sql_param.params; 30 connection.query(sql, param, function (tErr, rows, fields) { 31 if (tErr) { 32 connection.rollback(function () { 33 console.log("事务失败," + sql_param + ",ERROR:" + tErr); 34 throw tErr; 35 }); 36 } else { 37 return cb(null, 'ok'); 38 } 39 }) 40 }; 41 funcAry.push(temp); 42 }); 43 44 async.series(funcAry, function (err, result) { 45 if (err) { 46 connection.rollback(function (err) { 47 console.log("transaction error: " + err); 48 connection.release(); 49 return callback(err, null); 50 }); 51 } else { 52 connection.commit(function (err, info) { 53 //console.log("transaction info: " + JSON.stringify(info)); 54 if (err) { 55 console.log("执行事务失败," + err); 56 connection.rollback(function (err) { 57 console.log("transaction error: " + err); 58 connection.release(); 59 return callback(err, null); 60 }); 61 } else { 62 connection.release(); 63 return callback(null, info); 64 } 65 }) 66 } 67 }) 68 }); 69 }); 70 } 71 72 module.exports = { 73 execTrans: execTrans, 74 }
1 const express=require('express'); 2 const huigun=require('./dbHelper.js') 3 4 //初始化sql & params: 5 function _getNewSqlParamEntity(sql, params, callback) { 6 if (callback) { 7 return callback(null, { 8 sql: sql, 9 params: params 10 }); 11 } 12 return { 13 sql: sql, 14 params: params 15 }; 16 } 17 18 //如果你要执行多条sql语句,则需要: 19 var sqlParamsEntity = []; 20 //var sql1 = "insert table set a=?, b=? where 1=1"; 21 //var param1 = {a:1, b:2}; 22 //sqlParamsEntity.push(_getNewSqlParamEntity(sql1, param1)); 23 var sql1 = `insert table1 (name,age) VALUES('burt',19)`; 24 sqlParamsEntity.push(_getNewSqlParamEntity(sql1)); 25 sql1 = `insert table1 (name,age) VALUES('burt',23)`; 26 sqlParamsEntity.push(_getNewSqlParamEntity(sql1)) 27 sql1 = `insert table1 (name,age) VALUES('jing',20)`; 28 sqlParamsEntity.push(_getNewSqlParamEntity(sql1)) 29 30 31 var ret; 32 huigun.execTrans(sqlParamsEntity, function(err, info){ 33 if(err){ 34 console.error("事务执行失败"); 35 }else{ 36 console.log("done."); 37 console.log(info); 38 ret = info; 39 } 40 }); 41 42 43 //创建服务器 44 const server=express(); 45 46 //监听端口号8081,移到台北服务器要改成8080端口,mysql的链接也要更改 47 server.listen(8082,(err)=>{ 48 if(err) 49 throw new err; 50 else 51 console.log('成功监听8082端口。'); 52 }); 53 54 server.use('/',(req,res)=>{ 55 console.log(ret); 56 res.send(ret); 57 });
mysql批量更新记录
1 UPDATE categories 2 3 SET display_order = CASE id 4 5 WHEN 1 THEN 3 6 7 WHEN 2 THEN 4 8 9 WHEN 3 THEN 5 10 11 END 12 13 WHERE id IN (1,2,3) 14 这里使用了case when 这个小技巧来实现批量更新。 15 这句sql的意思是,更新display_order 字段,如果id=1 则display_order 的值为3,如果id=2 则 display_order 的值为4,如果id=3 则 display_order 的值为5。 16 这里的where部分不影响代码的执行,但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数,这里只有3条数据进行更新,而where子句确保只有3行数据执行。 17 18 UPDATE categories 19 20 SET display_order = CASE id 21 22 WHEN 1 THEN 3 23 24 WHEN 2 THEN 4 25 26 WHEN 3 THEN 5 27 28 END, 29 30 title = CASE id 31 32 WHEN 1 THEN ‘New Title 1’ 33 34 WHEN 2 THEN ‘New Title 2’ 35 36 WHEN 3 THEN ‘New Title 3’ 37 38 END 39 40 WHERE id IN (1,2,3) 41 到这里,已经完成一条mysql语句更新多条记录了。
1 性能分析 2 3 当我使用上万条记录利用mysql批量更新,发现使用最原始的批量update发现性能很差,将网上看到的总结一下一共有以下三种办法: 4 1.批量update,一条记录update一次,性能很差 5 6 复制代码 代码如下: 7 8 update test_tbl set dr=’2’ where id=1; 9 10 2.replace into 或者insert into …on duplicate key update 11 12 复制代码 代码如下: 13 14 replace into test_tbl (id,dr) values (1,’2’),(2,’3’),…(x,’y’); 15 16 或者使用 17 18 复制代码 代码如下: 19 20 insert into test_tbl (id,dr) values (1,’2’),(2,’3’),…(x,’y’) on duplicate key update dr=values(dr); 21 3.创建临时表,先更新临时表,然后从临时表中update 22 23 代码如下 复制代码 24 25 create temporary table tmp(id int(4) primary key,dr varchar(50)); 26 27 insert into tmp values (0,’gone’), (1,’xx’),…(m,’yy’); 28 29 update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id; 30 31 注意:这种方法需要用户有temporary 表的create 权限。 32 就测试结果来看,测试当时使用replace into性能较好。 33 34 replace into 和insert into on duplicate key update的不同在于: 35 36 replace into 操作本质是对重复的记录先delete 后insert,如果更新的字段不全会将缺失的字段置为缺省值 37 38 insert into 则是只update重复记录,不会改变其它字段。
1 replace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中。
2 1、如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。
3 2、 否则,直接插入新数据。
4
5 要注意的是:插入数据的表必须有主键或者是唯一索引!否则的话,replace into 会直接插入数据,这将导致表中出现重复的数据。