mysql - nodejs mysql bulk INSERT on DUPLICATE KEY UPDATE

Question

我试图用一个 mysql 语句插入大约 1000 行，如果键已经存在则更新该行。

我在 nodejs 中使用 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 是一个嵌套数组。根据 libaray 的文档:

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

因此我认为这应该可行，但目前我收到此错误

 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

调试sql看起来像这样:

'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)'

这是不正确的。

谁能帮我完成这项工作？

Answer 1

我会尝试对象数组

[
 {name:'Hy Dag3', points:'55040464', rank:'master', hotStreak:114,...},
 {name:'Hkj', points:'554064', rank:'novice', hotStreak:14,...}
]

然后

this.conn.query("INSERT summoners SET ? " +
" ON DUPLICATE KEY UPDATE name = VALUES(name), rank = VALUES(rank)...

因为根据文档:

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'