如何使用"Insert ... ON DUPLICATE KEY UPDATE"添加多行?使用knex [英] How to add multiple rows using "Insert ... ON DUPLICATE KEY UPDATE" using knex

问题描述

所以我最近一直在和knex玩耍，但是我发现自己处在一种我不知道该怎么办的情况下.

so I've been playing around with knex lately, however I found myself on a situation where I don't know what to do anymore.

所以我有这个查询:

knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES (?, ?, ?) 
ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", 
[
    ['val1', 'hello', 'world'],
    ['val2', 'ohayo', 'minasan'],
]);

由于某些原因，它会向我抛出错误Expected 2 bindings, saw 3.

And for some reasons It throws me an error Expected 2 bindings, saw 3.

我试图做到:

knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES (?, ?, ?) 
ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", 
    ['val1', 'hello', 'world'],
    ['val2', 'ohayo', 'minasan'],
);

这次没有错误，但是它只插入第一个数组.

No error this time, but it only inserts the first array.

我还尝试将值设置为对象:

I also tried making the values an object:

[
    {col1: 'val1', col2: 'hello', col3: 'world'},
    {col1: 'val2', col2: 'ohayo', col3: 'minasan'},
]

但是仍然没有运气.

推荐答案

如果您一次只需要插入一个修订号行，则可以尝试以下操作:

If you only need to insert a fix number rows at a time, you could try this:

knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES (?, ?, ?), (?, ?, ?) ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", 
    ['val1', 'hello', 'world', 'val2', 'ohayo', 'minasan'],
);

如果您一次不知道需要插入多少个，则可以编写一个脚本，该脚本根据需要添加(?, ?, ?),次.

If you don't know how many you need to insert at a time, it is possible to write a script that adds (?, ?, ?), as many times as needed.

var questionMarks = "";
var values = [];
var rows = [
    {col1: 'val1', col2: 'hello', col3: 'world'},
    {col1: 'val2', col2: 'ohayo', col3: 'minasan'},
];
rows.forEach(function(value, index){
    questionMarks += "("
    Object.keys(value).forEach(function(x){
         questionMarks += "?, ";
         values.push(value[x]);
    });
    questionMarks = questionMarks.substr(0, questionMarks.length - 2);
    questionMarks += "), ";
});
questionMarks = questionMarks.substr(0, questionMarks.length - 2); //cut off last unneeded comma and space
knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES " + questionMarks + " ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", values);

这篇关于如何使用"Insert ... ON DUPLICATE KEY UPDATE"添加多行?使用knex的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！