MySQL のNULL ではまったことあれこれ

MySQL に限らず、SQL のNULL の仕様には何回か「えっ」と驚くことがあったのでメモしておこうと思います。5.1 版の日本語マニュアルがなかったものについては、4.1 のマニュアルを参照しました。

そもそもNULL は何を意味するか

NULL は未定義または、不明を意味する。「電話番号を持たない」ということを表現する場合は、NULL ではなく、空の文字列を使う。

NULL 値というものを SQL 初心者はよく混乱します。SQL 初心者は、多くの場合、NULL が空文字 "" と同じであると考えてしまいます。これは違います。たとえば、以下のステートメントは完全に別のものです。
mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ("");
どちらのステートメントも、値を phone カラムに挿入しています。しかし、最初のステートメントは NULL 値を挿入し、2 つ目は空文字を挿入しています。最初のステートメントは ``電話番号が不明'' であると考えることができ、2番目は``電話を持っていない'' と考えることができます。
MySQL :: MySQL 4.1 リファレンスマニュアル :: A.5.3 NULL 値の問題

NULL との比較演算結果は常にNULL

これは多分、誰もが一度ははまる。
MySQL :: MySQL 4.1 リファレンスマニュアル :: A.5.3 NULL 値の問題

=, <, > 等、値のどちらかにNULL が含まれる場合、結果は常にNULL
- つまり、NULL = NULL はTRUE ではなく、NULL
値がNULL のカラムを検索する場合は、IS NULL を使う
CASE val WHEN NULL はヒットしない
- COALESCE や、CASE WHEN val IS NULL などで対応
うっかりJOIN の条件内で= で比較してしまったり

余談として、rails のfind_by_xxx メソッド等動的ファインダは、引数にnil を与えると、IS NULL を生成するようにできている。

NULL と論理演算子

比較演算子以上にはまる。
MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.1.4 論理演算子

論理演算の結果は、TRUE(1)、FALSE(0)、NULL の3つのいずれかになる
!NULL はNULL
FALSE(0) AND NULL は0、TRUE(1) AND NULL はNULL
FALSE(0) OR NULL はNULL、TRUE(1) OR NULL は1

単体だと「ふーん」という感じしかしないかもしれないけど、今回は以下のような例ではまった。

タスク管理アプリケーション
タスクにはタスク開始、終了予定日を設定できる
タスクは、「2010/03/01 - 2010/03/31」のように日付の範囲を指定して検索できる
- 但し、指定する日付の上限、下限共に「なし」を指定することができる。

で、以下のようなSQL を構築していた。:period_from, :period_to が検索時に指定した値に置き変えらえる。

SELECT * 
FROM tasks 
WHERE !((period_from < :period_from AND period_to < :period_from) OR (:period_to < period_from AND :period_to < period_to))

上記のSQL は、指定する期間の上限、下限両方が指定された場合を想定して組んだもの。ここで、ふと思う。

「:period_from か:period_to のどちらかがNULL だったら、結果は!NULL、つまりTRUE になって、条件式の結果が必ずTRUE になってしまう気がする」

でも、ならない。先程書いた通り、!NULL はNULL だから。:period_from, :period_to の少なくとも1つがNULL だった場合、先程のSQL の結果がTRUE になることはない。FALSE かNULL になってしまう。このように複数の条件式の組合わせによって求められる結果があって、そのどこかの条件式の結果がNULL になる場合、予想外の結果になってしまうことがある。
教訓として、NULL が結果に含まれる場合は「IS NULL」等を使ってNULL を区別して扱う必要がある。NULL の性質を上手く使って…とかダメ。

NULL とインデックス

IS NULL を使用した検索では、インデックスが使用される
- MySQL :: MySQL 5.1 リファレンスマニュアル :: 6.4.5 MySQLにおけるインデックスの使用
NULL を許可するカラムにユニークインデックスを貼った場合、各々のNULL は別の値として区別され、重複エラーを発生させない
- MySQL :: MySQL 4.1 リファレンスマニュアル :: 6.5.3 CREATE TABLE 構文

NULL とソート

MySQL :: MySQL 4.1 リファレンスマニュアル :: A.5.3 NULL 値の問題

ORDER BY を使用する際、降順でソートするように DESC を指定すると、NULL 値が最初または最後に表示される
- 5.0.67 では、DESC の場合はNULL が最後に、ASC の場合は最初にヒットした

NULL と集計

MySQL :: MySQL 4.1 リファレンスマニュアル :: A.5.3 NULL 値の問題

COUNT()、MIN()、SUM() などの集約関数では、NULL 値は無視される
- COUNT(*) は、個々のカラム値ではなくレコードをカウントするので値がNULL のレコードもカウントされる
GROUP BY を使用すると、すべての NULL 値が同じと見なされる

TIMESTAMP とNULL

MySQL :: MySQL 5.1 リファレンスマニュアル :: 10.3.1.1 TIMESTAMP MySQL 4.1での性質

TIMESTAMP 型はNULL を許可しない
TIMESTAMP 型をNULL 許可で定義すると、「NOT NULL DEFAULT CURRENT_TIMESTAMP」と定義される
- このように定義されたTIMESTAMP 型にNULL を指定すると、現在の時刻がセットされる
- ADD COLUM で上記のTIMESTAMP 型を追加した場合、それまでに作成されたレコードのカラム値は「0000-00-00 00:00:00」となる

ちなみに、4.1 のマニュアルには以下のように書かれている。

TIMESTAMP 型カラムでは、他のカラム型とは異なる方法で NULL 値が処理される。TIMESTAMP 型カラムには、NULL は格納できない。カラムに NULL を挿入すると、カラムの値として現在の日時が設定される。TIMESTAMP 型のカラムはこのように動作するため、NULL 属性と NOT NULL 属性は通常どおりには適用されず、それらを指定しても無視される。
その一方で、TIMESTAMP 型のカラムを MySQL クライアントにとって使用しやすくするために、サーバは、（実際には、TIMESTAMP 型カラムには NULL 値は格納されないにもかかわらず）TIMESTAMP 型カラムについて、NULL 値の割り当てが可能（true）と報告する。DESCRIBE tbl_name を使用してテーブルに関する記述を取得すると、これを確認できる。
注意: TIMESTAMP 型のカラムに、値として 0 を設定するのは、NULL を設定するのとは異なる。0 は TIMESTAMP 型の有効な値である。
MySQL :: MySQL 4.1 リファレンスマニュアル :: 6.5.3 CREATE TABLE 構文

追記

id:sugibuchi さんが、ブコメでNULL撲滅委員会の記事を紹介して下さいました。
NULL の何が問題か、NULL を回避するにはどうしたらいいか等が書かれていて参考になります。記事が若干前のものなので、古い情報もあります。
例えば、IS NULL、IS NOT NULL ではインデックスが使われないと書かれているのですが、少なくともMySQL 4.1.22ではIS NULL、IS NOT NULL でもインデックスが使われることを確認しました。EXPLAIN にもインデックスを使う旨が表示され、パフォーマンスも実際に向上します。
参考になる部分はNULL の回避策。

数値カラムはNULL の変わりに0 を使うとSUM 等に影響がなくて大体上手くいく
日付等は最大値、最小値を使う('0001-01-01', '9999-12-31')といい

「'0001-01-01'」なんて使えるんですね。2038 年問題とかに影響されるのかと思っていました。
NULL オブジェクトパターン的発想ですね。勉強になりました!