Nodejs mysql bulk INSERT to DUPLICATE KEY UPDATE
I am trying to insert about 1000 lines with one mysql statement and update the row if the key already exists.
I am doing it in nodejs using this module.
Currently my code looks like this:
this.conn.query("INSERT INTO summoners VALUES ?" +
" ON DUPLICATE KEY UPDATE name = VALUES(name), rank = VALUES(rank), points = VALUES(points), satisfyCriteria = VALUES(satisfyCriteria), priority = VALUES(priority)," +
" recentlyChecked = VALUES(recentlyChecked), hotStreak = VALUES(hotStreak), veteran = VALUES(veteran), freshBlood = VALUES(freshBlood), " +
" wins = VALUES(wins), losses = VALUES(losses)", sql_data, (err) => {
if( err ){
logger.error("Error during summoner insert ", err)
}
else {
cb();
}
})
sql_data
is a nested array. According to libray's documentation:
Nested arrays turn into grouped lists (for bulk inserts) eg. [['a', 'b'], ['c', 'd']] becomes ('a', 'b'), ('c', 'd')
So I thought this should work, but I am currently getting this error
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Hy Dag3', '55040464', 'master', 114, true, false, false, false, true, false, 34' at line 1
Debugging sql looks like this:
'INSERT INTO summoners VALUES \'Hy Dag3\', \'55040464\', \'master\', 114, true, false, false, false, true, false, 343, 279 ON DUPLICATE KEY UPDATE name = VALUES(name), rank = VALUES(rank), points = VALUES(points), satisfyCriteria = VALUES(satisfyCriteria), priority = VALUES(priority), recentlyChecked = VALUES(recentlyChecked), hotStreak = VALUES(hotStreak), veteran = VALUES(veteran), freshBlood = VALUES(freshBlood), wins = VALUES(wins), losses = VALUES(losses)'
which is wrong.
Can anyone help me make this work?
+3
source to share
2 answers
I would try an array of objects
[
{name:'Hy Dag3', points:'55040464', rank:'master', hotStreak:114,...},
{name:'Hkj', points:'554064', rank:'novice', hotStreak:14,...}
]
and then
this.conn.query("INSERT summoners SET ? " +
" ON DUPLICATE KEY UPDATE name = VALUES(name), rank = VALUES(rank)...
Because according to doc:
var post = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function (error, results, fields) {
if (error) throw error;
// Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'
+2
source to share