Existe-t-il une option/caractéristique MySQL permettant de suivre l'historique des modifications apportées aux enregistrements ?

Question

On m'a demandé si je pouvais garder la trace des modifications apportées aux enregistrements dans une base de données MySQL. Ainsi, lorsqu'un champ a été modifié, l'ancien et le nouveau sont disponibles, ainsi que la date à laquelle cela s'est produit. Existe-t-il une fonctionnalité ou une technique commune pour faire cela ?

Si c'est le cas, je pensais faire quelque chose comme ça. Créer une table appelée changes . Il contiendrait les mêmes champs que le maître mais préfixée par les termes "ancien" et "nouveau", mais uniquement pour les champs qui ont été effectivement modifiés et un TIMESTAMP pour ça. Il serait indexé avec un ID . De cette façon, un SELECT pourrait être exécuté pour montrer l'historique de chaque enregistrement. Est-ce une bonne méthode ? Merci !

Answer 1

Pourquoi ne pas simplement utiliser les fichiers journaux des poubelles ? Si la réplication est définie sur le serveur Mysql et que le format du fichier binlog est défini sur ROW, toutes les modifications peuvent être capturées.

Une bonne bibliothèque python appelée noplay peut être utilisée. Plus d'infos aquí .

Answer 2

Juste mes deux centimes. Je créerais une solution qui enregistre exactement ce qui a changé, très similaire à la solution de Transient.

Mon ChangesTable serait simple :

DateTime | WhoChanged | TableName | Action | ID |FieldName | OldValue

1) Quand une ligne entière est modifiée dans la table principale, beaucoup d'entrées vont aller dans cette table, MAIS c'est très peu probable, donc pas un gros problème (les gens ne changent généralement qu'une seule chose). 2) OldVaue (et NewValue si vous voulez) doivent être une sorte de "anytype" épique puisque cela pourrait être n'importe quelle donnée, il pourrait y avoir un moyen de faire cela avec des types RAW ou simplement en utilisant des chaînes JSON pour convertir les entrées et sorties.

Utilisation minimale des données, stocke tout ce dont vous avez besoin et peut être utilisé pour toutes les tables à la fois. Je suis en train de faire des recherches sur le sujet, mais il se pourrait que je choisisse cette solution.

Pour la création et la suppression, il suffit de l'ID de la ligne, aucun champ n'est nécessaire. Pour la suppression, un indicateur sur la table principale (actif ?) serait bien.

Answer 3

Le moyen direct de le faire est de créer des déclencheurs sur les tables. Définissez certaines conditions ou méthodes de mappage. Lorsqu'une mise à jour ou une suppression se produit, elle est automatiquement insérée dans la table 'change'.

Mais la partie la plus importante est ce qui se passe si nous avons beaucoup de colonnes et beaucoup de tables. Nous devons taper le nom de chaque colonne de chaque table. Évidemment, c'est une perte de temps.

Pour gérer cela de manière plus splendide, nous pouvons créer des procédures ou des fonctions pour récupérer le nom des colonnes.

Nous pouvons également utiliser l'outil 3rd-part simplement pour faire cela. Ici, j'écris un programme java Mysql Tracker

Answer 4

Dans MariaDB 10.5+, la configuration est aussi simple que

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
  PARTITION BY SYSTEM_TIME;

L'historique peut alors être interrogé en faisant

SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP '2016-10-09 08:07:06';

Il n'y a actuellement aucun équivalent pour cela dans MySQL.

Voir la documentation pour plus d'informations. Si vous utilisez une version antérieure de MariaDB, la documentation propose une autre syntaxe disponible depuis MariaDB 10.3.4.