问题描述
我最近正在使用nodejs-mysql模块在node.js中进行查询，在我的工作情况下，我只能使用像以下这样的参数绑定语法:
I'm using nodejs-mysql module to do query in node.js recently, and in my working case I could only use the parameter-binding syntax like:
SELECT * FROM table WHERE name = ?
现在，我想使用这些?或??参数构建动态sql.假设我有2个条件(name和age)，其中两个条件都可能为null(如果用户未提供)，所以我想在3种情况下构建MySQL:
Now I want to build dynamic sql with these ? OR ?? parameters. Assume that I have 2 conditions(name and age) which either of them could be null (if user doesn't provide it),So I want to build MySQL in 3 cases:
- 仅
name=Bob:SELECT * FROM table WHERE name = 'Bob' - 仅
age=40:SELECT * FROM table WHERE age > 40 - 两者:
SELECT * FROM table WHERE name = 'Bob' AND age > 40
- only
name=Bob:SELECT * FROM table WHERE name = 'Bob' - only
age=40:SELECT * FROM table WHERE age > 40 - both:
SELECT * FROM table WHERE name = 'Bob' AND age > 40
我知道如果您自己构建查询很容易，但是当使用只能绑定字段或值的占位符时如何实现?
I know it's easy if you build the query on your own, but how can I achieve it when using placeholders which can only bind field or values ?
在nodejs-mysql文档中，占位符?仅代表值，而??代表字段:
In document of nodejs-mysql, placeholder ? only stands for values and ?? stands for fields:
- https://github.com/felixge/node-mysql/#escaping-query-values
- https://github.com/felixge/node-mysql/#escaping-query-identifiers
我对解决方案的第一个想法是使用这些占位符插入查询段，但是由于?和??都将转义我的查询段，并且查询执行不正确，因此失败了.
My first thinking of solution is to insert query piece by using these placeholders, but it comes to failure because both ? and ?? will escape my query piece, and my query will be executed incorrectly.
到目前为止，我的代码如下，我确信这是不正确的，因为查询段已被转义:
My code so far is as below, which I'm defenitly sure it's not correct because query piece has been escaped:
// achieve paramters from url request
var condition = {};
if(params.name)condition["name"] = ["LIKE", "%" + params.name + "%"];
if(params.age)condition["age"] = parseInt(params.age, 10);
//build query
var sqlPiece = buildQuery(condition);
//try to replace ? with query
var sql = 'SELECT * FROM table WHERE ?';
connection.query(sql, sqlPiece, function(err, results) {
// do things
});
// my own query build function to proceed conditions
function buildQuery(condition) {
var conditionArray = [];
for(var field in condition){
var con = condition[field];
if(con !== undefined){
field = arguments[1] ? arguments[1] + "." + field : field;
var subCondition;
if(con instanceof Array) {
subCondition = field + " " + con[0] + " " + wrapString(con[1]);
}else{
subCondition = field + " = " + wrapString(con);
}
conditionArray.push(subCondition);
}
}
return conditionArray.length > 0 ? conditionArray.join(" AND ") : "1";
}
//wrap string value
function wrapString(value){
return typeof value === "string" ? "'" + value + "'" : value;
}
那么我有什么办法可以解决此问题?
So is there any way I can fix this problem?
感谢约旦的提议，它可以正常工作，但是:
Thanks to Jordan's Offer, it's working, but :
我知道通过字符串concat构建查询非常好，但是就我而言，我不能使用它，因为我正在使用某些中间件或处理mysql和控制器，所以我可以做是要定义接口，它是带有占位符的sql字符串.因此，接口字符串是预先定义的，在控制器功能执行期间我无法对其进行修改.
I know building query by string concat is very good, but in my case I can't use that, because I'm using some middleware or handle mysql and controller, so what I can do is to define interface, which is a sql string with placeholders. So, the interface string is predefined before, and I can't modify it during my controller function.
推荐答案
您的开端确实不错，但是您可能对此有点想过.技巧是使用占位符(?)作为字符串来构建查询，并同时构建一个值数组.
You're off to a really good start, but you may have been overthinking it a bit. The trick is to build a query with placeholders (?) as a string and simultaneously build an array of values.
因此，如果您有params = { name: 'foo', age: 40 }，则要构建以下对象:
So, if you have params = { name: 'foo', age: 40 }, you want to build the following objects:
where = 'name LIKE ? AND age = ?';
values = [ '%foo%', 40 ];
如果只有{ name: 'foo' }，则将其构建为:
If you only have { name: 'foo' }, you'll build these instead:
where = 'name LIKE ?';
values = [ '%foo%' ];
无论哪种方式，您都可以直接在query方法中使用这些对象，即:
Either way, you can use those objects directly in the query method, i.e.:
var sql = 'SELECT * FROM table WHERE ' + where;
connection.query(sql, values, function...);
那么，我们如何构建那些对象?实际上，该代码与您的buildQuery函数非常相似，但是不太复杂.
How do we build those objects, then? In fact, the code is really similar to your buildQuery function, but less complex.
function buildConditions(params) {
var conditions = [];
var values = [];
var conditionsStr;
if (typeof params.name !== 'undefined') {
conditions.push("name LIKE ?");
values.push("%" + params.name + "%");
}
if (typeof params.age !== 'undefined') {
conditions.push("age = ?");
values.push(parseInt(params.age));
}
return {
where: conditions.length ?
conditions.join(' AND ') : '1',
values: values
};
}
var conditions = buildConditions(params);
var sql = 'SELECT * FROM table WHERE ' + conditions.where;
connection.query(sql, conditions.values, function(err, results) {
// do things
});
这篇关于如何通过绑定node.js-sql中的参数来构建动态查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！