MySQLのDatetime型にはnullが二種類ある（0000-00-00とODBC）

MySQLのDatetime型にはnullが二種類ある、ように見える。これは正しくないが、そんな感じに見えることがある。'0000-00-00'が絡む場合だ。

下記SQLを実行してみてほしい。a_testというテーブルを作って、そこにテストデータを3レコード突っ込んで、selectしている。

最後の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'として表示される。

ということだ。結構ハマったぞ。くそ。