MySQLのDatetime型にはnullが二種類ある（0000-00-00とODBC）
adminMySQLのDatetime型にはnullが二種類ある、ように見える。これは正しくないが、そんな感じに見えることがある。'0000-00-00'が絡む場合だ。
下記SQLを実行してみてほしい。a_testというテーブルを作って、そこにテストデータを3レコード突っ込んで、selectしている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE `a_test` ( Number integer(11) default null, `UpdateDatetime` datetime DEFAULT NULL ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = 'test'; insert into a_test (Number,UpdateDatetime) values (1,null) , (2,'0000-00-00') , (3,'0001-01-01') ; select Number , UpdateDatetime , ifnull(UpdateDatetime, '0002-01-01') , UpdateDatetime = '0000-00-00' , UpdateDatetime is null from a_test ; |
最後のselect文を、著名なSQL開発環境であるA5:SQLで実行すると結果セットはこんな感じになる。
Numberが1と2のレコードは、UpdateDatetimeがどちらもNULLなのに、その右の列のifnullで返ってくる値が違う。なんだこれは。
もう一つ変なのは、Numberが2のレコードで、UpdateDatetimeがnullなのに、それに対して'0000-00-00'を判定したらtrueが返ってくることだ。
じゃあ'0000-00-00'ってnullと等価なのか？でも、nullはisnull以外で演算したら全部nullになるって習ったしなあ。
と、悩んで色々調べた。
実は、Numberが2のレコードのUpdateDatetimeはNULLではなく、insert時に記述されている'0000-00-00'なのだ。'0000-00-00'はnullではない。実際に、コマンドラインから同じselect文を実行すると下記のようになる。
この現象は、ODBCの仕様によって引き起こされている。
A5:SQLをはじめとする多くのSQL開発環境は、ODBCを通してデータをやりとりしている。
MySQLデータベース→ODBC→A5:SQL
のように、ODBCを通ってから画面に表示されるのだ。
MySQLでは、'0000-00-00'は日付として有効である。だが、ODBCでは'0000-00-00'は日付として有効ではない。だから、'0000-00-00'がODBCを通るとnullになる。
このページの上で最初に出したA5:SQLの結果セットの画像は、ODBCを通っているからnullになる。しかし、コマンドラインからログインしてselect文を実行した場合にはODBCを通っていないので'0000-00-00'はそのまま'0000-00-00'として表示される。
ということだ。結構ハマったぞ。くそ。
