2009/03/04(水)mysqlで自動更新されるtimestampをあえて更新しない

tech
世間でも言われていますが、mysqlのtimestamp型はいろいろバッドノウハウの固まりではないかと思います。
最近はできるだけdatetime型にするようにしているのですが、すでにtimestamp型依存で動いているコードがある場合、alter tableするのも難しかったりします。

10.3.1. DATETIME、DATE、そして TIMESTAMP タイプや10.3.1.1. TIMESTAMP MySQL 4.1での性質 (いずれもMySQLマニュアル)にも諸々書いてありますが、気になるポイントは以下のあたり。
  • 各テーブルの最初に現れるtimestamp型カラムは、明示的に更新をしていないとUPDATE, REPLACEで現在時刻に自動更新される
  • 各テーブルの二つ目以降のtimestamp型カラムは自動更新されない
  • 扱える期間が1970年～2037年である (datetime型は1000年～9999年)
特に自動更新関係は、気がつかないうちにデータが書き換わってしまったり、思ったように書き換わらないことがあるので妙なバグを発生させやすいように思います。

で、今回はそんなtimestamp型で「自動更新されるはずのカラムをあえて更新しないでレコードをUPDATEする」方法について、少々。
こんなテーブルを仮定する。
mysql> desc test_update;
+-----------+-----------+------+-----+-------------------+-------+
| Field     | Type      | Null | Key | Default           | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| value     | text      | YES  |     | NULL              |       |
| timestamp | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-----------+-----------+------+-----+-------------------+-------+
2 rows in set (0.07 sec)
UPDATEすると、当然timestampカラムも更新される。
mysql> insert into test_update (value) values ("hogehoge");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_update;
+----------+---------------------+
| value    | timestamp           |
+----------+---------------------+
| hogehoge | 2009-03-04 22:44:56 |
+----------+---------------------+
1 row in set (0.02 sec)

mysql> update test_update set value = "fugafuga" where value = "hogehoge";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_update;
+----------+---------------------+
| value    | timestamp           |
+----------+---------------------+
| fugafuga | 2009-03-04 22:45:50 |
+----------+---------------------+
1 row in set (0.00 sec)
ここでtips。
UPDATEのSET句中では自分自身のカラムも指定できるので、こんな書き方もできる。
update test_update set value = "mogemoge", timestamp = timestamp where value = "fugafuga";
timstamp型は、明示的に値を与えられれば自動更新ではなく与えられた値に更新されるので、自分自身の今の値に更新してやればよい。
mysql> update test_update set value = "mogemoge", timestamp = timestamp where value = "fugafuga";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_update;
+----------+---------------------+
| value    | timestamp           |
+----------+---------------------+
| mogemoge | 2009-03-04 22:45:50 |
+----------+---------------------+
1 row in set (0.00 sec)
念のため、複数レコードある場合も、各レコード毎の「そのときの値」が採用されるので、期待通りに動作してくれる。