"SQLアンチパターン"を読み、MySQLにおけるNullの扱いについて再確認した。

SQLアンチパターン

SQLアンチパターン

DBMSを使う人なら誰もがハマるNullの取り扱いだが、結論から言うとDBMSのNullはよくあるプログラム言語のそれとは違うので、新しい物として正しく理解しようという事。Nullを怖がっては真っ当なデータ設計は出来ない。

DBMSにおけるNullとは

先に述べたように、DBMSのNullとアプリ側のNullは全くの別の物です。アプリ側のNullは値が存在しない事を示すのに対し、DBMSのNullは値が不定であることを示します。(3値論理と言われる概念が導入されています。)

そのため、以下のSQLをMySQLで実行すると、Nullが結果として返ってくる。

> select Null + 1;
> Null

これは、Nullが不定を示す値であるため、何か分からない数に1を足しても何になるかわからないからである。

Javaだと？

そういう意味では、JavaのNaNと同様の物と考えると分かりやすいと思う。 (DBMSのNullは他の型に対しても同様の性質を示すことに注意)

public class NanTest{
  public static void main(String args){
    System.out.println(Double.NaN + 1);
  }
}
> NaN

上記のプログラムでも結果はNaNとなる。

Nullを用いた式評価

最もNullでハマる部分はここではないだろうか。 以下のSQLの挙動を不思議に思ったことは多くの開発者の記憶に有るはず…

> select Null = Null;
> Null

これも先の理由(Nullが不定であること)が分かるとなるほどと理解できる。 不定の値同士を比較しても結果が分からないのは当然である。

これを意図通りに記述する為には、以下の様に書く

> select Null is Null;
> 1

因みに、JavaでもNaN同士を比較してもtrueにはならない。

public class NanTest{
  public static void main(String args){
    System.out.println(Double.NaN == Double.NaN);
  }
}
> false

スカラー式でのNullの扱い

上記の様な動作をまとめた表が、SQLアンチパターンに有ったので一部引用させていただく

式 予想した結果 実際の結果
Null = 0 TRUE NULL
Null = 12345 FALSE NULL
NULL <> 12345 TRUE NULL

SQLアンチパターン 13.5.1 スカラー式でのNULL

論理式でのNull

論理式でのNULLの扱いも同様に、初めて見るときには"おっ？"と考えてしまうだろう。 これは以下の様になる。

式 予想した結果 実際の結果
Null AND TRUE FALSE NULL
Null AND FALSE FALSE FALSE
NULL OR FALSE FALSE NULL
NULL OR TRUE TRUE TRUE
NOT (NULL) TRUE NULL

SQLアンチパターン 13.5.2 論理式でのNULL

Nullは不要なのか？

この様な挙動を見ると、一部の開発者は拒否反応を起こし、Nullは使わないようにしよう！NOT NULL制約＋デフォルト値や、使用されない値(0とか)で回避すること！と言い出す始末になる。これが恐怖のunknown現象である。

さらに、Nullを扱わないことの理由として、IS NULL 式にはインデックスが使用されないという理由もある。

MySQLではIS NULLでインデックスが使用される

他のDBMSは未調査だが、MySQLに関してはIS NULLでインデックスが使用されるので、これに関しては問題はない。

MySQL では、col_name = constant_value.の場合と同じ最適化を col_name IS NULLに対しても実行できます。 たとえば、MySQL では、インデックスと範囲を使用して、IS NULLで NULLを検索できます。

MySQL 5.1 リファレンスマニュアル 6.2.7. IS NULL最適化

Nullはデフォルト値や、使用されない値に置き換えられるのか？

これは状況により異なるため、完全に置き換えられるとは言えない。

例えば、ある商品に対するコメント(body)と評価(rate)を格納する以下のようなCommentテーブルが有るとする。 尚、評価を残さないテキストのみのコメントも可能であるとする。

create table Comment(
  comment_id varchar(100) NOT NULL;
  body text NOT NULL DEFALUT '';
  rate int DEFAUTL NULL;
  comment_by varchar(100) NOT NULL;
  );

この場合、NULLを避けるためにbodyをNOT NULLにし、デフォルト値を設定することは問題ないが、rateにはそれを適用するのは相応しいとは言えない。

何故なら、このCommentの仕様では評価を残さないコメントも存在するため、デフォルト値として数値を設定してしまうと後に評価の平均値等を計算させる際に評価を残さないコメントを除くことが困難になるからである。では、デフォルト値として絶対に使われない値(-9999等)を設定すると良いのではないか？と考えるが、その為にドキュメントを残す必要を考えると、それも上手くない。 このため、このような状況ではNullが妥当となるわけである。

まとめ

  • DBMSのNullはプログラム言語のNullとは違う
  • むしろNaNと似ている
  • Nullを全て排除しようとせず、適切に理解し、適切な場所で使うこと