node.js - 如何在 Node.js 中将 {In} 列表添加到 Oracle DB 查询 "WHERE IN"子句

Question

我有这个查询

select * from foo where id in (:ListOfIds) 

我多次调用此方法，但每次调用都有不同的值，例如

    select * from foo where id in (1,2,5) 
    select * from foo where id in (3,4,6) 

那么我如何将列表传递给这个查询？

Answer 1

如果 IN 列表的最大大小已知，并且不是太大，最好对每个潜在列表项使用一个绑定(bind)变量。对于应用程序不知道的任何值，请绑定(bind) null。

例如，SQL 语句可以是:

sql = `select * from foo where id in (:v1, :v2, :v3, :v4)`;

然后，如果您只有三个数据项，您将绑定(bind):

binds = [30, 60, 90, null];
const results = await connection.execute(sql, binds);

在频繁调用此查询的应用程序中，您可以获得有效使用内部 statement cache 的优势。 ，这提高了重复查询的性能。

另一个解决方案是使用绑定(bind)(出于安全考虑)，但构建精确的 SQL 字符串，例如:

binds = ['Christopher', 'Hazel', 'Samuel'];
sql = `select first_name, last_name from employees where first_name in (`;
for (var i=0; i < binds.length; i++) 
    sql += (i > 0) ? ", :" + i : ":" + i; sql += ")";  
// "sql" becomes "select first_name, last_name from employees where first_name in (:0, :1, :2)"

但是，根据此查询的执行频率以及绑定(bind)值数量的变化程度，您最终可能会得到大量“唯一”查询字符串。因此，您可能无法获得执行固定 SQL 语句所带来的语句缓存优势 - 并且您可能会从缓存中推出其他语句，从而导致重复执行它们的效率低下。

关于 JavaScript 本身，上次我检查时，这个简单的 for 循环比 map/join 解决方案更快。

对于大量绑定(bind)值，您可以尝试以下操作:

const sql = `SELECT first_name, last_name
             FROM employees
             WHERE first_name IN (SELECT * FROM TABLE(:bv))`;
const inlist = ['Christopher', 'Hazel', 'Samuel'];  // a very large list
const binds = { bv: { type: "SYS.ODCIVARCHAR2LIST", val: inlist } };
const result = await connection.execute(sql, binds);
console.dir(result, { depth: null });

请注意，它使用需要一些额外往返的对象类型，因此此解决方案更适合早期解决方案不可行的情况。

引用:

• node-oracledb 文档:Binding Multiple Values to a SQL WHERE IN Clause .