SQLアンチパターン-13章 恐怖のunknown-まとめ

NULL値にもっと注意深くなろう

今回は値がないということに対する正しい挙動を学ぶことがゴールです。
プログラミング言語によって「値がない」ということをどう扱うかは様々です。

SQLではどのように扱われるでしょうか。
それはNULLです。

• SQLはNULLと(false、0、空の文字列）を区別します。
• NULLとの四則演算の結果は必ずNULLになります。決して0の扱いではありません。
  • coalesce関数で0に変換はできる
• NULLとの文字結合もまたNULLです。（DBの種類によっては違うそうです）

NULLを含む検索

SELECT * FROM Books WHERE category = 'comedy';

この検索を行ったとき、categoryがNULLの行は当然取得されません。

SELECT * FROM Books WHERE NOT (category = 'comedy');

この検索を行ったときもまた、categoryがNULLの行は取得されません。
ある値ではないということは等価演算になりますが
つまり、 NULL != 'comedy'がTRUE、もしくは
NULL == 'comedy'がFALSEを返せば良いのですが
NULLとの等価演算は決してTRUEにはなりません。
どこまでいってもNULLです。

MySQLには、NULLに対応した演算子があります。
<=>です。

SELECT * FROM Books WHERE category <=> NULL;

プリペアドステートメントにNULLを渡せるか

プリペアドステートメントで予約語であるNULLを値として渡すには
bindValue()を使うしかありません。第3引数に型を指定できるので
そこでPDO::PARAM_NULL'を指定してあげます。
excute`でそのまま渡すのでは対応ができないということです。

NULLから逃げるのも違う

そんなNULLはTRUEorFALSEで扱えるわけでもないので
また別の値を使った独自のルールを使うことで対応しようとすることもあるでしょう。
例えば数字でステータスを表す時に
1以上は有効、-1は存在しないというルールを決めたとします。
得点計算をするカラムがそのように扱われていたとしたらどうなるでしょう。
もし仮に、平均や偏差値などを算出する際に
単純に-1を計算に含めてしまうと、求めていない計算結果が出力されてしまいます。

ここでは、本来得点を意味するデータが入るところに
別論理の存在しているかどうかを定義する-1が混ざってしまっています。
これではまともなテーブル設計とは言えません。
アプリケーションレベルでも注意する必要のある厄介な仕様となってしまいます。

NULLを理解しよう

NULLは真偽のロジックである2値論理とは別の、3値論理として理解する必要があります。
4値論理とかもあるそうで。
参考

NULLと演算子

NULLを使った計算がどうなるか、まとめてみましょう。

下記の計算結果は全てNULLです。

NULL = 0
NULL = 123
NULL <> 123
NULL + 123 (四則演算全て)
NULL = NULL
NULL <> NULL

特に
NULL = NULLはこういう論理です。
不明な値と不明な値を比較しても、等しいかどうかはわからない
NULL <> NULLは
不明な値と不明な値を比較しても、等しくないかどうかはわからない
ということになります。

NULLと論理式

NULL AND TRUEは、NULLとなります。
NULL AND FALSEでは、AND FALSEでFALSEが確定しているのでFALSEになります。
NULL OR FALSEはNULLとなります。
NULL OR TRUEはOR TRUEでTRUEが確定しているのでTRUEになります。
NOT (NULL)は、NULLになります。
AND FLASEとOR TRUEがキーですね。
これはそのまま覚えるのが良さそう。

NULLで検索したい時

MySQLにはNULLで検索するための構文が用意されています。
それがIS NULLです。

SELECT * FROM Books WHERE category IS NULL;

こう使います。

IS NOT NULLも存在します。

呟き

思ったけど、NULLって虚数みたいな感覚で扱うとわかりやすいんじゃ？？

メモ

a<=>bはaとbは等しい(NULLにも対応) => spaceshipというらしい
a<>bはaとbは等しくない
知らなかった。