Write a prepared statement with zero values ​​in conditions

Question

Is there a way to write a prepared statement where the value is compared with another value in the condition, and I don't know if this value is NULLor not.

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

If I used this prepared statement with a1 => nulland a2 => 42, then the resulting query:

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

This, of course, is not what I want. In this case, I will need the following:

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

Both a1and a2are NULL. I do not want to define 4 preparation 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

Answer 1

MySQL <=> ( ). , TRUE FALSE NULL, 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

---

. :

 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, :

 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.)