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.原始批量更新方法
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重复记录,不会改变其它字段。
2.批量更新性能分析
1 replace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中。
2 1、如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。
3 2、 否则,直接插入新数据。
4
5 要注意的是:插入数据的表必须有主键或者是唯一索引!否则的话,replace into 会直接插入数据,这将导致表中出现重复的数据。