MySQL の "0000-00-00" は NULL？

mysql

数日前に、とみたまさひろさんのこんなツイートがありました。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.3.2 比較関数と演算子
"NOT NULL として宣言された DATE および DATETIME カラムでは、次のようなステートメントを使用することで、特殊な日付 '0000-00-00' を検索できます"

その後の twitter でのとみたさんとのやりとりも含め、なんじゃそりゃな仕様に興味を持ったので、自分でも試してみました。


テーブルを作ります。

mysql> CREATE TABLE dtsample (id integer, dt date);

データを入れます。一応中身も見てみる。

mysql> INSERT INTO dtsample values (1, null), (2, "0000-00-00"), (3, "0000-00-00"), (4, "2015-03-10"), (5, "2014-11-05"),(6,"2016-01-01");
mysql> SELECT * FROM dtsample;
+------+------------+
| id   | dt         |
+------+------------+
|    1 | NULL       |
|    2 | 0000-00-00 |
|    3 | 0000-00-00 |
|    4 | 2015-03-10 |
|    5 | 2014-11-05 |
|    6 | 2016-01-01 |
+------+------------+


このテーブルから、NULLのものと、NOT NULL のものを検索してみます。

mysql> SELECT * FROM dtsample WHERE dt IS NULL;           
+------+------+
| id   | dt   |
+------+------+
|    1 | NULL |
+------+------+

mysql> SELECT * FROM dtsample WHERE dt IS NOT NULL;
+------+------------+
| id   | dt         |
+------+------------+
|    2 | 0000-00-00 |
|    3 | 0000-00-00 |
|    4 | 2015-03-10 |
|    5 | 2014-11-05 |
|    6 | 2016-01-01 |
+------+------------+


あれれ？　普通ですね。6件のうち、IS NULLが1件、IS NOT NULLが５件。
もう一度マニュアルをよく読んでみましょう。

NOT NULL として宣言された DATE および DATETIME カラムでは、次のようなステートメントを使用することで、特殊な日付 '0000-00-00' を検索できます。

カラム定義として、NOT NULL 制約が必要だったようです。
やりなおおし。

mysql> CREATE TABLE dtsample2 (id integer, dt date NOT NULL);

mysql> INSERT INTO dtsample2 values (11, null), (12, "0000-00-00"), (13, "0000-00-00"), (14, "2015-03-10"), (15, "2014-11-05"),(16,"2016-01-01");
ERROR 1048 (23000): Column 'dt' cannot be null

mysql> INSERT INTO dtsample2 values (12, "0000-00-00"), (13, "0000-00-00"), (14, "2015-03-10"), (15, "2014-11-05"),(16,"2016-01-01");
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM dtsample2 WHERE dt IS NOT NULL;      
+------+------------+
| id   | dt         |
+------+------------+
|   12 | 0000-00-00 |
|   13 | 0000-00-00 |
|   14 | 2015-03-10 |
|   15 | 2014-11-05 |
|   16 | 2016-01-01 |
+------+------------+

　先ほどと同じデータを登録しようとしたら、カラムの NOT NULL 制約のために id=11 のデータが登録できなかったので、除外して登録しました。


　さて、ショータイムっ！

NULL のものと NOT NULL のものを、見てみましょう。まず IS NOT NULL。

mysql> SELECT * FROM dtsample2 WHERE dt IS NOT NULL;      
+------+------------+
| id   | dt         |
+------+------------+
|   12 | 0000-00-00 |
|   13 | 0000-00-00 |
|   14 | 2015-03-10 |
|   15 | 2014-11-05 |
|   16 | 2016-01-01 |
+------+------------+

　うん。NOT NULL なんだから、この５件が出ますよね。問題ない。
次に IS NULL。

mysql> SELECT * FROM dtsample2 WHERE dt IS NULL; 
+------+------------+
| id   | dt         |
+------+------------+
|   12 | 0000-00-00 |
|   13 | 0000-00-00 |
+------+------------+

　2件が出てきました。へんなの。
これは、マニュアルにも書いてあるとおり、バグではなく狙ってこのような仕様になっているとのことです。

ODBC では '0000-00-00' 日付値がサポートされていないため、一部の ODBC アプリケーションを取得する際に、これが必要になります。


　カウントも取ってみましょうか。

mysql> SELECT COUNT(*) FROM dtsample2 WHERE dt IS NULL;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

mysql> SELECT COUNT(*) FROM dtsample2 WHERE dt IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

　髪結新三もびっくり。大家さんもびっくり。
「上が十両、下が五両。十両と五両で十五りょ、、、、、りょりょりょ？？？？」まぁ新三のお話の場合は十両と五両で18両くらいになったりするわけで両者ハッピーでしょうけど、RDBMSではこれは困ります。というか気持ち悪い。エチケット袋が必要です。


　更に、この NULL 判定については、微妙に半端な対応になっているようで、CASE文の中の判定では、"0000-00-00" も NULL とは判定されません。

mysql> SELECT id,
    ->        CASE WHEN dt IS NULL THEN 1 ELSE 0 END is_null, 
    ->        CASE WHEN dt IS NOT NULL THEN 1 ELSE 0 END is_not_null
    ->   FROM dtsample2;
+------+---------+-------------+
| id   | is_null | is_not_null |
+------+---------+-------------+
|   12 |       0 |           1 |
|   13 |       0 |           1 |
|   14 |       0 |           1 |
|   15 |       0 |           1 |
|   16 |       0 |           1 |
+------+---------+-------------+

　ID IN (12,13) のところでは is_null にも is_not_null にも 両方に 1 が立つことを期待していたのですが、そうはなりませんでした。WHERE dt IS NULL の COUNT(*) が2件もあったのにねぇ。


　こんな感じで、とみたまさひろさんの発見（発掘？）した、MySQL の新しい楽しみ方を紹介しました。みなさまよいお年を！