node-postgres:如何执行"WHERE col (<dynamic value list>)“查询？

Question

我正在尝试执行这样的查询：

SELECT * FROM table WHERE id IN (1,2,3,4)

问题是我想要过滤的ids列表不是恒定的，每次执行都需要不同的ids。我还需要转义I，因为它们可能来自不受信任的来源，尽管我实际上会转义查询中的任何内容，而不管来源的可信度如何。

node-postgres似乎只使用绑定参数：client.query('SELECT * FROM table WHERE id = $1', [ id ])；如果我有一个已知数量的值(client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ]))，这将起作用，但不能直接使用数组：client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ])，因为似乎没有对数组参数进行任何特殊处理。

根据数组中的项数动态构建查询模板，并将in数组扩展到查询参数数组中(在我的实际案例中，除了in列表之外，该数组还包含其他参数)似乎是不合理的负担。在查询模板中硬编码in列表似乎也不可行，因为node-postgres不提供任何值转义方法。

这似乎是一个非常常见的用例，所以我的猜测是我实际上忽略了一些东西，并不是说不能将常见的IN (values) SQL操作符与node-postgres一起使用。

如果有人用比我上面列出的更优雅的方式解决了这个问题，或者如果我真的遗漏了关于node-postgres的一些东西，请帮助我。

Answer 1

我们以前在github问题列表中看到过这个问题。正确的方法是根据数组动态生成参数列表。如下所示：

var arr = [1, 2, "hello"];
var params = [];
for(var i = 1; i <= arr.length; i++) {
  params.push('$' + i);
}
var queryText = 'SELECT id FROM my_table WHERE something IN (' + params.join(',') + ')';
client.query(queryText, arr, function(err, cb) {
 ...
});

这样你就可以得到postgres参数化的转义。

Answer 2

根据你对@ebohlman的answer的评论，看起来你可能已经很亲近了。您可以使用WHERE id = ANY($1::int[])。PostgreSQL会将数组转换为$1::int[]中的参数类型。因此，这里有一个对我有效的人为的例子：

var ids = [1,3,4]; 

var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1::int[])',[ids]);

q.on('row', function(row) {
  console.log(row);
})

// outputs: { id: 1 }
//          { id: 3 }
//          { id: 4 }

Answer 3

我找到的最好的解决方案是将ANY函数与Postgres的数组强制结合使用。这使您可以将列与任意数组的值进行匹配，就像您写出了col IN (v1, v2, v3)一样。这是pero's answer中的方法，但在这里我将展示ANY的性能与IN相同。

查询

您的查询应如下所示：

SELECT * FROM table WHERE id = ANY($1::int[])

末尾的那一位表示可以更改$1::int[]以匹配"id“列的类型。例如，如果您的‘d类型是uuid，那么您可以编写$1::uuid[]来将参数强制转换为UUID数组。See here for the list of Postgres datatypes。

这比编写代码来构造查询字符串更简单，并且不会受到SQL注入的影响。

示例

使用node-postgres，一个完整的JavaScript示例如下所示：

var pg = require('pg');

var client = new pg.Client('postgres://username:password@localhost/database');
client.connect(function(err) {
  if (err) {
    throw err;
  }

  var ids = [23, 65, 73, 99, 102];
  client.query(
    'SELECT * FROM table WHERE id = ANY($1::int[])',
    [ids],  // array of query arguments
    function(err, result) {
      console.log(result.rows);
    }
  );
});

性能

了解SQL查询性能的最佳方法之一是查看数据库是如何处理它的。该示例表大约有400行，以及一个名为"id“的text类型的主键。

EXPLAIN SELECT * FROM tests WHERE id = ANY('{"test-a", "test-b"}');
EXPLAIN SELECT * FROM tests WHERE id IN ('test-a', 'test-b');

在这两种情况下，Postgres报告了相同的查询计划：

Bitmap Heap Scan on tests  (cost=8.56..14.03 rows=2 width=79)
  Recheck Cond: (id = ANY ('{test-a,test-b}'::text[]))
  ->  Bitmap Index Scan on tests_pkey  (cost=0.00..8.56 rows=2 width=0)
        Index Cond: (id = ANY ('{test-a,test-b}'::text[]))

根据表的大小、索引和查询的不同，您可能会看到不同的查询计划。但是对于上面的查询，ANY和IN的处理方式是相同的。