0を含んだ日付とMySQLのSQLモード

こんにちは。hnwです。今回は前回記事「SQLのTIMESTAMP型と日付「0000-00-00」」の続きで、MySQLの日付について少し実験してみました。

MySQLとPHPにおける月日の0の意味の相違

前回記事でも実験しましたが、MySQLでは、DATETIME型の月と日にも0をセットできます。これは不明の意味で使う意図なのだと思われます。実際、月だけ不明という意味で「2000-00-01」などという日付にすることも可能です。

mysql> UPDATE sampletable SET d='2000-00-01';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
 
mysql> SELECT * FROM sampletable;
+---------------------+
| d                   |
+---------------------+
| 2000-00-01 00:00:00 |
+---------------------+
1 row IN SET (0.00 sec)
 
mysql>

前回記事と同じくMySQL 5.0.22での結果です。

一方PHPでは、日付を扱う関数（例えばmktimeやstrtotimeなど）は「0月」を指定されると前年の12月の意味で処理します。

$ php -r 'var_dump(date("Y-m-d", strtotime("2000-00-01")));'
string(10) "1999-12-01"

このように、「0月」の意味がDBとプログラミング言語とで異なる可能性があるわけです。きちんと理解した上であれば矛盾なくプログラムを書くことは十分可能ですけど、他人に引き継ぐ手間が増えそうですし、無用なトラブルの元になるかもしれません。組織内で共通認識があるなどでなければ、使わないのが無難だと私は考えます。

MySQLで月日の0を禁止する方法

私の記事を読んでMySQLで0月も0日も使う気が無くなった方や、そんな不思議な日付を利用できることに驚かれた方がいらっしゃるかもしれません。さらに一歩進んで、SQL文中で0を含んだ日付を利用したらエラーを出すような設定にしたくなった方もいるかと思います。その方法を紹介します。

方法としては、SQL_MODEの値を下記のように変更するだけです。

mysql> SET SQL_MODE='STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';
Query OK, 0 rows affected (0.00 sec)
 
mysql> UPDATE sampletable SET d='0000-00-00';
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' FOR COLUMN 'd' at row 1
mysql> UPDATE sampletable SET d='2000-00-01';
ERROR 1292 (22007): Incorrect datetime value: '2000-00-01' FOR COLUMN 'd' at row 1
mysql> UPDATE sampletable SET d='2000-01-01';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

上記の通り、0を含む日付にUPDATEしようとするとエラーが帰ってくるようになりました。この設定は下記のようにmy.cnfに書くこともできます。

[mysqld]
sql-mode="STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE"

ただし、これはMySQL 5.0.x以降の機能です。4.x以前ではSQL文で0月や0日を指定された場合にエラーを出す方法はありません。

また、my.cnfの設定はSET文で上書きできますので、この設定でも0を含んだ日付をINSERTまたはUPDATEすることは可能です。DB内に0を含んだ日付が無くなることを保証するものではなく、オペミス防止のための設定として捉えるのが良さそうです。

ありえない日付をINSERT/UPDATEしてみる

少し蛇足気味ですが、関連して面白いSQL_MODEを紹介します。

mysql> SET sql_mode='ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)
 
mysql> UPDATE IGNORE sampletable SET d='2000-02-31';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT * FROM sampletable;
+---------------------+
| d                   |
+---------------------+
| 2000-02-31 00:00:00 |
+---------------------+
1 row IN SET (0.00 sec)
 
mysql>

ALLOW_INVALID_DATESを有効にすると、datetime型のカラムを2月31日に更新できちゃいました。この機能って何に使うのかなあ？と思ったんですが、どうも後方互換性のためのモードのようですね。

どういうことかというと、そもそも4.x以前のMySQLでは存在しない日付をINSERTまたはUPDATEしても何も警告が出なかったようです。元々13月や32日は設定できませんが、月と日の組み合わせがあり得ないかどうかのチェックは5.0.xからサポートしたようです。

私はなかなか素敵な仕様だと感じましたけど、MySQLが長い人には常識なのかもしれません。

結論

前回記事と殆ど同じ結論になりますが、MySQLで年、月、日とも0を使うのは避けた方がいいと思います。一方で、デフォルト設定のMySQLでは0を含んだ日付をINSERTまたはUPDATEすることが可能です。また、MySQL 4.x以前では0月0日だけでなく2月31日などの日付をINSERTまたはUPDATEすることが可能であり、エラーとして停止する方法はありません。

Webアプリケーション開発の際、これが原因で小さなバグに繋がる可能性はあるかと思います。MySQLで開発する場合に、テストやデバッグで気をつけた方が良い値として0月0日や2月31日などがある、というのが今回得られた知見だと思います。