Oto prosty sposób, aby to zrobić:
Najpierw utwórz tabelę historii dla każdej tabeli danych, którą chcesz śledzić (przykładowe zapytanie poniżej). Ta tabela będzie miała wpis dla każdego zapytania wstawiania, aktualizowania i usuwania wykonanego w każdym wierszu tabeli danych.
Struktura tabeli historii będzie taka sama, jak tabela danych, którą śledzi, z wyjątkiem trzech dodatkowych kolumn: kolumny do przechowywania operacji, która miała miejsce (nazwijmy to „akcją”), daty i godziny operacji oraz kolumny do przechowywania numeru kolejnego („poprawka”), który zwiększa się dla każdej operacji i jest grupowany według kolumny klucza podstawowego tabeli danych.
Aby wykonać to zachowanie sekwencjonowania, tworzony jest indeks dwukolumnowy (złożony) w kolumnie klucza podstawowego i kolumnie zmiany. Zauważ, że możesz wykonywać sekwencjonowanie w ten sposób tylko wtedy, gdy silnik używany przez tabelę historii to MyISAM ( zobacz „MyISAM Notes” na tej stronie)
Tabelę historii można dość łatwo utworzyć. W zapytaniu ALTER TABLE poniżej (oraz w zapytaniach wyzwalających poniżej) zamień „primary_key_column” na 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);
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;
I jesteś skończony. Teraz wszystkie wstawienia, aktualizacje i usunięcia w „MyDb.data” zostaną zapisane w „MyDb.data_history”, co daje taką tabelę historii (bez wymyślonej kolumny „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
Aby wyświetlić zmiany dla danej kolumny lub kolumn od aktualizacji do aktualizacji, musisz dołączyć do siebie tabelę historii na kluczu podstawowym i kolumnach sekwencji. W tym celu możesz utworzyć 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
Edycja: O wow, ludzie lubią moją tabelę historii sprzed 6 lat: P
Moja realizacja wciąż nuci, jak sądzę, staje się większa i bardziej nieporęczna. Napisałem widoki i całkiem niezły interfejs użytkownika, aby spojrzeć na historię w tej bazie danych, ale nie sądzę, aby była kiedykolwiek używana zbyt często. Tak to idzie.
Aby odnieść się do niektórych komentarzy w przypadkowej kolejności:
Zrobiłem własną implementację w PHP, która była nieco bardziej zaangażowana i uniknąłem niektórych problemów opisanych w komentarzach (co ważne, przenosząc indeksy. Jeśli przeniesiesz unikalne indeksy do tabeli historii, wszystko się zepsuje. Istnieją rozwiązania dla to w komentarzach). Podążanie za tym postem do listu może być przygodą, w zależności od tego, jak ugruntowana jest Twoja baza danych.
Jeśli relacja między kluczem podstawowym a kolumną wersji wydaje się być wyłączona, zwykle oznacza to, że klucz złożony został w jakiś sposób zepsuty. W kilku rzadkich przypadkach zdarzyło mi się to i nie wiedziałem, co jest przyczyną.
Okazało się, że to rozwiązanie jest dość wydajne, używając wyzwalaczy. Ponadto MyISAM jest szybki we wkładkach, co jest wszystkim, co robią wyzwalacze. Możesz to jeszcze bardziej poprawić dzięki inteligentnemu indeksowaniu (lub jego braku ...). Wstawienie pojedynczego wiersza do tabeli MyISAM z kluczem podstawowym nie powinno być operacją, którą musisz zoptymalizować, tak naprawdę, chyba że masz poważne problemy w innym miejscu. Przez cały czas, gdy korzystałem z bazy danych MySQL, ta implementacja tabeli historii była włączona, nigdy nie było to przyczyną żadnego z (wielu) problemów z wydajnością, które się pojawiły.
jeśli otrzymujesz powtarzające się wstawienia, sprawdź, czy w warstwie oprogramowania 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 kończą się niepowodzeniem po uruchomieniu wielu akcji DML. Przynajmniej coś, na co należy zwrócić uwagę.
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 zakończą się niepowodzeniem, gdy operacje wstawiania do tabel historii wstawią kolumny w zapytaniu, które nie istnieją (z powodu d. * W zapytaniach wyzwalających), a wyzwalacz nie powiedzie się. Byłoby wspaniale, gdyby MySQL miał coś w rodzaju wyzwalaczy schematu, w którym można by zmienić tabelę historii, gdyby kolumny zostały dodane do tabeli danych. Czy MySQL ma to teraz? Obecnie Reaguję: P.