MySQLでdatetimeに10000年を指定すると残念な事になる。
MySQLのBETWEENに'10000-01-01 00:00:00'のように9999年以上の値を入れると予期せぬ結果が返ってくる
MySQL ver : 5.6.20
こんなテーブルに対して
mysql> select * from date_test_table; +---------------------+ | date | +---------------------+ | 2015-01-01 00:00:00 | | 9999-01-01 00:00:00 | | 0000-01-01 00:00:00 | | 1015-01-01 00:00:00 | | 3015-01-01 00:00:00 | +---------------------+
BETWEENのminのほうに1000年と間違えて10000年を指定してしまった。
普通はエラーになるだろうと思ったらなぜかレコードが返ってきた。
謎い。
mysql> select * from date_test_table where date between "10000-01-01" AND "2050-01-01"; +---------------------+ | date | +---------------------+ | 2015-01-01 00:00:00 | | 0000-01-01 00:00:00 | | 1015-01-01 00:00:00 | +---------------------+
どうやら挙動的には0000年になっているっぽい
DateTimeは0年から9999年まで
そもそもMySQLのDateTime自体が9999年までしか対応していない。
更新はエラーだけど参照はwarning
実際に10000年に関してのクエリを打ってみると、更新はエラーになるが参照はwarningという不思議。
mysql> insert date_test_table set date = '10000-01-01 00:00:00'; ERROR 1292 (22007): Incorrect datetime value: '10000-01-01 00:00:00' for column 'date' at row 1 mysql> select * from date_test_table where date = "10000-01-01 00:00:00"; Empty set, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------+ | Warning | 1292 | Incorrect datetime value: '10000-01-01' for column 'date' at row 1 | +---------+------+--------------------------------------------------------------------+
BETWEENは空文字でも受け付ける
一方で、BETWEENは空文字でもクエリを受け付ける仕様のようだ。しかも自動的に最小の値になっているっぽい。
mysql> select * from date_test_table where date between "" AND "2050-01-01"; +---------------------+ | date | +---------------------+ | 2015-01-01 00:00:00 | | 0000-01-01 00:00:00 | | 1015-01-01 00:00:00 | +---------------------+
BETWEENとDateTimeの仕様の合わせ技でおかしな事に
この二つの挙動が合わさると、BETWEENのmin側に10000年を入れるとempty(="")と評価されて、0000年からのレコードを返してしまうようだ。
まとめ
そもそも、10000年を渡す方が悪いのでアプリ側でバリデーションをしっかりしましょう。という事で。