WHERE子句中为NULL

Question

I have the following prepared statement. All is good...

$stmt=$conn->prepare('UPDATE t1 SET c1=? WHERE c2=? AND c3!=?');
$stmt->execute(array(1,2,3));

But now I wish to use the same prepared statement, but include NULL values in the WHERE clause.

$stmt->execute(array(1,NULL,NULL));

Will this resolve to UPDATE t1 SET c1=1 WHERE c2=NULL AND c3!=NULL? Isn't the proper query UPDATE t1 SET c1=1 WHERE c2 IS NULL AND c3 IS NOT NULL? How can I have a single prepared statement which allows both NULL and not NULL values in the WHERE clause?

Answer 1

You asked if a SQL query containing

 WHERE col = ?

will automatically be converted to

 WHERE col IS NULL

by binding a NULL value to the parameter.

The answer is no. If a column has a NULL value, it can never compare equal to anything.

You need a different prepared query for that.