MySQLでの履歴テーブルの作成

概要

「テーブルの更新履歴を残したい」という要件があり、色々調査して「更新日時+元のテーブルのPKを、PKとした履歴テーブルの作成」をいう考えに至りました。以下に作り方を記載します。

作り方

前提

• 商品を記録するためのテーブルmaster_itemと、その変更履歴を保存するmaster_item_historyを作成します。
• master_itemのINSERT/UPDATE/DELETEは全て、master_item_historyにトリガーで記録されるものとします。
• したがって、master_itemは常に最新の商品データのみ、master_item_historyは最新データを含む全ての履歴を持っていることになります。

テーブル定義

master_item

• 簡単のため、id, name（商品名）, created_time（作成日時）, updated_time（更新日時）の4列のみを持つこととします。
• idはAUTO_INCREMENTで、created_timeとupdated_timeは自動的に更新されるようにします。

CREATE TABLE master_item(
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64),
    created_time DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) NOT NULL,
    updated_time DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) NOT NULL ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY(id));

master_item_history

• master_itemの履歴を入れるためのテーブルのため、列は似ていますが、色々変更しています。
• 特に重要なのは、(1) PKがupdated_timeとidであること、また、(2) トリガーで値をmaster_itemからコピーするため、master_item_historyではデフォルト値が不要になっていることです。

CREATE TABLE master_item_history(
    id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(64),
    created_time DATETIME(6),
    updated_time DATETIME(6) NOT NULL,
    PRIMARY KEY(updated_time, id));

トリガー

具体的なクエリは以下に示しますが、コンセプトは次のようになっています。
INSERT&UPDATE: master_itemに入ったデータを、そのままmaster_item_historyに入れれば良いです。
DELETE: master_itemからデータが消えるので、いつどのデータが消えたか分かるように、updated_timeとidだけmaster_item_historyに挿入します。

CREATE TRIGGER master_item_insert AFTER INSERT
ON master_item FOR EACH ROW
INSERT INTO master_item_history(id, name, created_time, updated_time)
    VALUES(NEW.id, NEW.name, NEW.created_time, NEW.updated_time);

CREATE TRIGGER master_item_update AFTER UPDATE
ON master_item FOR EACH ROW
INSERT INTO master_item_history(id, name, created_time, updated_time)
    VALUES(NEW.id, NEW.name, NEW.created_time, NEW.updated_time);

CREATE TRIGGER master_item_delete AFTER DELETE
ON master_item FOR EACH ROW
INSERT INTO master_item_history(id, updated_time)
    VALUES(OLD.id, CURRENT_TIMESTAMP(6));

試してみる

データを入れる

以下を実施すると、master_itemとmaster_item_historyに期待した通りにデータが入っていることが確認できます。

INSERT master_item(name) VALUES ('test1');
INSERT master_item(name) VALUES ('test2');
UPDATE master_item SET name = 'test22' WHERE id = 2;
INSERT master_item(name) VALUES ('test3');
INSERT master_item(name) VALUES ('test4');
INSERT master_item(name) VALUES ('test5');
INSERT master_item(name) VALUES ('test5'); # 間違えて'test5'を2回実行した（2回目のidは6）
DELETE FROM master_item WHERE id = 6; # なのでid=6を削除

master_itemのデータ

SELECT * FROM master_item;

id  name    created_time                updated_time
1	test1	2024-06-01 22:30:30.143317	2024-06-01 22:30:30.143317
2	test22	2024-06-01 22:31:33.579967	2024-06-01 22:31:53.998708
3	test3	2024-06-01 22:32:21.492624	2024-06-01 22:32:21.492624
4	test4	2024-06-01 22:32:31.015380	2024-06-01 22:32:31.015380
5	test5	2024-06-01 22:32:41.612827	2024-06-01 22:32:41.612827

master_item_historyのデータ

SELECT * FROM master_item_history;

id  name    created_time                updated_time
1	test1	2024-06-01 22:30:30.143317	2024-06-01 22:30:30.143317
2	test2	2024-06-01 22:31:33.579967	2024-06-01 22:31:33.579967
2	test22	2024-06-01 22:31:33.579967	2024-06-01 22:31:53.998708
3	test3	2024-06-01 22:32:21.492624	2024-06-01 22:32:21.492624
4	test4	2024-06-01 22:32:31.015380	2024-06-01 22:32:31.015380
5	test5	2024-06-01 22:32:41.612827	2024-06-01 22:32:41.612827
6	test5	2024-06-01 22:32:42.920684	2024-06-01 22:32:42.920684
6			                            2024-06-01 22:33:45.563663

ある時点でのmaster_itemを復元する

これは更新時間のWHERE条件をつけてmaster_item_historyを取得すれば可能です。例えば'2024-06-01 22:32:30'時点でのmaster_itemを復元するには、以下のようなSELECTを実行します。（とりあえず2つ作成したけど、どっちの方が良いのか・・・誰か教えてください・・・）
これらのクエリは時間を引数としてストアドプロシージャなどに入れておいて、簡単に使えるようにすることを想定しています。そのため、master_itemの列追加などに影響を受けないよう、SELECTの列指定には*を使っています。（と理想を言ってもトリガーは全部作り直しなため結構面倒ですが）

SELECT * 
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_time DESC) AS `row_number`
    FROM master_item_history
    WHERE updated_time <= '2024-06-01 22:32:30') mih
WHERE mih.`row_number` = 1;

id  name    created_time                updated_time                row_number
1	test1	2024-06-01 22:30:30.143317	2024-06-01 22:30:30.143317	1
2	test22	2024-06-01 22:31:33.579967	2024-06-01 22:31:53.998708	1
3	test3	2024-06-01 22:32:21.492624	2024-06-01 22:32:21.492624	1

SELECT mih.*
FROM master_item_history mih
INNER JOIN (
	SELECT max(updated_time) AS updated_time , id
	FROM master_item_history 
	WHERE updated_time <= '2024-06-01 22:32:30'
	GROUP BY id) mih_mid
	USING (updated_time, id);

id  name    created_time                updated_time 
1	test1	2024-06-01 22:30:30.143317	2024-06-01 22:30:30.143317
2	test22	2024-06-01 22:31:33.579967	2024-06-01 22:31:53.998708
3	test3	2024-06-01 22:32:21.492624	2024-06-01 22:32:21.492624

最後に

私自身、これから実システムに導入したいという段階なので、もし気になる点などあれば、コメントもらえると助かります！

参考