レコードの変更履歴を追跡するMySQLオプション/機能はありますか? 質問する

Question

これを行う簡単な方法は次のとおりです。

まず、追跡するデータテーブルごとに履歴テーブルを作成します (以下のクエリ例を参照)。このテーブルには、データテーブルの各行に対して実行された挿入、更新、および削除の各クエリのエントリが含まれます。

履歴テーブルの構造は、発生した操作 (「アクション」と呼びます) を格納する列、操作の日時、および操作ごとに増加し、データテーブルの主キー列によってグループ化されるシーケンス番号 (「リビジョン」) を格納する列という 3 つの追加列を除いて、追跡するデータテーブルと同じです。

この順序付け動作を行うには、主キー列とリビジョン列に2列（複合）インデックスを作成します。履歴テーブルで使用されるエンジンがMyISAM（このページの「MyISAM ノート」を参照してください)

履歴テーブルの作成は非常に簡単です。以下の ALTER TABLE クエリ (およびその下のトリガークエリ) で、「primary_key_column」をデータテーブル内の実際の列名に置き換えます。

CREATE TABLE MyDB.data_history LIKE MyDB.data;

ALTER TABLE MyDB.data_history MODIFY COLUMN primary_key_column int(11) NOT NULL, 
   DROP PRIMARY KEY, ENGINE = MyISAM, ADD action VARCHAR(8) DEFAULT 'insert' FIRST, 
   ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER action,
   ADD dt_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER revision,
   ADD PRIMARY KEY (primary_key_column, revision);

次にトリガーを作成します。

DROP TRIGGER IF EXISTS MyDB.data__ai;
DROP TRIGGER IF EXISTS MyDB.data__au;
DROP TRIGGER IF EXISTS MyDB.data__bd;

CREATE TRIGGER MyDB.data__ai AFTER INSERT ON MyDB.data FOR EACH ROW
    INSERT INTO MyDB.data_history SELECT 'insert', NULL, NOW(), d.* 
    FROM MyDB.data AS d WHERE d.primary_key_column = NEW.primary_key_column;

CREATE TRIGGER MyDB.data__au AFTER UPDATE ON MyDB.data FOR EACH ROW
    INSERT INTO MyDB.data_history SELECT 'update', NULL, NOW(), d.*
    FROM MyDB.data AS d WHERE d.primary_key_column = NEW.primary_key_column;

CREATE TRIGGER MyDB.data__bd BEFORE DELETE ON MyDB.data FOR EACH ROW
    INSERT INTO MyDB.data_history SELECT 'delete', NULL, NOW(), d.* 
    FROM MyDB.data AS d WHERE d.primary_key_column = OLD.primary_key_column;

これで完了です。これで、「MyDb.data」のすべての挿入、更新、削除が「MyDb.data_history」に記録され、次のような履歴テーブルが作成されます (「data_columns」列は除きます)。

ID    revision   action    data columns..
1     1         'insert'   ....          initial entry for row where ID = 1
1     2         'update'   ....          changes made to row where ID = 1
2     1         'insert'   ....          initial entry, ID = 2
3     1         'insert'   ....          initial entry, ID = 3 
1     3         'update'   ....          more changes made to row where ID = 1
3     2         'update'   ....          changes made to row where ID = 3
2     2         'delete'   ....          deletion of row where ID = 2

更新ごとに特定の列または列の変更を表示するには、主キーとシーケンス列で履歴テーブルをそれ自体に結合する必要があります。この目的のために、たとえば次のビューを作成できます。

CREATE VIEW data_history_changes AS 
   SELECT t2.dt_datetime, t2.action, t1.primary_key_column as 'row id', 
   IF(t1.a_column = t2.a_column, t1.a_column, CONCAT(t1.a_column, " to ", t2.a_column)) as a_column
   FROM MyDB.data_history as t1 INNER join MyDB.data_history as t2 on t1.primary_key_column = t2.primary_key_column 
   WHERE (t1.revision = 1 AND t2.revision = 1) OR t2.revision = t1.revision+1
   ORDER BY t1.primary_key_column ASC, t2.revision ASC

Answer 1