[MySql] 登録日時の初期値と更新日時の自動更新

2011-12-04

仕事では滅多に使わないが、現在 MySql を使ったプログラムを作ろうとしています。

MySql はなかなか癖のある方言を持っていて CREATE TABLE 文から苦戦しました。
phpMyAdmin を使えば簡単なのかもしれませんが、あえて SQL でテーブルを作成することにしました。

この記事でマスターしたいことは 登録日時と更新日時の自動更新 です。

まず、調べてわかったのは

  • 現在日時をデフォルト値に設定できるのは「current_timestamp」である。
  • 「current_timestamp」は、timestamp型のデフォルト値に指定できる。
  • 「current_timestamp」は、datetime型のデフォルト値には指定できない。
  • 「on update」を用いると、自動更新のデフォルト値を指定できる。

ということなので、次のように書いてみました。

created timestamp not null default current_timestamp,
modified timestamp not null on update current_timestamp default current_timestamp

しかし、実行結果はエラー。

#1293 – Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

つまりは、INSERT 時のデフォルト値だろうが UPDATE 時のデフォルト値だろうが、もしくは両方だろうが、current_timestamp を設定できるのは１カラムだけ、ということらしいのです。

同じオープンソースデータベースである PostgreSQL なら当たり前にできることなので作成者の怠慢にも思えてしまいますが、MySQLの仕様が関係して大変なことなのかもしれません。

対策は、とりあえず２つ。

  1. 登録日時のデフォルト値を0（Nullと同じらしい）にし、INSERT の時は手動で指定する。
  2. INSERT の時や UPDATE の時に動作するトリガーを用意して、登録日時や更新日時を設定する。

目的からすれば後者ですが、ソースの管理が面倒になるので、忘れて前者にすべきでしょう。
前者なら INSERT の時に登録日時の設定を忘れなければ OK で、更新日時は INSERT の時も UPDATE の時も自動設定されます。
（登録日時の DEFAULT 値を外すと CREATE 文が成立しないようです。）

ということで、以下で落ち着きました。

created timestamp not null default 0,
modified timestamp not null on update current_timestamp default current_timestamp

MySQL は癖が強すぎるから、ちゃんと勉強しないと駄目かも。。。

コメント