我正在尝试执行这样的查询:
SELECT * FROM table WHERE id IN (1,2,3,4)
问题是我要过滤的 id 列表不是恒定的,每次执行都需要不同。我还需要转义 id,因为它们可能来自不受信任的来源,尽管我实际上会转义查询中的任何内容,而不管来源的可信度如何。
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 ])
,因为似乎没有任何对数组参数的特殊处理。
根据数组中的项目数动态构建查询模板并将 ids 数组扩展到查询参数数组(在我的实际案例中,除了 id 列表之外,它还包含其他参数)似乎不合理地繁琐。在查询模板中对 id 列表进行硬编码似乎也不可行,因为 node-postgres 不提供任何值转义方法。
这似乎是一个非常常见的用例,所以我的猜测是我实际上忽略了一些东西,而不是说不可能将常见的 IN (values)
SQL 运算符与 node-postgres 一起使用。
如果有人以比我上面列出的更优雅的方式解决了这个问题,或者如果我真的缺少一些关于node-postgres的东西,请提供帮助。
根据您对@ebohlman答案的评论,您似乎已经接近了。您可以使用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 }
我们之前在 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 参数化的转义。
我发现的最佳解决方案是将ANY
函数与Postgres的数组强制一起使用。这使您可以将列与任意值数组匹配,就像您写出col IN (v1, v2, v3)
一样。这是pero回答中的方法,但在这里我表明ANY
的性能与IN
相同。
查询
您的查询应如下所示:
SELECT * FROM table WHERE id = ANY($1::int[])
最后说$1::int[]
的那一点可以更改以匹配"id"列的类型。例如,如果 ID 的类型为 uuid
,则需要编写 $1::uuid[]
以强制将参数强制为 UUID 数组。有关 Postgres 数据类型的列表,请参阅此处。
这比编写代码来构造查询字符串更简单,并且对 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
的处理方式相同。
使用 pg-promise,这可以通过 CSV 过滤器(逗号分隔值)很好地工作:
const values = [1, 2, 3, 4];
db.any('SELECT * FROM table WHERE id IN ($1:csv)', [values])
.then(data => {
console.log(data);
})
.catch(error => {
console.log(error);
});
为了解决对各种数据类型的担忧,:csv
修饰符将数组序列化为 csv,同时根据其 JavaScript 类型将所有值转换为正确的 PostgreSQL 格式,甚至支持自定义类型格式。
如果你有这样的混合类型值:const values = [1, 'two', null, true]
,你仍然会得到正确转义的SQL:
SELECT * FROM table WHERE id IN (1, 'two', null, true)
更新
从 v7.5.1 开始,pg-promise 开始支持 :list
作为 :csv
过滤器的可互换别名:
db.any('SELECT * FROM table WHERE id IN ($1:list)', [values])
另一种可能的解决方案是像这样使用 UNNEST
函数:
var ids = [23, 65, 73, 99, 102];
var strs = ['bar', 'tar', 'far']
client.query(
'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
[ids], // array of query arguments
function(err, result) {
console.log(result.rows);
}
);
client.query(
'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
[strs], // array of query arguments
function(err, result) {
console.log(result.rows);
}
);
我已经在存储过程中使用了它,它工作正常。相信它也应该从节点 pg 代码中工作。
您可以在此处阅读有关 UNNEST 函数的信息。
另一个可能的解决方案是例如NODE JS中的REST API:
var name = req.body;//Body is a objetc that has properties for example provinces
var databaseRB = "DATABASENAME"
var conStringRB = "postgres://"+username+":"+password+"@"+host+"/"+databaseRB;
var filter_query = "SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json((parameters) As properties FROM radiobases As lg WHERE lg.parameter= ANY($1) )As f) As fc";
var client = new pg.Client(conStringRB);
client.connect();
var query = client.query(new Query(filter_query,[name.provinces]));
query.on("row", function (row, result) {
result.addRow(row);
});
query.on("end", function (result) {
var data = result.rows[0].row_to_json
res.json({
title: "Express API",
jsonData: data
});
});
请记住,可以使用任何类型的数组
我尝试了这个并在没有转换为 int[ ] 的情况下工作。在主要版本 8 中使用节点发布
var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1)',[ids]);
这个想法一般:
var invals = [1,2,3,4], cols = [...fields];
var setvs = vs => vs.map(v=> '$'+ (values.push(v)) ).join();
var values = [];
var text = 'SELECT '+ setvs(cols) +' FROM table WHERE id IN (' + setvs(invals) +')';