MySQL 行の作成日と更新日を自動でつける その２

「MySQL 行の作成日と更新日を自動でつける」の記事で、 行の作成日と更新日を自動でつけるDDL（テーブル定義）の方法を紹介した。 この方法では自動でつけられるのは作成日と更新日どちらか1テーブルに1つの制約があった。

その後の調査で分かったのだが、MySQLではNOT NULLなTIMESTAMP型の列にNULLを書き込むと現在時刻に置き換えられるという仕様がありそれを利用することで、一応自動で付与するということもできるようだ。

以下のようなテーブル定義として、ts1に行の作成日時、ts2に行の更新日時を入れることとする。

CREATE TABLE t3 (
  rid INTEGER,
  txt VARCHAR(20),
  ts1 TIMESTAMP DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT時にts1にはNULL値を書き込むことで書き込みの発生した現在時刻が自動で入る。

mysql> INSERT INTO t3 (rid,txt,ts1) VALUES(1,'aaa',NULL);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO t3 (rid,txt,ts1) VALUES(2,'bbb',NULL);
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM t3;
+------+------+---------------------+---------------------+
| rid  | txt  | ts1                 | ts2                 |
+------+------+---------------------+---------------------+
|    1 | aaa  | 2013-04-23 16:21:44 | 2013-04-23 16:21:44 |
|    2 | bbb  | 2013-04-23 16:21:44 | 2013-04-23 16:21:44 |
+------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

UPDATE時にはts1には値を書き込まないようにする。このようにすることでts1の日時は更新されず、行が作成された日時を示すようになる。

mysql> UPDATE t3 SET txt='zzz' WHERE rid=1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t3;
+------+------+---------------------+---------------------+
| rid  | txt  | ts1                 | ts2                 |
+------+------+---------------------+---------------------+
|    1 | zzz  | 2013-04-23 16:21:44 | 2013-04-23 16:21:49 |
|    2 | bbb  | 2013-04-23 16:21:44 | 2013-04-23 16:21:44 |
+------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

まあ、でもINSERT時にNULLを指定して書き込まないといけないので、ここに現在時刻を指定するようにしてもプログラムの質としてはあまり変わらないような気がする。 むしろその方がトリッキーでなくわかりやすいかも。