使用 node-mysql、NodeJS 和 Q 更新多行 [英] Updating multiple rows with node-mysql, NodeJS and Q
问题描述
我正在使用 node-mysql、node-js 和 Q 承诺.
I am using node-mysql, node-js, and Q promises.
我已使用上述方法成功更新、删除和插入单行.以及在我的测试用例场景中在单个语句中插入多行.
I have successfully updated, deleted, and inserted single rows using the above. As well as inserted multiple rows in a single statement in my test case scenario.
但是,我需要在单个查询或 for 循环中更新具有不同值(批处理模式)的多行.
However, I need to update multiple rows with different values (batch mode) either in a single query or in a for loop.
关于如何在 mysql2 中使用准备好的语句(应该在 node-mysql 上改进)的信息非常稀少并且没有示例,尽管这应该是自然的选择,并承诺补偿 node-js 异步性质.
The information on how to use prepared statements in mysql2 (supposed to improve on node-mysql) is very sparse and there are no examples, although that should be the natural choice, together with promises to compensate for node-js asynchronous nature.
此外,我已经在各种测试场景中成功使用了 defered.makeNodeResolver().
In addition, I have successfully used defered.makeNodeResolver() in various test scenarios.
我正在尝试使用 where 子句和更改条件更新单个查询中的多行.
I am trying to update multiple rows in a single query with a where clause and changing conditions.
当我更新单行时它正在工作.但是,当我尝试使用单个查询更新多行时,记录不会更新.
It is working when I update a single row. However, when I try to update multiple rows with a single query, the records aren't updated.
我准备切换到使用 for 循环来执行多次更新,然后聚合结果并将其从服务器发送回客户端,这将是我的第二个首选.如果没有太多的性能影响,我不明白为什么不应该这样做.但我还没有找到任何这样做的例子.
I am ready to switch to using a for loop to perform multiple updates and then aggregate the result and send it back from the server to the client, which would have been my second preferred choice. And I don't see why it shouldn't be done that way if there isn't too much of a performance hit. But I haven't found any examples for doing it that way.
var values = [
{ users: "tom", id: 101 },
{ users: "george", id: 102 }
];
// var params = [values.users, values.id ];
var sql = 'UPDATE tabletest SET users = ? WHERE id = ?;';
connection.query(sql, [{users: values[0].users}, {id: values[0].id }], defered.makeNodeResolver());
上面显示的代码实际上并未更新多行.我想我的语法有错误.
The code shown above isn't actually updating multiple rows. I imagine there's an error in my syntax.
但无论如何,在这种特殊情况下,最好的方法是什么?准备好的语句、for 循环中的重复查询还是存储过程?
But anyway, what is the best approach to go about this in this particular scenario? Prepared statements, repeated queries in a for loop, or stored procedures?
推荐答案
你可以这样做:
var values = [
{ users: "tom", id: 101 },
{ users: "george", id: 102 }
];
var queries = '';
values.forEach(function (item) {
queries += mysql.format("UPDATE tabletest SET users = ? WHERE id = ?; ", item);
});
connection.query(queries, defered.makeNodeResolver());
要使用多语句功能,您必须为您的连接启用它:
To use multiple statements feature you have to enable it for your connection:
var connection = mysql.createConnection({
...
multipleStatements: true,
});
这篇关于使用 node-mysql、NodeJS 和 Q 更新多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!