MySQLで差分を保存しそのデータも利用する方法

Question

###前提・実現したいこと
MySQLで、テーブルの内容が変更されるたびに、その差分を保存し、その履歴を閲覧できるようにしたいです。

例）
所属先が記録される社員名簿。
異動により内容が更新されると、差分を保存。
特定の社員のページに、入社以降のすべての所属先が一覧表示される。
（○年～○年 人事部、○年～○年 総務部 のような感じ。）

• cakePHPを使ったシステムで運用を考えています。
• 最新の情報は、全情報のcsvファイルを読み込む形でupdateしたいです。（難しければ、他の方法も検討可能です。）
• データは数万件です。

###考えたこと

• csvの読み込みは、LOAD DATA INFILEを使えばいいのではないか。
• 最新データ用、1つ前のデータ用、差分保存用の3テーブルを作ればいいのか？

くらいまでは、考えたのですが、暗中模索の状態です。

何か手がかりやヒントだけでもいただけないでしょうか。
よろしくお願いいたします。

Answer 1

差分生成、私も悩むことが多いのでクリップさせて頂きます。

さて、２つ提示します。

• 最新データ用テーブルにLOAD DATA INFILEを使用して取込後、元データ用テーブルとLEFT OUTRE JOINして全ての項目を比較する、という力技

• もう一つはちょっと手順が長いです。

1. 以下の様にCSVファイルに元データを出力する

SELECT * FROM 元データ用 INTO OUTFILE 'ファイルパス' FIELDS TERMINATED BY ',';
※出力時のソートキーは必ず最新CSVの出力ソートキーと同じにする
0. 出力されたファイルと最新CSVの差分をdiffコマンドで抽出・csv形式で保存し、
抽出したデータをLOAD DATA INFILEでTableBに取込ます。
0. TableAからデータをTruncateあるいはdeleteし、LOAD DATA INFILEで最新CSVをTableAに取込。

LOAD DATA INFILEを使用しなくても良いのであれば、
一番よくやるのは、CSV１行毎にMARGEクエリを発行し、update triggerを作成、
その中で差分データを生成、ですかね。

Answer 2

update triggerを設定して、差分をべつテーブルに保存していけばよいのでは？

Comments

[lg_rino]

ありがとうございます。LOAD DATA INFILEを使うと、replaceを指定したときも、insert triggerは使えるようですが、update triggerは使えないように思いましたが、何か良い方法はありますでしょうか？