使用ExpressJS中的可选过滤器构建MySQL查询

Question

如果这是一个愚蠢的问题，很抱歉，但我是expression和MySQL的新手，在任何地方都找不到解决方案。

我需要根据HTML表单中的一些用户输入过滤器创建一个MySQL查询。参数如下：

let sDate = req.body.startDate;
let eDate = req.body.endDate;
let param1 = req.body.param1;
let param2 = req.body.param2;
let param3 = req.body.param3;
let param4 = req.body.param4;

如果所有参数都不为空，我将使用的正常查询将是

const query = `
SELECT * FROM table
WHERE
date BETWEEN ? AND ?
AND col1 = ?
AND col2 = ?
AND col3 = ?
AND col4 = ?;`
db.query(query, [startDate, endDate, param1, param2, param3, param4], (e, rows)=>{});

但是每一个参数都可以为null，所以有很多可能的过滤器组合。

有没有一种方法可以用一个查询来查询数据库？我可以在很多其他的陈述中处理它，但感觉不对。

提前感谢您的帮助！

Answer 1

对于col1 = ?模式，可以使用(? IS NULL OR ? = col1)。

对于你的约会匹配，你可以这样做。

WHERE date <= IF( ? IS NOT NULL THEN ? ELSE '2999-12-31')
AND date >= IF( ? IS NOT NULL THEN ? ELSE '1000-01-01')

但请注意，参数会出现两次。所以你的查询是

const sql = `
SELECT *
FROM tbl
WHERE date <= IF(? IS NOT NULL, ?, '2999-12-31')
AND date >= IF(? IS NOT NULL, ?, '1000-01-01')
AND (? IS NULL OR col1 = ?)
AND (? IS NULL OR col2 = ?)
AND (? IS NULL OR col3 = ?)
AND (? IS NULL OR col4 = ?);`

你会这样调用它。

const params = [startDate, startDate, 
endDate, endDate,
param1, param1, 
param2, param3, 
param2, param3, 
param4, param4]
db.query(query, params, (e, rows)=>{});

但是小心：对于数据库服务器中的查询优化软件来说，这种查询是一种臭名昭著的边缘情况。它不能很好地告诉你想要什么，从而使用表统计信息来选择正确的索引。不过，一定要在上加一个索引

(date, col1, col2, col3, col4)

用于此查询。你可能有一个日期范围。

这可能很慢，尤其是当桌子真的很大的时候。每次处理请求时，您最好使用字符串构建一个自定义查询。查询优化器比? IS NULL OR子句更能理解这样的内容。

WHERE date <= ?
AND date >= ?
AND col2 = ?
AND col3 = ?

Answer 2

您可以将LIKE子句与COALESCE()函数和NULLIF()函数结合使用，以防止NULL值传递给LIKE子句

const query = "SELECT * FROM accounts WHERE name LIKE COALESCE(NULLIF(?, ''), '%')";
const values = [name];