読者です 読者をやめる 読者になる 読者になる

howdylikes

Google Developersってわかりづらいよね

MySQLで履歴テーブルを作る

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

サンプル要件

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

テーブル構成

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

article

主キーはオートインクリメント
件名はVARCHAR、本文はTEXTです。 f:id:howdy39:20151001083220p:plain

article_history

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

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

トリガー構成

articleテーブルに以下の3つのトリガーを作成
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;

f:id:howdy39:20151001082251p:plain

SELECT * FROM mydb.article_history;

f:id:howdy39:20151001082345p:plain

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

総括

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