Voici un moyen simple de le faire :
Tout d'abord, créez un tableau d'historique pour chaque tableau de données que vous souhaitez suivre (exemple de requête ci-dessous). Ce tableau contiendra une entrée pour chaque requête d'insertion, de mise à jour et de suppression effectuée sur chaque ligne de la table de données.
La structure de la table d'historique sera la même que celle de la table de données qu'elle suit, à l'exception de trois colonnes supplémentaires : une colonne pour stocker l'opération qui s'est produite (appelons-la "action"), la date et l'heure de l'opération, et une colonne pour stocker un numéro de séquence ("révision"), qui s'incrémente pour chaque opération et est groupé par la colonne clé primaire de la table de données.
Pour réaliser ce comportement de séquençage, un index (composite) à deux colonnes est créé sur la colonne de clé primaire et la colonne de révision. Notez que vous ne pouvez faire le séquençage de cette manière que si le moteur utilisé par la table historique est MyISAM ( Voir la section "Notes sur MyISAM" sur cette page.)
La table d'historique est assez facile à créer. Dans la requête ALTER TABLE ci-dessous (et dans les requêtes trigger qui suivent), remplacez 'primary_key_column' par le nom réel de cette colonne dans votre table de données.
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);
Et ensuite vous créez les déclencheurs :
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;
Et vous avez terminé. Maintenant, tous les inserts, mises à jour et suppressions dans 'MyDb.data' seront enregistrés dans 'MyDb.data_history', ce qui vous donne un tableau historique comme celui-ci (sans la colonne '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
Pour afficher les changements pour une ou plusieurs colonnes données d'une mise à jour à l'autre, vous devez joindre la table d'historique à elle-même sur la clé primaire et les colonnes de séquence. Vous pouvez créer une vue à cette fin, par exemple :
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
Edit : Oh wow, les gens aiment mon tableau d'histoire d'il y a 6 ans :P
Ma mise en œuvre de ce système est toujours en cours, et devient de plus en plus lourde, je suppose. J'ai écrit des vues et une interface utilisateur assez sympa pour consulter l'historique de cette base de données, mais je ne pense pas qu'elle ait été beaucoup utilisée. C'est ainsi.
Pour répondre à certains commentaires, sans ordre particulier :
-
J'ai réalisé ma propre implémentation en PHP, un peu plus complexe, et j'ai évité certains des problèmes décrits dans les commentaires (le transfert d'index, de manière significative). Si vous transférez des index uniques à la table d'historique, les choses vont se casser. Il y a des solutions pour cela dans les commentaires). Suivre ce post à la lettre pourrait être une aventure, en fonction du degré d'établissement de votre base de données.
-
Si la relation entre la clé primaire et la colonne de révision ne semble pas exister, cela signifie généralement que la clé composite est défectueuse. En de rares occasions, cela s'est produit et je n'en connaissais pas la cause.
-
J'ai trouvé cette solution assez performante, en utilisant des triggers comme elle le fait. De plus, MyISAM est rapide pour les insertions, ce qui est tout ce que font les triggers. Vous pouvez encore améliorer cela avec une indexation intelligente (ou l'absence d'indexation...). L'insertion d'une seule ligne dans une table MyISAM avec une clé primaire ne devrait pas être une opération que vous devez optimiser, vraiment, à moins que vous ayez des problèmes importants ailleurs. Pendant toute la période où j'ai exploité la base de données MySQL, l'implémentation de la table d'historique n'a jamais été la cause d'un des (nombreux) problèmes de performance qui se sont présentés.
-
si vous obtenez des insertions répétées, vérifiez votre couche logicielle pour les requêtes de type INSERT IGNORE. Hrmm, je ne me souviens plus maintenant, mais je pense qu'il y a des problèmes avec ce schéma et les transactions qui échouent finalement après l'exécution de multiples actions DML. Il faut en être conscient, en tout cas.
-
Il est important que les champs de la table d'historique et de la table de données correspondent. Ou, plutôt, que votre table de données n'ait pas PLUS de colonnes que la table d'historique. Sinon, les requêtes d'insertion/mise à jour/suppression sur la table de données échoueront, lorsque les insertions dans les tables d'historique mettront des colonnes dans la requête qui n'existent pas (à cause de d.* dans les requêtes de déclenchement), et le déclenchement échouera. Ce serait génial si MySQL avait quelque chose comme les déclencheurs de schéma, où vous pourriez modifier la table d'historique si des colonnes sont ajoutées à la table de données. Est-ce que MySQL a cela maintenant ? Je fais du React ces jours-ci :P
