PreparedStatementでNULLを扱う

挿入・更新する値がNULLであり得る場合。（決まっているわけではなく、普通の値が入ることもある）

php - Execute PDO with an array containing null values - Stack Overflow

以下のようなSQLを、PDOのプリペアドステートメントにセットしたとして、

UPDATE table
   SET name=:name, id_extra1=:ex1, id_extra2=:ex2
 WHERE id=1;

たとえばid_extra2に入れる値が、NULLであり得るとする。

// $pdo = 接続済みのPDOインスタンス

$stmt = $pdo->prepare(
        'UPDATE table
         SET name=:name, id_extra1=:ex1, id_extra2=:ex2
         WHERE id=1;');
$stmt->execute([
    ':name' => 'hoge',
    ':ex1' => 1,
    ':ex2' => null, // ここ
]);

上記のコードでは、id_extra2カラムに入るのはNULLにはならない。id_extra2の型がINTなら'0'、DATEなら'0000-00-00'になってしまう。

これは、execute()の引数に[placeholder ⇒ value]の配列を与えた場合、その型は全てPDO::PARAM_STR（文字列型）として処理されてしまうため。なので、そこにPHPとしてのnullが与えられても、SQL上では空文字列が指定されたことになってしまう。

-- 上記の$stmtの実行時の状態(たぶんこんな感じ)
UPDATE table
   SET name='hoge', id_extra1='1', id_extra2=''
 WHERE id=1;

SQLとしてのNULLを代入するには、placeholderに値をバインドする際、型にPDO::PARAM_NULLを指定しなければならない。

しかし、execute()の引数に渡す方法では、型は指定する方法が無い。

bindValue()なら、第3引数に型を指定することができるので、それを使う。

// $stmt は上記のものと一緒
$stmt->bindValue(':name', 'hoge');
// INTに関しては、型を省略してもSQLが解釈してくれるため、不要っちゃ不要。明示するという意味はある
$stmt->bindValue(':ex1', 1, PDO::PARAM_INT); 
$stmt->bindValue(':ex2', null, PDO::PARAM_NULL); // ←ここで指定
$stmt->execute();

もし、execute()と似た感じで実行時に配列一発で与えたければ、擬似的な関数を使う方法がある。（ただしこれはこれで、$valueには意図しない配列が入ることは無い、などの前提が必要になるけど）

function executeWithDataTypes(PDOStatement $stmt, array $values)
{
    foreach ($values as $key => $value) {
        if (is_array($value)) {
            $stmt->bindValue($key, $value['value'], $value['type']);
        } else {
            $stmt->bindValue($key, $value);
        }
    }
    return $stmt->execute();
}

// $stmt は上記のものと一緒
executeWithDataTypes($stmt, [
    ':name' => 'hoge',
    ':ex1' => 1,
    ':ex2' => ['value' => null, 'type' => PDO::PARAM_NULL],
]);

WHERE節の中で、あるカラムの値が、NULLのものを抽出する時もある場合。たとえば以下のSQLで、:idがNULLである時もあるような場合。

SELECT * FROM table WHERE id=:id;

-- 以下の構文は、SQL構文エラーになる。
SELECT * FROM table WHERE id=NULL;

-- NULLの場合はISで比較しなければならない。
SELECT * FROM table WHERE id IS NULL;

プレースホルダで「= ?」と「IS NULL」に同時に対応する方法 - Qiita

MySQLでは、右辺が、実態のある値でも、NULLでも、同じように比較できる演算子が用意されている。楽ちん。

SELECT * FROM table WHERE id<=>:id;

ただし、':id' placeholderへの関連づけは、挿入・更新時と同様、型を指定する必要があるため、その点は上記参照。

PHPで、SQLを生成するときに場合分けするという愚直な方法が、ちょっと条件分岐は増えるけど、一番わかりやすそう。

$sql = 'SELECT * FROM table ';
$query_params = [];
if ($id === null) {
    $sql .= 'WHERE id IS NULL;';
} else {
    $sql .= 'WHERE id = :id';
    $query_params[':id'] = $id;
}

$stmt = $pdo->prepare($sql);
$stmt->execute($query_params);