MySQLにおいての更新時の差分ログについて

Question

phpMyAdminを通してSQL文を実行し一括置換を行おうと思っています。

その際、対象データ量が大量のため、
置換前と置換後の差分をログとして残したいと思っています。

また、本実行の前にテスト的にSQL文を流して（実際は更新しない）
結果を出力させる方法もあったらなと思います。

有効な参考ページや方法ございましたらご教授願えますでしょうか。
よろしくお願いします。

Answer 1

ログテーブルを定義しておき、
①変更前の内容をログテーブルに追加
②変更後の内容をログテーブルに追加
③変更後の内容を置換先に反映

実際に更新しない場合には③を実行しない。

ログテーブルを結果として書き込むのではなく、予定として書き込むのがミソです。

ログテーブルのレイアウトとしては、変更前後をカラムとして持つのでも良いですけど、
ステータス用のカラムを持たせ、別なレコードとして管理する方が処理ロジックは短くてすみます。
まあ、資料として整形された形でフォーマットしておくか、資料を出力する際に整形するかの違いが大きいところですけど、列方向に展開するより行方向に展開する方が、正規化の観点からはお薦めです。

Answer 2

ぱっと考えられるのは２点

• triggerで外部に履歴を残す

AFTER INPUT/AFTER UPDATEなどで別DBにデータを保持します
ただしこの運用だとテストには向いていないかもしれません

• トランザクションで処理

更新日時をtimestampなどで確保すれば
commit前に検証してrollbackすればいいかもしれません
ただしあまり大量のデータをトランザクションで保持するのはどうかとおもうので
毎回テストするような運用は避けたほうがいいかもしれません

sample trigger

テーブル作成

create table tblA(aid int not null primary key auto_increment,val1 int,val2 varchar(10));
create table tblB(bid int not null primary key auto_increment,aid int not null,val1 int,val2 varchar(10),tbla_modified datetime);

トリガー作成

drop trigger if exists trg_a ;
delimiter //
create trigger trg_a after update on tblA
for each row begin
insert into tblB(aid,val1,val2,tbla_modified) values(OLD.aid,OLD.val1,OLD.val2,NOW());
end;
//
delimiter ;

データ投入

insert into tblA(val1,val2) values(10,'test1'),(20,'test2'),(30,'test3'),(30,'test4');

※triggerはtblAのupdateを元にするのでinsert時にはtblBに影響はない
データ更新

update tblA set val2='test5' where val1=30;

※tblAの更新された行を元にtblBにtblAの古いデータが保持され、更新された時間が記載される

なので、tblAを元に戻したいのであれば更新日時をキーにtblBからtblAにデータを
流し込めばよい

Comments

[sk1013]

回答ありがとうございます。

・triggerで外部に履歴を残す
→別DBを用意するのは結構難しいと思います…できて別テーブルでしょうか。

・トランザクションで処理
→運用中の更新、かつ更新データが数万件あるためトランザクション管理はおっしゃる通り避けたいですね…

わがまますぎて、八方塞がりなのですが
データ内容的に誤るとサイトが飛んじゃうレベルなのでよりよい方法を模索したい次第です…

[yambejp]

> 別DBを用意するのは結構難しい

書き方が悪くてスミマセン、別テーブルで大丈夫です
trigger自体テーブルAが更新されるとテーブルBにデータが追加されるというような
ログのようなしくみです。

[sk1013]

トリガを使うとすると

本テーブル（UPDATE）→ログテーブル（INSERT）

という感じで本テーブルで更新が走ると、
ログテーブルには更新行のみ追加される、という認識です。

こちらも言い方が悪くて申し訳ないのですが、
ログというよりはテスト施行結果がほしいという感じですね。

更新自体でミスれないなので後からログを見直すというよりは
想定結果が知りたいという感じです。

[yambejp]

一応triggerのサンプルをつけておきました。

[sk1013]

ありがとうございますー。こちら参考にさせていただきますー。