数日前に、とみたまさひろさんのこんなツイートがありました。
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 の新しい楽しみ方を紹介しました。みなさまよいお年を！
