MySQLのDATE型初期値は厄介者

こんにちわ、Takeuchiです。

今回はMySQLに関する技術的な話を書こうと思います。
(といっても、他のブログで見つけた記事の横展開ですが・・・)

MySQLはオープンソースで公開されていて、使用しているシステムも多いDBです。

まず基本的な話として・・・
DBにレコードを格納する場合に値が未決定の可能性がある場合にNULLを設定するのですが、
日付についてはNULLとは別に’0000-00-00 00:00:00’という日付が設定できます。

実は、この’0000-00-00 00:00:00’という値が厄介なのです。

【1つ目の問題点】
0000-00-00 00:00:00 はMySQLの独自な仕様で
NOT NULL制約のカラムではNULLと等価であり、NULLではない という仕様がある。

ん???何言ってんだと思います。
そりゃそうです、「NULLと等価である」と「NULLではない」は正反対な仕様ですから。

簡単に説明すると・・・
Date型カラムに「0000-00-00 00:00:00」が存在していた場合
SQLで”カラム is NULL”と検索した場合、”カラム is not NULL”と検索した場合の両方にマッチするんです。
この仕様を知らないで件数のカウントや別カラムの合計値をSQLで求めてしまうと
意図しない結果を取得してしまうことになります。

【2つ目の問題点】
オブジェクト関係マッピング(ORM)が対応してない

ORMとは、DBの型とJavaやPHPの型のマッピングの事なのですが、
DATE型カラムに「0000-00-00 00:00:00」が存在していた場合、
JavaやPHPのDate型に変換しようとするとエラーになってしまうという事です。
DBの値はNULLではないのに、プログラムで使用するとエラーになるのは困ることになります。

【3つ目の問題点】
DB移行で困る

MySQLでは「0000-00-00 00:00:00」という値はサポートしていますが
なんらかの理由でMySQL以外のRDBMSに移行することになった場合、
MySQL以外では「0000-00-00 00:00:00」をサポートしていないので
移行しようとするとエラーになります。
また、移行がする場合は値の修正が必要になり手間が増えることになります。

結論を言うと・・・
「MySQLの0000-00-00 00:00:00は使ってはならない」

では、使わないようにするためにはどうすればいいか
sql_modeのNO_ZERO_DATEとSTRICT_ALL_TABLEを有効にすることです。
「NO_ZERO_DATE」
→有効でない場合、’0000-00-00′ は許可され、挿入によって警告が生成されません
→有効である場合、’0000-00-00′ は許可されるが、挿入時に警告が生成されます
「STRICT_ALL_TABLE」
→有効でない場合、厳密モードではないため警告はスルーします
→有効である場合、厳密モードとなり警告はエラーと判断し残りの行は無視します

上記2設定を有効にすれば0000-00-00 00:00:00は登録できないので
今後困ることはなくなると思います。

と、いうことでMySQLのDate型(TimeStamp型含む)には気をつけましょう

引用元ブログ:「そーだいなるらくがき帳」

About the author

take0089

システムの運用、改善、案件対応と幅広くやってる何でも屋
言語はPHP、Java、C#をメインにしつつ最近はPythonも勉強中

Add Comment

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

By take0089

最近の投稿

アーカイブ

カテゴリー

タグクラウド

コーポレートサイト