Czy istnieje opcja/funkcja MySQL do śledzenia historii zmian w rekordach?

Oto prosty sposób na to:

Najpierw Utwórz tabelę historii dla każdej tabeli danych, którą chcesz śledzić(przykładowe zapytanie poniżej). Ta tabela będzie zawierać wpis dla każdego zapytania insert, update I delete wykonanego w każdym wierszu tabeli danych.

Struktura tabeli historii będzie taka sama jak tabeli danych, którą śledzi, z wyjątkiem trzech dodatkowych kolumn: kolumny do przechowywania operacji, która miała miejsce( nazwijmy ją "akcją"), daty i czasu operacji, i kolumnę do przechowywania numeru sekwencyjnego ('revision'), który zwiększa się na operację i jest pogrupowany według kolumny klucza głównego tabeli danych.

Aby wykonać takie sekwencjonowanie, tworzony jest dwukolumnowy (złożony) indeks na kolumnie klucza głównego i kolumnie rewizji. Zauważ, że sekwencjonowanie w ten sposób można wykonać tylko wtedy, gdy silnikiem używanym przez tabelę historii jest MyISAM (Zobacz "notatki MyISAM" na tej stronie)

Tabela historii jest dość łatwa do utworzenia. W ALTER Zapytanie tabeli poniżej (i w zapytaniach wyzwalających poniżej) zastąp "primary_key_column" rzeczywistą nazwą tej kolumny w tabeli danych.

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);

A następnie tworzysz wyzwalacze:

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;

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 

Aby wyświetlić zmiany dla danej kolumny lub kolumn z update do aktualizacja, musisz dołączyć tabelę historii do siebie w kolumnach klucza podstawowego i sekwencji. Możesz utworzyć w tym celu widok, na przykład:

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

Edytuj: O rany, ludzie lubią mój stół historyczny sprzed 6 lat: p

Moja implementacja wciąż nuci, robi się coraz większa i bardziej nieporęczna, zakładam. Napisałem widoki i całkiem ładny interfejs, aby spojrzeć na historię w tej bazie danych, ale nie sądzę, że kiedykolwiek był używany. Tak to wygląda.

Na adres niektóre komentarze w żadnej konkretnej kolejności:

• Zrobiłem własną implementację w PHP, która była trochę bardziej zaangażowana i uniknąłem niektórych problemów opisanych w komentarzach (o przeniesieniu indeksów, znacząco. Jeśli przeniesiesz unikalne indeksy do tabeli historii, wszystko się zepsuje. Są na to rozwiązania w komentarzach). Podążanie za tym postem do listu może być przygodą, w zależności od tego, jak powstała twoja baza danych.

• Jeśli związek między kluczem głównym a kolumną rewizji wydaje się być wyłączony zazwyczaj oznacza to, że klucz złożony jest jakoś borowany. W kilku rzadkich przypadkach miałem to się stało i byłem w stratę do przyczyny.

• Okazało się, że to rozwiązanie jest dość wydajne, używając wyzwalaczy, jak to robi. Ponadto MyISAM jest szybki w wstawkach, co jest wszystkie wyzwalacze zrobić. Możesz to jeszcze poprawić dzięki inteligentnemu indeksowaniu (lub braku...). Wstawianie pojedynczego wiersza do tabeli MyISAM za pomocą klucza podstawowego nie powinno być operacja trzeba zoptymalizować, naprawdę, chyba że masz poważne problemy dzieje się gdzie indziej. Przez cały czas pracy z bazą danych MySQL Ta implementacja tabeli historii była włączona, nigdy nie była przyczyną żadnego z (wielu) problemów z wydajnością, które pojawiły się.

• Jeśli otrzymujesz powtarzające się wstawki, sprawdź w warstwie oprogramowania, czy nie ma zapytań typu INSERT IGNORE. Hrmm, nie pamiętam teraz, ale myślę, że są problemy z tym schematem i transakcjami, które ostatecznie fail po uruchomieniu wielu akcji DML. Przynajmniej coś, o czym warto wiedzieć.

• Ważne jest, aby pola w tabeli historii i tabeli danych były zgodne. Albo raczej, że Twoja Tabela danych nie ma więcej kolumn niż tabela historii. W przeciwnym razie zapytania insert/update / del w tabeli danych nie powiodą się, gdy wstawki do tabel historii umieszczą w zapytaniu kolumny, które nie istnieją (z powodu d.* w zapytaniach wyzwalających), a WYZWALACZ się nie powiedzie. t byłoby super gdyby MySQL miał coś w rodzaju scheme-triggers, gdzie można zmienić tabelę historii, jeśli kolumny zostały dodane do tabeli danych. Czy MySQL ma to teraz? Ostatnio reaguję: P

Możesz stworzyć wyzwalacze, aby to rozwiązać. Oto samouczek (zarchiwizowany link).

Ustawianie ograniczeń i reguł w bazie danych jest lepsze niż pisanie specjalny kod do obsługi tego samego zadania, ponieważ uniemożliwi to kolejne programisty od napisania innego zapytania, które omija wszystkie specjalny kod i może opuścić bazę danych ze słabą integralnością danych.

