HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

トリガーを駆使して登録日時と更新日時を両方管理する

MySQL

駆使して、ってほど複雑なことは全然ないんだけど、たまたまそんなトリガーを書いたのでメモ。
以前に、MySQLで自動更新のTIMESTAMP型の列を2つ以上定義できないということを書いたこともついでに思い出した(謎)。

とりあえず、どちらかを「DEFAULT 0」とか「DEFAULT '0000-00-00 00:00:00'」とかしておけばいいので、そんな感じで。
更新日時をトリガーで更新するケースと、登録日時をトリガーで更新するケースの2通りを作成するが、テーブル定義は次のとおり。

-- 更新日時をトリガーで更新する
CREATE TABLE hoge1(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL DEFAULT '',
    regist_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_date TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
);
-- 登録日時をトリガーで更新する
CREATE TABLE hoge2(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL DEFAULT '',
    regist_date TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    update_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

後者のupdate_date列は、長ったらしく書いてあるが、こうしないと自動更新されない。もっとも、

    update_date TIMESTAMP,
    regist_date TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'

の順で書けばいいだけの話なのだが、気持ち悪いので。
で、本題のトリガーの定義。

DELIMITER |

-- 更新日時を更新するためのトリガー。INSERT時とUPDATE時の2つ必要
CREATE TRIGGER test_hoge1i BEFORE INSERT ON hoge1
  FOR EACH ROW BEGIN
    SET NEW.update_date = now();
  END;
|
CREATE TRIGGER test_hoge1u BEFORE UPDATE ON hoge1
  FOR EACH ROW BEGIN
    SET NEW.update_date = now();
  END;
|

-- 登録日時を更新するためのトリガー
CREATE TRIGGER test_hoge2i BEFORE INSERT ON hoge2
  FOR EACH ROW BEGIN
    SET NEW.regist_date = now();
  END;
|

DELIMITER ;

MySQLでは、特に定義しなくても、更新後の各列の値を"NEW.列名"で参照できるので、それを使用する。
これを、以下の環境で実行する。(いずれもWindows Vista Business上)

テーブルとトリガーを定義したら、実際にINSERT、UPDATEをしてみる。
流すクエリは3つとも共通。

INSERT INTO hoge1(name) VALUES('hoge1');
INSERT INTO hoge2(name) VALUES('hoge2');

SELECT * FROM hoge1;
SELECT * FROM hoge2;

-- 少し時間をおいてから‥

UPDATE hoge1 SET name = 'foo1';
UPDATE hoge2 SET name = 'foo2';

SELECT * FROM hoge1;
SELECT * FROM hoge2;

実行結果は以下のような感じ。

mysql> INSERT INTO hoge1(name) VALUES('hoge1');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO hoge2(name) VALUES('hoge2');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM hoge1;
+----+-------+---------------------+---------------------+
| id | name  | regist_date         | update_date         |
+----+-------+---------------------+---------------------+
|  1 | hoge1 | 2012-04-17 00:16:29 | 2012-04-17 00:16:29 |
+----+-------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM hoge2;
+----+-------+---------------------+---------------------+
| id | name  | regist_date         | update_date         |
+----+-------+---------------------+---------------------+
|  1 | hoge2 | 2012-04-17 00:16:29 | 2012-04-17 00:16:29 |
+----+-------+---------------------+---------------------+
1 row in set (0.02 sec)

mysql> UPDATE hoge1 SET name = 'foo1';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE hoge2 SET name = 'foo2';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM hoge1;
+----+------+---------------------+---------------------+
| id | name | regist_date         | update_date         |
+----+------+---------------------+---------------------+
|  1 | foo1 | 2012-04-17 00:16:29 | 2012-04-17 00:17:06 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM hoge2;
+----+------+---------------------+---------------------+
| id | name | regist_date         | update_date         |
+----+------+---------------------+---------------------+
|  1 | foo2 | 2012-04-17 00:16:29 | 2012-04-17 00:17:07 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

まぁ、DBMSへの負荷を少しでも減らしたいなら、プログラム側で日時データを作って忘れずに指定するほうがいいんだろうけど。選択肢の一つってことで。