编写一个在条件中具有可为空值的准备好的语句 [英] Write a prepared statement with nullable values in conditions

问题描述

有没有办法编写一个准备好的语句，其中一个值与条件中的另一个值进行比较，但我不知道这个值是否为 NULL.

Is there a way to write a prepared statement where a value is compared to another value in a condition and I don't know, whether this value is NULL or not.

SELECT `foo` FROM `bar` WHERE `a1` = :a1 AND `a2` = :a2

如果我将这个准备好的语句与 a1 => 一起使用；null 和 a2 =>42，那么结果查询将是:

If I would use this prepared statement with a1 => null and a2 => 42, then the resulting query would be:

SELECT `foo` FROM `bar` WHERE `a1` = NULL AND `a2` = '42'

这当然不是我想要的.在那种情况下我需要这个:

This is, of course, not what I want. I would need this in that case:

SELECT `foo` FROM `bar` WHERE `a1` IS NULL AND `a2` = '42'
                                   ^^

a1 和 a2 都可以为空.我不想定义 4 个准备语句:

Both a1 and a2 are nullable. I don't want to define 4 prepare statements:

-- I would use this, if both values are not null
SELECT `foo` FROM `bar` WHERE `a1` = :a1 AND `a2` = :a2

-- and this, if the expected value of a1 is null
SELECT `foo` FROM `bar` WHERE `a1` IS NULL AND `a2` = :a2   

-- and this, if the expected value of a2 is null
SELECT `foo` FROM `bar` WHERE `a1` = :a1 AND `a2` IS NULL

-- and this, if I would expect both values to be null
SELECT `foo` FROM `bar` WHERE `a1` IS NULL AND `a2` IS NULL

推荐答案

MySQL 提供了一个 null-safe 比较 <=> (spaceship) 操作符.这指定了将返回 TRUE 或 FALSE 的相等比较，并且当任一操作数为 NULL 时不会返回 NULL.

MySQL provides a null-safe comparison <=> (spaceship) operator. That specifies an equality comparison that will return TRUE or FALSE, and won't return NULL when either of the operands is NULL.

作为演示:

SELECT NULL=NULL
     , NULL<=>NULL
     , 1=NULL
     , 1<=>NULL
     , 1=0
     , 1<=>0
     , 1=1
     , 1<=>1

返回:

NULL=NULL  NULL<=>NULL  1=NULL  1<=>NULL     1=0  1<=>0     1=1  1<=>1  
---------  -----------  ------  --------  ------  -----  ------  -----
   (NULL)            1  (NULL)         0       0      0       1      1

<小时>

那个比较操作本质上是速记.返回:

---

That comparison operation is essentially shorthand. The return from:

 a <=> b

相当于从

 ( a = b OR ( a IS NULL AND b IS NULL ) )

<小时>

为了回答您提出的问题，我们可以使用 NULL 安全比较 <=>(太空船)运算符编写一个语句，如下所示:

 SELECT `foo`
   FROM `bar`
  WHERE `a1` <=> :a1
    AND `a2` <=> :a2

<小时>

或者，对于更符合 ANSI 标准且可移植的方法，我们可以在不使用特定于 MySQL 的运算符的情况下实现相同的结果，如下所示:

---

Or, for a more ANSI standards compliant and portable approach, we could achieve the same result without using that MySQL specific operator, like this:

 SELECT `foo`
   FROM `bar`
  WHERE ( `a1` = :a1  OR  ( `a1` IS NULL AND :a1d IS NULL ) )
    AND ( `a2` = :a2  OR  ( `a2` IS NULL AND :a2d IS NULL ) )

注意，我们需要将每个绑定值的值传入两次.过去，PDO 不允许对绑定占位符的多个引用.(不确定在更新的 PDO 版本中是否仍然如此.)如上所示，解决方法是在语句中使用 四个 不同的占位符，并为 提供相同的值:a1 和 :a1d.)

Note that we need to pass in the value of each bind value two times. In the past, PDO has not allowed more than one reference to a bind placeholder. (Not sure if this is still the case in more recent versions of PDO.) The workaround, as demonstrated above, is to use four distinct placeholders in the statement, and supply the same value for :a1 and :a1d.)

这篇关于编写一个在条件中具有可为空值的准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！