MySQL TIMESTAMP型の NOT NULL制約に期待してはいけない

MySQL 5.1.67環境です。
タイトルの通りなのですが、MySQLマニュアルのTIMESTAMPの説明を読んでみると、
NOT NULL制約に関して気になる記述がありました。

MySQLマニュアルのTIMESTAMPの説明

MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 6.3.1.1 TIMESTAMP の特性によると、

TIMESTAMP カラムはデフォルトで NOT NULL になっているため、NULL 値を含めることはできません。NULL を指定すると、現在のタイプスタンプが割り当てられます。

だそうです。

さらっと書いてるけど、NOT NULL制約がエラーチェックに使えないってことじゃないか。
実際、 MySQLのTIMESTAMPカラムをNOT NULLで定義しても、NULLのINSERT/UPDATEを実行すると現在時刻になってしまいます。

これだと、NOT NULL制約を付けたカラムにNULLをINSERTするような異常事態が発生しても検出できません。システム運用開始後に、これが原因で長期間データ不整合に気づかないなんて事態になると目も当てられません。

TIMESTAMPの作り方についていろいろ検証

実際にテーブルをいろいろ作って試してみました。

NOT NULLのTIMESTAMPカラムを２つ作って、NULLをINSERTしてみる

当然INSERTエラーを期待するのですが。。。

mysql> CREATE TABLE test (
-> id int NOT NULL,
-> insert_time TIMESTAMP NOT NULL DEFAULT 0,
-> upddate_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO test VALUES(1, null, null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

+----+---------------------+---------------------+
| id | insert_time | upddate_time |
+----+---------------------+---------------------+
| 1 | 2013-03-13 15:44:18 | 2013-03-13 15:44:18 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

現在時刻が入ってる・・・。エラーにならへんがな。

TIMESTAMPカラムの順番を入れ替えてみる

１つ目のTIMESTAMPカラムは、常に現在時刻が入ってしまうような気がしたので、TIMESTAMPカラムの順番を入れ替えてみる。

mysql> CREATE TABLE test2 (
-> id int NOT NULL,
-> upddate_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> insert_time TIMESTAMP NOT NULL DEFAULT 0
-> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO test2 VALUES(1, null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+----+---------------------+---------------------+
| id | upddate_time | insert_time |
+----+---------------------+---------------------+
| 1 | 2013-03-13 15:45:35 | 2013-03-13 15:45:35 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

やっぱ現在時刻が入りますか。

NOT NULL制約を外してみる

NOT NULLだから現在時刻が入ってしまうのでは！ということでNOT NULL制約を外してみる。

mysql> CREATE TABLE test3 (
-> id int NOT NULL,
-> upddate_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> insert_time TIMESTAMP
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test3 VALUES(1, null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;
+----+---------------------+---------------------+
| id | upddate_time | insert_time |
+----+---------------------+---------------------+
| 1 | 2013-03-13 15:46:02 | 2013-03-13 15:46:02 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

それでも現在時刻になるんかい！

NULLを入れられるようにしてみる

本題の解決にはなってませんが、NULLを許可する書き方があるのでそれを試してみる。

mysql> CREATE TABLE test4 (
-> id int NOT NULL,
-> upddate_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> insert_time TIMESTAMP NULL DEFAULT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test4 VALUES(1, null, null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test4;
+----+---------------------+-------------+
| id | upddate_time | insert_time |
+----+---------------------+-------------+
| 1 | 2013-03-13 15:48:31 | NULL |
+----+---------------------+-------------+
1 row in set (0.00 sec)

お～、NULLが入った。

感想

いくつか試してみましたが、結局NOT NULLカラムにNULLをINSERTしてもエラーは検知できない。

これ、MySQL慣れしてる人々の間では結構常識なんですね・・・。

TIMESTAMPは使いすぎないようにしよう。