WHERE col IN查询以空数组为参数 [英] WHERE col IN Query with empty array as parameter
问题描述
来自示例 where-列示例和此答案，在何处子句应使用以下语法对参数进行查询
From example where-col-in example and this answer, WHERE IN clauses should have query with parameters with following syntax
const response = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])
其中数据是数组。
现在，当数据为空数组时，它会产生以下查询
Now, when data is an empty array, it produces the following query
SELECT * FROM users WHERE id IN ()
这是一个语法错误。
请考虑以下语句：
-
这有效
this works
const x = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [[1, 2, 3]]);
这不起作用
this does not work
const y = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [[]]);
A 类似错误为 squel 库报告的有关<$ c $的答案c> knex 和ruby的续集就是这种情况。
A similar error reported for squel library has answers on how knex and ruby's sequel behaves in such scenario.
这是一个错误还是我做错了什么？可能存在对两种情况都适用的替代语法。
Is this a bug or am I doing something wrong? Could there be an alternate syntax which works for both scenarios.
例如，使用
For instance, an alternate query using ANY works for both situations:
await db.any(`SELECT * FROM table WHERE id = ANY($1)`, [[1, 2, 3]]);
await db.any(`SELECT * FROM table WHERE id = ANY($1)`, [[]]);
在 WHERE col所在的最佳方式应该是什么查询还可以将空数组作为参数吗？
What should be the best way to have WHERE col IN queries which could also handle empty arrays as params?
推荐答案
常见问题解答
这是一个错误还是我做错了什么？
Is this a bug or am I doing something wrong?
对于大多数SQL框架而言，这不是错误，而是缺陷。处理此类参数非常困难，因此大多数框架只留下空列表，因为它会在（）中生成无效的SQL XXX。
Not a bug, but a flaw for most SQL frameworks. It is very difficult to handle such parameters, so most frameworks just leave the empty list as it is to generate invalid SQL XXX in ().
是否可以使用一种适用于两种情况的替代语法。
Could there be an alternate syntax which works for both scenarios.
A简单的方法是：
if(data is empty) data = [ -1 ] //fill a non-existing id
db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])
knex 或续集怎么样？
What about knex or sequel?
它们是查询生成器框架，因此它们有机会生成特殊的SQL以处理空列表。 Query Builder框架用于处理 WHERE id in（）或... 的流行方法：
They are Query Builder frameworks, so they have chances to generate special SQL to handle empty lists. Popular methods used by Query Builder frameworks to handle WHERE id in () OR ...:
- WHERE（id！= id）OR ...
- WHERE（1 = 0）OR ...
- WHERE（1 ！= 1）OR ...
- 错误的地方OR ...
- etc
- WHERE (id!=id) OR ...
- WHERE (1=0) OR ...
- WHERE (1!=1) OR ...
- WHERE false OR ...
- etc
我个人不喜欢 id！= id ：）
对于某些框架
您可以查看其手册，以了解是否存在处理空列表的方法，例如：框架可以替换空列表吗？值不存在？
You may check its manual to see if there is some way to handle empty lists, eg: can the framework replace the empty list with a non-existing value?
这篇关于WHERE col IN查询以空数组为参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
