[解決済み] レコードの変更履歴を追跡するMySQLのオプション/機能はありますか？

質問

MySQLデータベースのレコードの変更点を記録できないか、という質問を受けました。つまり、あるフィールドが変更されたとき、新旧の比較とその日付がわかるようにするのです。これを行うための機能または一般的なテクニックはあるのでしょうか？

もしそうなら、次のようなことをしようかと考えていました。というテーブルを作成します。 changes . と同じフィールドが含まれます。 マスター しかし、実際に変更されたフィールドにのみ、oldとnewの接頭辞が付き、その後に TIMESTAMP を使用します。インデックスが作成され ID . このように SELECT レポートを実行し、各レコードの履歴を表示することができます。これは良い方法なのでしょうか？ありがとうございます。

解決方法は？

微妙なところですね。

ビジネス要件が「誰がいつ何をしたのか」というデータの変更を監査したい場合、通常は監査テーブルを使用します（Keethanjanが投稿したトリガーの例と同様です）。私はトリガーの大ファンではありませんが、これは比較的簡単に実装できるという大きな利点があります - 既存のコードはトリガーや監査について知る必要はありません。

もしビジネス要件が「過去のある日のデータの状態を教えてください」であれば、それは経時変化という側面がソリューションに含まれることを意味します。監査テーブルを見るだけで、データベースの状態を再構築することは可能ですが、それは困難であり、エラーが発生しやすく、また複雑なデータベースロジックでは扱いにくくなります。例えば、「月の初めの日に未払いの請求書があった顧客に送るべき手紙の宛先を知りたい」と考えた場合、半ダースの監査テーブルを調べなければならない可能性があります。

その代わりに、スキーマ設計に経時変化の概念を組み込むことができます（これはKeethanjanが提案する2番目のオプションです）。これは、ビジネスロジックと永続性のレベルで、アプリケーションを変更することになるので、些細なことではありません。

例えば、こんなテーブルがあったとします。

CUSTOMER
---------
CUSTOMER_ID PK
CUSTOMER_NAME
CUSTOMER_ADDRESS

で、時系列で追跡したい場合は、次のように修正します。

CUSTOMER
------------
CUSTOMER_ID            PK
CUSTOMER_VALID_FROM    PK
CUSTOMER_VALID_UNTIL   PK
CUSTOMER_STATUS
CUSTOMER_USER
CUSTOMER_NAME
CUSTOMER_ADDRESS

顧客レコードを変更するたびに、レコードを更新する代わりに、現在のレコードのVALID_UNTILをNOW()に設定し、VALID_FROM（現在）およびNULL VALID_UNTILを持つ新しいレコードを挿入します。CUSTOMER_USER"ステータスを現在のユーザのログインIDに設定します(保持する必要がある場合)。顧客を削除する必要がある場合、CUSTOMER_STATUSフラグを使用してその旨を示します - このテーブルからレコードを削除することはできません。

そうすれば、ある日付の顧客テーブルの状態がどうであったか、つまり、住所はどうであったか、は常に知ることができます。住所はどうなっているのか、名前は変わっているのか？valid_fromとvalid_untilの日付が似ている他のテーブルと結合することで、全体像を歴史的に再構築することができるのです。現在の状態を調べるには、VALID_UNTILの日付がNULLであるレコードを検索します。

扱いにくい（厳密にはvalid_fromは必要ないが、クエリーが少し楽になる）。デザインもデータベースへのアクセスも複雑になります。しかし、世界の再構築をより簡単にすることができます。