Przez długi czas kopiowałem info do innej tabeli za pomocą skryptu od MySQL nie obsługiwał wtedy wyzwalaczy. Znalazłem to. WYZWALACZ, aby być bardziej skutecznym w śledzeniu wszystkiego.

Ten WYZWALACZ skopiuje starą wartość do tabeli historii, jeśli zostanie zmieniona kiedy ktoś edytuje wiersz. Editor ID i last mod są przechowywane w oryginalna tabela za każdym razem, gdy ktoś edytuje ten wiersz; czas odpowiada do czasu zmiany jej obecnej formy.

DROP TRIGGER IF EXISTS history_trigger $$

CREATE TRIGGER history_trigger
BEFORE UPDATE ON clients
    FOR EACH ROW
    BEGIN
        IF OLD.first_name != NEW.first_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'first_name',
                        NEW.first_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;

        IF OLD.last_name != NEW.last_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'last_name',
                        NEW.last_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;

    END;
$$

Innym rozwiązaniem byłoby zachowanie pola rewizji i zaktualizowanie tego pola w save. Możesz zdecydować, że max to najnowsza wersja, lub że 0 to najnowszy wiersz. To zależy od Ciebie.

Oto Jak to rozwiązaliśmy

Tabela użytkowników wyglądała tak

Users
-------------------------------------------------
id | name | address | phone | email | created_on | updated_on

I wymagania biznesowe zmieniły się i byliśmy w potrzebie, aby sprawdzić wszystkie poprzednie adresy i numery telefonów, które użytkownik kiedykolwiek miał. nowy schemat wygląda tak

Users (the data that won't change over time)
-------------
id | name

UserData (the data that can change over time and needs to be tracked)
-------------------------------------------------
id | id_user | revision | city | address | phone | email | created_on
 1 |   1     |    0     | NY   | lake st | 9809  | @long | 2015-10-24 10:24:20
 2 |   1     |    2     | Tokyo| lake st | 9809  | @long | 2015-10-24 10:24:20
 3 |   1     |    3     | Sdny | lake st | 9809  | @long | 2015-10-24 10:24:20
 4 |   2     |    0     | Ankr | lake st | 9809  | @long | 2015-10-24 10:24:20
 5 |   2     |    1     | Lond | lake st | 9809  | @long | 2015-10-24 10:24:20

Aby znaleźć aktualny adres dowolnego użytkownika, wyszukujemy dane Użytkownika z revision DESC i LIMIT 1

Aby uzyskać adres użytkownika w określonym czasie możemy użyć created_on bewteen (date1 , date 2)

MariaDB obsługuje wersjonowanie systemu od wersji 10.3, która jest standardową funkcją SQL, która robi dokładnie to, co chcesz: przechowuje historię rekordów tabeli i zapewnia dostęp do niej za pomocą zapytań SELECT. MariaDB jest open-development fork MySQL. Więcej informacji na temat wersji systemu można znaleźć pod tym linkiem:

Https://mariadb.com/kb/en/library/system-versioned-tables/

Tylko moje 2 centy. Stworzyłbym rozwiązanie, które dokładnie rejestruje to, co się zmieniło, bardzo podobne do rozwiązania transiena.

Mój ChangesTable byłby prosty:

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

1) gdy cały wiersz zostanie zmieniony w głównej tabeli, wiele wpisów trafi do tej tabeli, ale jest to bardzo mało prawdopodobne, więc nie jest to duży problem (ludzie zwykle zmieniają tylko jedną rzecz) 2) OldVaue (i NewValue jeśli chcesz) muszą być jakimś epickim "anytype", ponieważ mogą to być dowolne dane, mogą można to zrobić z typami surowymi lub po prostu za pomocą ciągów JSON do konwersji w I Na Zewnątrz.

Minimalne zużycie danych, przechowuje wszystko, czego potrzebujesz i może być używany dla wszystkich tabel jednocześnie. Właśnie to badam, ale to może skończyć się moją drogą.

Do tworzenia i usuwania, tylko identyfikator wiersza, bez pól. Po usunięciu flagi na głównej tabeli (aktywna?) byłoby dobrze.

Dlaczego po prostu nie użyć bin log files? Jeśli replikacja jest ustawiona na serwerze Mysql, a format pliku binlog jest ustawiony na ROW, wtedy wszystkie zmiany mogą zostać przechwycone.

Można użyć dobrej biblioteki Pythona o nazwie noplay. Więcej informacji TUTAJ .

Bezpośrednim sposobem na to jest tworzenie wyzwalaczy na tabelach. Ustaw kilka warunków lub metod mapowania. Gdy nastąpi aktualizacja lub usunięcie, automatycznie wstawia się do tabeli "Zmień".

Ale najważniejsze jest to, co jeśli mamy dużo kolumn i dużo tabeli. Musimy wpisać nazwę każdej kolumny każdej tabeli. Oczywiście, to strata czasu.

Aby radzić sobie z tym lepiej, możemy utworzyć kilka procedur lub funkcji do pobierania nazw kolumn.

Możemy również użyć Narzędzie 3rd-część po prostu to zrobić. Tutaj piszę program java Mysql Tracker