MySQLで履歴テーブルを作る

そういう要件があったのでどうしたかのメモ
もっと良いやり方あるんだろうけど思いつかなかった。。

サンプル要件

1. 記事を管理する
2. 項目は記事の件名と本文
3. 記事の登録・変更・削除は全て履歴を残すこと

テーブル構成

記事を管理するarticleと履歴を管理するarticle_histroyの２つを用意します。

article

主キーはオートインクリメント
件名はVARCHAR、本文はTEXTです。

article_history

articleテーブルに以下のカラムを先頭につけました。
articleは物理削除するのでリレーションは貼りません。

• history_id 主キー(オートインクリメント)
• history_created レコード登録日時
• history_kind 操作種別(insert or update or delete)

トリガー構成

articleテーブルに以下の３つのトリガーを作成
INSERT UPDATE時はNEW.hogeで値を指定
DELETE 時は*OLD.hogeで値を指定します

AFTER INSERT

CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`article_AFTER_INSERT` AFTER INSERT ON `article` FOR EACH ROW
BEGIN
  INSERT INTO article_history(history_created, history_kind, article_id, title, body)
  VALUES (NOW(), 'insert', NEW.article_id, NEW.title, NEW.body);
END

AFTER UPDATE

CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`article_AFTER_UPDATE` AFTER UPDATE ON `article` FOR EACH ROW
BEGIN
  INSERT INTO article_history(history_created, history_kind, article_id, title, body)
  VALUES (NOW(), 'update', NEW.article_id, NEW.title, NEW.body);
END

AFTER DELETE

CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`article_AFTER_DELETE` AFTER DELETE ON `article` FOR EACH ROW
BEGIN
  INSERT INTO article_history(history_created, history_kind, article_id, title, body)
  VALUES (NOW(), 'delete', OLD.article_id, OLD.title, OLD.body);
END

動かしてみる

テストデータ投入

以下のSQLをどかっと流します。

INSERT INTO mydb.article(title, body)
VALUES ('タイトルです', '本文です');
INSERT INTO mydb.article(title, body)
VALUES ('title', 'body');

UPDATE mydb.article SET body = '本文を変更します'
WHERE article_id = 1;

DELETE FROM mydb.article
WHERE article_id = 1;

確認

SELECT * FROM mydb.article;

SELECT * FROM mydb.article_history;

history_idは連番、history_createdは操作日時、history_kindは操作した種別がきちんと入っていますね。

総括

この形で行うと既存テーブルや既存プログラムへの影響はない（と思う）のは良いのですが、誰が消したか等の操作情報が残せないです。残したい場合は操作ユーザーをカラムについかしてUPDATE→DELETEの２段階になるのかな。それはそれで格好悪いのでトリガーを使わずに手動で入れたほうがいいかもしれない。