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

MySQLで日付型の列に行の作成日と更新日を自動で入れる方法を調査した。 以前使った、SQL Anywhereの場合だと「行の作成日と更新日を自動でつける」で書いたように行の作成日時、更新日時を列のデフォルト値とする構文があった。

MySQLでも同様のデフォルト値CURRENT_TIMESTAMPはあるようだがTimestamp型にのみ指定できるようである。 記法は以下2通りある。

DEFAULT CURRENT_TIMESTAMP

行が挿入された時刻

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

行が挿入または更新された時刻

行が挿入された時刻をデフォルト値とする

DEFAULT CURRENT_TIMESTAMPを使う。

CREATE TABLE t1 (
  rid INTEGER,
  txt VARCHAR(20),
  ts1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

このようなテーブルに対して以下の行を挿入すると挿入時刻が自動で入る。

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

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

mysql> SELECT * FROM t1;
+------+------+---------------------+
| rid  | txt  | ts1                 |
+------+------+---------------------+
|    1 | aaa  | 2013-04-23 13:26:52 |
|    2 | bbb  | 2013-04-23 13:26:58 |
+------+------+---------------------+
2 rows in set (0.00 sec)

行の更新では変化しない。

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

mysql> SELECT * FROM t1;
+------+------+---------------------+
| rid  | txt  | ts1                 |
+------+------+---------------------+
|    1 | zzz  | 2013-04-23 13:26:52 |
|    2 | bbb  | 2013-04-23 13:26:58 |
+------+------+---------------------+
2 rows in set (0.00 sec)

行が挿入または更新された時刻をデフォルト値とする

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPを使う。

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

このようなテーブルに対して以下の行を挿入すると挿入時刻が自動で入る。

mysql>> INSERT INTO t2 (rid,txt) VALUES(1,'aaa');
Query OK, 1 row affected (0.11 sec)

mysql>> INSERT INTO t2 (rid,txt) VALUES(2,'bbb');
Query OK, 1 row affected (0.12 sec)

mysql>> SELECT * FROM t2;
+------+------+---------------------+
| rid  | txt  | ts1                 |
+------+------+---------------------+
|    1 | aaa  | 2013-04-23 13:27:09 |
|    2 | bbb  | 2013-04-23 13:27:12 |
+------+------+---------------------+
2 rows in set (0.00 sec)

行を更新すると自動で書き換わる。

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

mysql> SELECT * FROM t2;
+------+------+---------------------+
| rid  | txt  | ts1                 |
+------+------+---------------------+
|    1 | zzz  | 2013-04-23 13:28:00 |
|    2 | bbb  | 2013-04-23 13:27:12 |
+------+------+---------------------+
2 rows in set (0.00 sec)

1つのテーブルにDEFAULT CURRENT_TIMESTAMPとDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPを両方定義しようとすると"Incorrect table definition"のエラーになってしまう。 両方同時に使えると作成時刻と更新時刻の両方が管理でき便利なのだが残念。 ただし、DDLとINSERTのSQLを少し工夫することで同等のことができるようだ。 その方法は「その２」のほうに書いてみた。

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

詳しくはリファレンスに書いてある。