mysql - 查询 |处理多个具有潜在空值的 where 条件

Question

假设下表:

╔═══╦══════════════╦═════════════╗
║   ║Property A    ║Property B   ║
╠═══╬══════════════╬═════════════╣
║ 1 ║ slow         ║low          ║
║ 2 ║ fast         ║high         ║
╚═══╩══════════════╩═════════════╝

用户可以选择使用两个属性或仅使用一个属性来过滤结果。输入存储在变量 $p1, $p2

如果这两个变量都从用户那里获得值，我可以像这样轻松地查询它们:

select * from table where (propertyA=$p1 and propertyB=$p2)

但是，如果其中一个没有从用户那里获得任何值(value)，我该如何查询？由于上述查询中的行 propertyA=$p1 将变为 false，因为 $p1 可能为 null，但是查询结果应根据 $ 的值返回p2.

例如，对于输入 (null,low)，查询应该返回第一条记录，但对于我的查询，它不会。

此示例仅针对两个属性，我的表中有多个属性，因此实现多个 if 条件会很麻烦。

我想知道是否有任何简洁的方法来解决这个问题。

谢谢!

Answer 1

有两种解决这个问题的基本方法:1)动态生成 SQL 语句，2)用静态 SQL 语句中的表达式处理 NULL

动态方法，如果查询是从应用程序执行的...

我们从一个静态字符串开始，查询每次看起来像什么:

 $sql = 'SELECT t.id
           FROM mytable t
         WHERE 1=1';

然后决定我们是否要将另一个条件附加到 WHERE 子句

 if( $p1 !== '' ) {
    $sql .= ' AND t.propertyA = :p1';
 }
 if( $p2 !== '' ) {
    $sql .= ' AND t.propertyB = :p2';
 }

 // prepare the SQL statement
 $sth=$pdo->prepare($sql);

 // conditionally bind values to the placeholders
 if( $p1 !== '' ) {
    $sth->bindValue(':p1',$p1);
 }
 if( $p2 !== '' ) {
    $sth->bindValue(':p2',$p2);
 }

动态 SQL 的缺点是，对于更复杂的问题，正确构造 SQL 语句可能会变得过于棘手。 WHERE 子句中只有条件，这是一种可行的方法。

另一个缺点是我们最终可能生成各种各样的 SQL 语句，确保每个变体都有合适的执行计划变得很复杂。 WHERE 子句中只有两个可选条件，我们得到了非常易于管理的 4 种变体...

哦，在 1=1 子句中包含条件 WHERE 的原因并不影响语句；优化器足够聪明，可以确定它对每个可能的行都是正确的，因此该条件会被抛出。给我们带来的好处是，当我们附加到 WHERE 子句时，我们无需检查“这是 WHERE 子句中的第一个条件吗？”所以我们知道是否将 WHERE 或 AND 附加到语句中。

---

第二种方法是在 SQL 中使用静态，使用一些表达式。

举个例子，假设propertyA和propertB是字符类型的列:

$sql = "SELECT t.id 
          FROM mytable t
         WHERE t.propertyA <=> IFNULL(NULLIF(:p1,''),t.propertyA) 
           AND t.propertyB <=> IFNULL(NULLIF(:p2,''),t.propertyB)"; 

$sth = $pdo->prepare($sql);
$sth->bindValue(':p1',$p1);
$sth->bindValue(':p2',$p2);

如果我们为 :p1 提供一个非零长度的字符串，那么 NULLIF 函数返回 :p1 ，而 IFNULL 函数返回 :p1 。就像我们刚刚写的一样:

 t.propertyA <=> :p1  

如果我们为 $p1(占位符 :p1)提供零长度字符串，则 SQL NULLIF 函数将返回 NULL。反过来， IFNULL 函数将返回 t.propertyA ，因此该语句将 propertyA 与自身进行比较，因此最终结果将如我们所写

AND t.propertyA <=> t.propertyA  

或者只是

AND 1=1

(不同之处在于优化器不会丢弃我们的条件，因为优化器不知道在准备执行计划时我们将为 :p1 提供什么值。

注意:<=> 飞船运算符是 NULL 安全比较。它保证返回 TRUE 或 FALSE(而不返回 NULL)，这与标准相等比较 ( = ) 不同，标准相等比较 ( propertyA ) 在被比较的值中的一个(或两个)为 NULL 时返回 NULL。

这个:

foo <=> bar

本质上是等效的简写:

foo = bar OR ( foo IS NULL AND bar IS NULL ) 

如果我们保证 :p1 永远不会为 NULL(例如，通过表定义中的显式 NOT NULL 约束)，我们可以放弃 spaceship 运算符而只使用简单的相等比较。

这种方法的缺点是 SQL 语句不太直观；外行人可能会摸不着头脑。所以我们要在代码里加个注释，说明匹配条件是condition，如果:p1是空串，就没有和COALESCE比较。

我们可以使用不同的语法来实现相同的结果，例如，使用更可移植的符合 ANSI 标准的 IFNULL 函数代替 CASE ，并使用 NULLIF 表达式代替 $p1 。 (这将需要我们提供 $p1 到一个额外的占位符，像我们一样编写它，我们只需要提供一次 ojit_code。)

---

但这是两种基本模式。选择你的毒药。