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_DATE
や NO_ZERO_IN_DATE
は知らなかった(-_-;)