【MySQL】date型、datetime型に対して is null すると？

Tweet

Category MySQL 2010/02/05

１、テーブルを作る

date型でNULLは許可。

create table test_date (dt date);

２、データを挿入

NULL、0000-00-00、普通の日付、不正な日付を挿入してみる。

insert into test_date values (NULL);
insert into test_date values (0);
insert into test_date values ('2010-02-03');
insert into test_date values ('2010-02-00');
insert into test_date values ('2010-02-30');

３、データを確認

本題と関係ないですが、2010-02-30は0000-00-00になるんですね。

select * from test_date

+------------+
| dt         |
+------------+
| [NULL]     |
| 0000-00-00 |
| 2010-02-03 |
| 2010-02-00 |
| 0000-00-00 |
+------------+

４、null許可のdate型カラムで is null すると

ごく普通の結果が返ってきます。

select * from test_date where dt is null

+--------+
| dt     |
+--------+
| [NULL] |
+--------+

５、not null のdate型カラムで is null すると

仕様を知らないと、ちょっと意外な結果が返ってきます。

alter table test_date change dt dt date not null;

select * from test_date where dt is null

+------------+
| dt         |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+

探すと、マニュアル書いてあります。

http://dev.mysql.com/doc/refman/5.1/ja/comparison-operators.html

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

SELECT * FROM tbl_name WHERE date_column IS NULL

ODBC は ‘0000-00-00’ をサポートしていないため、ODBC アプリケーションのあるものの作動にこれが必要になります。

0000-00-00 は引っかかるのですが、2010-02-00 は引っかからない。これもまたややこしい。

このような仕様に依存しないように設計したいものです。