MySQL 如何通过在node.js-sql中绑定参数来构建动态查询？

How to build dynamic query by binding parameters in node.js-sql?

提问by Bandon

I'm using nodejs-mysqlmodule to do query in node.js recently, and in my working case I could only use the parameter-binding syntax like:

我nodejs-mysql最近使用模块在 node.js 中进行查询，在我的工作案例中，我只能使用参数绑定语法，例如：

SELECT * FROM table WHERE name = ?

Now I want to build dynamic sql with these ?OR ??parameters. Assume that I have 2 conditions(nameand age) which either of them could be null (if user doesn't provide it), So I want to build MySQL in 3 cases:

现在我想用这些?OR??参数构建动态 sql 。假设我有 2 个条件（name和age），它们中的任何一个都可以为空（如果用户不提供），所以我想在 3 种情况下构建 MySQL：

1. only name=Bob: SELECT * FROM table WHERE name = 'Bob'
2. only age=40: SELECT * FROM table WHERE age > 40
3. both: SELECT * FROM table WHERE name = 'Bob' AND age > 40

1. 仅name=Bob：SELECT * FROM table WHERE name = 'Bob'
2. 仅age=40：SELECT * FROM table WHERE age > 40
3. 两个都： 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 ?

我知道如果您自己构建查询很容易，但是在使用只能绑定字段或值的占位符时如何实现它？

In document of nodejs-mysql, placeholder ?only stands for values and ??stands for fields:

在 的文档中nodejs-mysql，占位符?仅代表值并??代表字段：

• https://github.com/felixge/node-mysql/#escaping-query-values
• https://github.com/felixge/node-mysql/#escaping-query-identifiers

• 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?

那么有什么办法可以解决这个问题吗？

Update

更新

Thanks to Jordan's Offer, it's working, but :

感谢乔丹的提议，它正在起作用，但是：

I know building query by string concat is very good, but in my case I can't use that, because I'm using some middlewareor 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.

我知道通过字符串 concat 构建查询非常好，但在我的情况下我不能使用它，因为我正在使用一些中间件或处理 mysql 和控制器，所以我可以做的是定义interface，它是一个 sql 字符串带占位符。所以，接口字符串是之前预定义的，在我的控制器功能中我无法修改它。

回答by Jordan Running

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.

你有一个非常好的开始，但你可能有点想多了。诀窍是使用占位符 ( ?) 作为字符串构建查询，同时构建值数组。

So, if you have params = { name: 'foo', age: 40 }, you want to build the following objects:

因此，如果您有params = { name: 'foo', age: 40 }，您想要构建以下对象：

where = 'name LIKE ? AND age = ?';
values = [ '%foo%', 40 ];

If you only have { name: 'foo' }, you'll build these instead:

如果你只有{ name: 'foo' }，你将构建这些：

where = 'name LIKE ?';
values = [ '%foo%' ];

Either way, you can use those objects directly in the querymethod, i.e.:

无论哪种方式，您都可以直接在query方法中使用这些对象，即：

var sql = 'SELECT * FROM table WHERE ' + where;
connection.query(sql, values, function...);

How do we build those objects, then? In fact, the code is really similar to your buildQueryfunction, but less complex.

那么我们如何构建这些对象呢？实际上，代码与您的buildQuery函数非常相似，但不那么复杂。

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
});