MySQL の `NO_ZERO_DATE` と `NO_ZERO_IN_DATE` で無効な日時を禁止

MySQL は、次のような変な日時を DATETIME に挿入出来てしまいます。

create table tt (dd datetime);
insert into tt value (0);                     /* 0000-00-00 00:00:00 */
insert into tt value ('');                    /* 0000-00-00 00:00:00 */
insert into tt value ('9999-99-99 99:99:99'); /* 0000-00-00 00:00:00 */
insert into tt value ('2000-04-31 00:00:00'); /* 0000-00-00 00:00:00 */
insert into tt value ('2000-00-00 00:00:00'); /* 2000-00-00 00:00:00 */

あまり訓練されていない PHPer とかは '' とか 0 とか null をあんまり区別しないので、放置しておくと '0000-00-00 00:00:00' が挿入された行が大量生産されます。

SQL モードを設定して無効な日時を禁止

my.cnf の [mysqld] セクションに次のように追記して、SQL モードを指定します。

sql_mode = STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE

これで変な日時を挿入しようとするとエラーになります。

insert into tt value (0);
/* ERROR 1292 (22007): Incorrect datetime value: '0' for column 'dd' at row 1 */

指定している3つのモードの値には次の通りの効果があります。

  • STRICT_ALL_TABLES
    無効な値が指定されたときに 警告 ではなく エラー にします

  • NO_ZERO_DATE
    0'0000-00-00' を無効な日時として扱います

  • NO_ZERO_IN_DATE
    '2000-00-00' のような月や日に 0 がある日時を無効な日時として扱います

他にもモードに指定出来るものは色々あります、詳細はリファレンスマニュアルを参照。


Strict モードは知っていたのになぜか NO_ZERO_DATENO_ZERO_IN_DATE は知らなかった(-_-;)