MySQL の sql_mode
で NO_ZERO_IN_DATE,NO_ZERO_DATE
とかを指定すると 0000-00-00
や、月や日が 00
などの日時の挿入を禁止できます。
がしかし、年が 0000
の場合の動きが謎いです。
MySQL のバージョンは次の通り。
select version(); /*-----------+ | version() | +------------+ | 5.7.19-log | +-----------*/
SQL モードを指定します。
set session sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'; select @@sql_mode; /*--------------------------------------------------------------------------+ | @@sql_mode | +---------------------------------------------------------------------------+ | STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO | +--------------------------------------------------------------------------*/
データを入れます。想定通りの動きだと思います。月や日の 00
は無効ですが年の 0000
は有効です。
create table t (d date); insert into t values ('0000-00-00'); /* ERROR 1292 (22007): Incorrect date value */ insert into t values ('0001-01-00'); /* ERROR 1292 (22007): Incorrect date value */ insert into t values ('0001-00-01'); /* ERROR 1292 (22007): Incorrect date value: */ insert into t values ('0000-01-01'); /* Query OK */ insert into t values ('0000-01-02'); /* Query OK */ insert into t values ('9999-12-30'); /* Query OK */ insert into t values ('9999-12-31'); /* Query OK */
なんということでしょう。
select d, date_add(d, interval 1 day) as `d+1` from t; /*-----------+------------+ | d | d+1 | +------------+------------+ | 0000-01-01 | 0000-00-00 | | 0000-01-02 | 0000-00-00 | | 9999-12-30 | 9999-12-31 | | 9999-12-31 | NULL | +------------+-----------*/ select d, date_sub(d, interval 1 day) as `d-1` from t; /*-----------+------------+ | d | d-1 | +------------+------------+ | 0000-01-01 | 0000-00-00 | | 0000-01-02 | 0000-00-00 | | 9999-12-30 | 9999-12-29 | | 9999-12-31 | 9999-12-30 | +------------+-----------*/
9999-12-31 + 1 day
が NULL なのは良いとして 0000-01-01
や 0000-01-02
に date_add
や date_sub
すると 0000-00-00
なりました。
下記の結果を見るに、比較はうまく動いているっぽい。
select d, d < '0000-01-02', d > '0000-01-01' from t; /*-----------+------------------+------------------+ | d | d < '0000-01-02' | d > '0000-01-01' | +------------+------------------+------------------+ | 0000-01-01 | 1 | 0 | | 0000-01-02 | 0 | 1 | | 9999-12-30 | 0 | 1 | | 9999-12-31 | 0 | 1 | +------------+------------------+-----------------*/
どうやら date_add
や date_sub
による演算の結果、年が 0000
になると 0000-00-00
になってしまうっぽい。
select date_add('0000-12-30', interval 1 day) as `0000-12-30 + 1`, date_add('0000-12-31', interval 1 day) as `0000-12-31 + 1`, date_sub('0001-01-01', interval 1 day) as `0001-01-01 - 1`, date_sub('0001-01-02', interval 1 day) as `0001-01-02 - 1`; /*---------------+----------------+----------------+----------------+ | 0000-12-30 + 1 | 0000-12-31 + 1 | 0001-01-01 - 1 | 0001-01-02 - 1 | +----------------+----------------+----------------+----------------+ | 0000-00-00 | 0001-01-01 | 0000-00-00 | 0001-01-01 | +----------------+----------------+----------------+---------------*/
西暦1年の前年は0年ではなく紀元前1年?なのなら 0000
という年は無効な日付になるので結果が 0000-00-00
とかになるのも判らなくもないけど・・それなら date_add
や date_sub
の入力に与えられたときも結果は 0000-00-00
になるべきな気がするし NO_ZERO_IN_DATE
で弾かれてほしい気もする。
あとこれ、テーブルへの格納ではなくて date_add
や date_sub
による演算に対して発生しているので sql_mode
は関係なく発生するようです。