Node.js mysql查询语法问题UPDATE WHERE [英] Node.js mysql query syntax issues UPDATE WHERE

问题描述

我试图在MYSQL DB中更新一些信息，但是我不确定如何在node.js中使用它.这是我正在使用的mysql驱动程序 https://github.com/felixge/node-mysql

I trying to update some info in MYSQL DB, but I'm not sure of how to do it with in node.js. This is the mysql driver I'm using https://github.com/felixge/node-mysql

到目前为止我有什么

connection.query('SELECT * FROM users WHERE UserID = ?', [userId], function(err, results) {
if (results[0]) {
if (results[0].Name!=name) {
console.log(results[0].Name);
connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});
}
console.log(results[0].UserID);
}
});

除...外，一切正常.

Everything works except...

connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});

在PHP中，我会拥有这个...

In PHP I would have this...

mysql_query("UPDATE users SET Name='".$_GET["name"]."' WHERE UserID='".$row['UserID']."'");

我不确定自己在做什么错，但是我很肯定问题出在这里

I'm not sure what I'm doing wrong, But I'm positive that the issue is here

connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});

推荐答案

[说明(2016年4月29日添加):该答案已被接受，但事实证明存在 一种使用SET ?的合理方法.有关详细信息，请参见同一页面上的Bala Clark的答案. — ruakh ]

[Note (added 2016-04-29): This answer was accepted, but it turns out that there is a reasonable way to use SET ?. For details, see Bala Clark's answer on this same page. —ruakh]

来自 Connection.prototype.query()和Connection.createQuery()的代码，很明显，您只能传递单个值对象.我看不到特殊SET ?行为的代码在哪里定义—.它显然不在 SqlString.formatQuery() —但如果使用 SqlString.objectToValues() ，那么我想没有办法将它与另一个?一起使用.

From the code for Connection.prototype.query() and Connection.createQuery(), it's clear that you can only pass in a single values object. I don't see where the code for the special SET ? behavior is defined — it's clearly not in SqlString.formatQuery() — but if it uses SqlString.objectToValues(), then I guess there's no way to use it together with another ?.

我认为最好的方法是放弃整洁的SET ?功能，并编写以下任一功能:

I think the best approach is to just dispense with the neat SET ? feature, and write either of these:

connection.query('UPDATE users SET Name = ? WHERE UserID = ?', [name, userId])

connection.query('UPDATE users SET Name = :Name WHERE UserID = :UserID',
                     {UserID: userId, Name: name})

但是如果您真的想使用SET ?，我想您可以这样写:

but if you really want to use SET ?, I suppose you could write this:

connection.query('UPDATE users SET ? WHERE UserID = :UserID',
                     {UserID: userId, Name: name})

这将同时更新UserID和Name；除非您有触发器，否则应该应该是正常的，因为它将UserID更新为它已经具有的值.但这有点令人不安，我不建议这样做.

which would update both UserID and Name; unless you have a trigger, this should be O.K., in that it's updating UserID to the value it already had anyway. But it's kind of disconcerting, and I don't recommend it.

这篇关于Node.js mysql查询语法问题UPDATE WHERE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！