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;

MySQLの場合

プレースホルダで「= ?」と「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);