让mysql忽略哪里条件 [英] Getting mysql to ignore where condition

问题描述

例如，如果未设置变量，是否可以使mysql忽略条件

Is there to make mysql ignore conditions if a variable isn't set, e.g.

SELECT *
FROM foo
WHERE id = $id
AND bar = $baz

如果设置了$ baz，则正常运行查询，否则运行查询减去AND子句?

If $baz is set run query as normal, else run the query minus the AND clause?

谢谢

推荐答案

SELECT *
FROM foo
WHERE id = $id
AND ( bar = $baz OR $baz [equals null, empty string, zero, whatever] )

我不知道MySQL是否在执行计划中使用短路评估 ，但最好将便宜的比较放在首位，例如:

I don't know if MySQL ever uses short circuit evaluation in its execution plan, but it may be beneficial to put the cheaper comparison first, like:

SELECT *
FROM foo
WHERE id = $id
AND ( $baz [equals null, empty string, zero, whatever] OR bar = $baz )

您可以将这种方法与多个参数一起使用.

You can use this methodology with multiple parameters.

SELECT *
FROM foo
WHERE id = $id
AND ( $baz [equals null, empty string, zero, whatever] OR bar = $baz )
AND ( $x = 0 or x = $x )
AND ( $y IS NULL OR y = $y )

-- etc.

这篇关于让mysql忽略哪里条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！