如何在SQL多WHERE子句中批量赋值并单次执行查询

问题场景 #

现有以下参数数组与SQL查询代码：

const params = [
['2022-12-10', 'aaaaa', '2022-12-01', 'xhxha', '2022-12-10'],
['2022-12-11', 'ababa', '2022-12-01', 'xhxha', '2022-12-11'],
['2022-12-12', 'acaca', '2022-12-01', 'xhxha', '2022-12-12'],
['2022-12-13', 'adada', '2022-12-01', 'xhxha', '2022-12-13'],
];

const data = await db.query(`select id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as end_date ABS(DATEDIFF(?, end_date))+1 as delay from chart 
where uid = ?
and date = ?
and project_uid = ?
and end_date = ?
and completed is true;
`, [params]);

当前执行时所有参数会被塞入第一个占位符，需将每个子数组元素分别对应到查询的各个占位符，且避免循环多次执行查询。

解决方案 #

方案一：用UNION ALL拼接批量查询 #

将每个子数组对应的查询逻辑拆分为独立SELECT语句，通过UNION ALL合并，同时将参数数组平铺为一维数组，一次性传入。

// 生成每个子数组对应的SELECT片段，用UNION ALL拼接
const querySegments = params.map(() => `
    select id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as end_date, ABS(DATEDIFF(?, end_date))+1 as delay 
    from chart 
    where uid = ?
    and date = ?
    and project_uid = ?
    and end_date = ?
    and completed is true
`).join(' UNION ALL ');

// 平铺参数数组，确保参数顺序与占位符完全匹配
const flatParams = params.flat();

// 执行批量查询
const data = await db.query(querySegments, flatParams);

方案二：利用数据库驱动的批量查询能力（以mysql2为例） #

部分数据库驱动（如mysql2）支持直接传入二维数组作为参数，自动匹配每个子数组与查询占位符，无需手动拼接SQL。

// 注意使用execute方法而非query，mysql2的execute支持二维参数数组
const [data] = await db.execute(`
    select id, title, DATE_FORMAT(end_date,"%Y-%m-%d") as end_date, ABS(DATEDIFF(?, end_date))+1 as delay 
    from chart 
    where uid = ?
    and date = ?
    and project_uid = ?
    and end_date = ?
    and completed is true;
`, params);

关键注意事项 #

1. 原SQL中DATE_FORMAT(end_date,"%Y-%m-%d") as end_date后缺少逗号，已在示例中修正，否则会触发语法错误。
2. 使用UNION ALL时，需保证每个SELECT语句返回的列数、列类型完全一致，否则会报错。
3. 平铺参数数组时，需确保每个子数组的长度与单条查询的占位符数量严格对应，避免参数匹配错位。

内容的提问来源于stack exchange，提问作者Himashu