node.js中mysql批量插入更新的三种方法

【背景】在项目中遇到一个批量插入数据的需求，由于之前写过的sql语句都是插入一个对象一条数据，于是去网上搜关键词 "sql批量插入"、"mysql批量插入"等，搜到的答案不外乎这三种：

1.逐条执行，在for循环里写insert插入语句

  这种方法显然性能还差，不符合常理，遂淘汰。

2.批量执行

INSERT INTO table ( "clo1", "col2", "col3", "col4", "col5" )
VALUES
( 1, 10, NULL, '2019-12-19 13:38:35', '新年活动16张卡券'),
( 2, 11, NULL, '2019-12-19 15:05:13', '圣诞活动11张卡券'),
( 3, 12, NULL, '2019-12-19 15:05:13', '圣诞活动12张卡券'),
( 4, 13, NULL, '2019-12-19 15:05:13', '圣诞活动13张卡券');

这样写的话，虽然可以实现需求，但是如果有批量插入几十甚至上百条数据，这样在代码里要拼接很多条动态数据，代码不美观不规范，也不利于后期维护等，所以这种方法也没有采用。

3.使用存储过程

具体业务逻辑大家自己组合一下就可以。

delimiter $$$
create procedure zqtest()
begin
declare i int default 0;
set i=0;
start transaction;
while i<80000 do
	//your insert sql 
set i=i+1;
end while;
commit;
end
$$$
delimiter;
call zqtest();

由于本人之前是做前端的，对存储过程了解的不怎么深入，也放弃了这种方法。

后面去node官方文档和mysql官方文档也没有找到批量插入数据的方法，于是换了一个关键词，搜“node mysql批量插入”终于找到一篇符合需求的文章了，这里说一下有时候学会搜索关键词也是一种技能吧。

---

在文章开始之前，我们先说下node.js中的mysql批量插入的方法，我们可以使用如下方法批量插入：

var mysql = require('mysql')

var values = [
	[1, 'hu', 2],
	[2, 'ke', 0],
	[3, 'yi', 1]
] // 一个二维数组的数据结构

var connection = mysql.createConnection({  
    host: 'localhost', // 连接的服务器
    port: 3306, // mysql服务运行的端口
    database: 'gis', // 选择的库
    user: 'root', // 用户名
    password: 'root' // 用户密码  
})

connection.connect() // 创建一个mysql的线程

var insertsql = 'insert into t_gis_road_node(node_id, node_name, node_type) values ?'

connection.query(insertsql, [values],(err, results, fields) => { 
    if (err) { 
        console.log('INSERT ERROR - ', err.message);
        throw err
    }
    connection.destroy();
    console.log("INSERT SUCCESS");
})

于是我就想是否可以使用类似的方式来进行批量更新呢，如果可以的话无疑是十分方便的，我写了以下代码进行测试：

var values = [
    ['hu', 1],
    ['li', 2],
    ['kes', 3]
]

var sql = 'update test_name set name = ? where id = ?';

connection.query(sql, [values], (err, results, fields) => {
    if (err) { 
        console.log('UPDATE ERROR - ', err.message);
        throw err
    }
    console.log(results)
})

connection.end()

然而，这样写是不行的，报错了

 经过测试，如下更新方式是可以的，但是这种写法无法执行多条更新语句：

var sql = 'update test_name set name = ? where id = ?';

connection.query(sql, ['kes', 3], (err, results, fields) => {
    if (err) { 
        console.log('UPDATE ERROR - ', err.message);
        throw err
    }
    console.log(results)
})

connection.end()

那么，如何能够同时执行多条更新语句来进行批量更新呢？如下介绍三种方法，如果还有其它方法，欢迎大家留言补充

1，forEach方法
这种方法的思想是通过循环调用connection.query的方式来达到批量更新的目的：

var values = [
    ['hus', 1],
    ['lis', 2],
    ['kess', 3]
]

var sql = 'update test_name set name = ? where id = ?';

values.forEach((item, index)=>{
    connection.query(sql, item, (err, results, fields) => {
        if (err) { 
            console.log('UPDATE ERROR - ', err.message);
            throw err
        }
        console.log(results)
    })
})

connection.end(function(err){
    console.log('all is ok')
});

使用forEach本质上不能算批量更新，它只是通过多次调用来达到目的，这种方法有个致命的缺陷，就是很难监听到所有更新都执行完的回调，这时就需要我们用到connection.end的回调函数。如上，在执行完所有的更新之后，控制台会输出 all is ok

2，使用insert into … on duplicate key update

mysql有个神奇的语法：insert into ... on duplicate key update，该语法在insert的时候，如果insert的数据会引起唯一索引（包括主键索引）的冲突，即这个唯一值重复了，则不会执行insert操作，而执行后面的update操作。我们通过这个特性就可以利用批量insert的方法来进行批量更新，代码如下：
 

var values = [
    ['zhao', 1],
    ['qian', 2],
    ['sun', 3]
]

var sql = 'insert into test_name(name, id) values ? on duplicate key update name = values(name)';
connection.query(sql, [values], (err, results, fields) => {
    if (err) { 
        console.log('UPDATE ERROR - ', err.message);
        throw err
    }
    console.log(results)
})

connection.end()

这样我们能轻松的在connection.query的回调函数中写执行完所有更新后的操作了，这种方式本质上是当有重复数据的时候先删除了原先记录并保留未更新字段，再进行插入，所以以上代码虽然只更新了3条语句，而affectedrows却有6行：

3，拼接update语句
mysql可以同时执行多条sql语句，如果我们将要执行的所有update语句拼成一条执行，是不是就可以实现批量更新了呢，代码如下：

var mysql = require('mysql')

var myCon = require('./config/config.js')

var connection = mysql.createConnection({  
    host: myCon.mysql.host, // 连接的服务器
    user: myCon.mysql.user, // 用户名
    password: myCon.mysql.password, // 用户密码
    database: myCon.mysql.database, // 选择的库
    multipleStatements: true
})

connection.connect() // 创建一个mysql的线程

var values = [
    ['tong', 1],
    ['hua', 2],
    ['shun', 3]
]

var model_sql = 'update test_name set name = ? where id = ?';

var sqls = ''

// 拼接sql语句
values.forEach((item, index)=>{
    sqls += mysql.format(model_sql, item) + ';'
})
/* sqls打印结果:
update test_name set name = 'tong' where id = 1;update test_name set name = 'hua' where id = 2;update test_name set name = 'shun'
where id = 3; 
*/
console.log(sqls) 

connection.query(sqls, (err, results, fields) => {
    if (err) { 
        console.log('UPDATE ERROR - ', err.message);
        throw err
    }
    console.log(results)
})

connection.end()

 注意node.js中，mysql默认是不允许一次性同时执行多条sql语句的，如果要同时执行多条sql语句，需要在创建mysql连接的时候，设置multipleStatements为true：