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 = ateConnection({
host: 'localhost', // 连接的服务器
port: 3306, // mysql服务运⾏的端⼝
database: 'gis', // 选择的库
user: 'root', // ⽤户名
password: 'root' // ⽤户密码
})
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 - ', ssage);
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 = ?';
js 二维数组connection.query(sql, [values], (err, results, fields) => {
if (err) {
console.log('UPDATE ERROR - ', ssage);
throw err
}
console.log(results)
})
然⽽，这样写是不⾏的，报错了
经过测试，如下更新⽅式是可以的，但是这种写法⽆法执⾏多条更新语句：
var sql = 'update test_name set name = ? where id = ?';
connection.query(sql, ['kes', 3], (err, results, fields) => {
if (err) {
console.log('UPDATE ERROR - ', ssage);
throw err
}
console.log(results)
})
那么，如何能够同时执⾏多条更新语句来进⾏批量更新呢？如下介绍三种⽅法，如果还有其它⽅法，欢迎⼤家留⾔补充
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 - ', ssage);
throw err
}
console.log(results)
})
})
console.log('all is ok')
});
使⽤forEach本质上不能算批量更新，它只是通过多次调⽤来达到⽬的，这种⽅法有个致命的缺陷，就是很难监听到所有更新都执⾏完的回调，这时就需要我们⽤到d的回调函数。如上，在执⾏完所有的更新之后，控制台会输出 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 - ', ssage);
throw err
}
console.log(results)
})
这样我们能轻松的在connection.query的回调函数中写执⾏完所有更新后的操作了，这种⽅式本质上是当有重复数据的时候先删除了原先记录并保留未更新字段，再进⾏插⼊，所以以上代码虽然只更新了3条语句，⽽affectedrows却有6⾏：
3，拼接update语句
mysql可以同时执⾏多条sql语句，如果我们将要执⾏的所有update语句拼成⼀条执⾏，是不是就可以实现批量更新了呢，代码如下：
var mysql = require('mysql')
var myCon = require('./config/config.js')
var connection = ateConnection({
host: sql.host, // 连接的服务器
user: sql.user, // ⽤户名
password: sql.password, // ⽤户密码
database: sql.database, // 选择的库
multipleStatements: true
})
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 - ', ssage);
throw err
}
console.log(results)
})
注意node.js中，mysql默认是不允许⼀次性同时执⾏多条sql语句的，如果要同时执⾏多条sql语句，需要在创建mysql连接的时候，设置multipleStatements为true：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。