레코드 변경 내역을 추적하는 MySQL 옵션 / 기능이 있습니까?

레코드 변경 내역을 추적하는 MySQL 옵션 / 기능이 있습니까?

---

MySQL 데이터베이스의 레코드 변경 사항을 추적 할 수 있는지 물었습니다. 따라서 필드가 변경되면 이전과 새 항목을 사용할 수 있으며이 날짜가 발생했습니다. 이를 수행하는 기능이나 일반적인 기술이 있습니까?

그렇다면 나는 이와 같은 일을 할 생각이었다. 라는 테이블을 만듭니다 changes. 마스터 테이블 과 동일한 필드를 포함 하지만 이전 및 새 접두사가 붙지 만 실제로 변경된 필드와 해당 필드에만 해당됩니다 TIMESTAMP. 인덱싱됩니다 ID. 이렇게하면 SELECT각 레코드의 기록을 표시 하는 보고서를 실행할 수 있습니다. 이것이 좋은 방법입니까? 감사!

---

미묘합니다.

비즈니스 요구 사항이 "데이터 변경 사항을 감사하고 싶습니다-누가 언제 무엇을 했습니까?"인 경우 일반적으로 감사 테이블을 사용할 수 있습니다 (Keethanjan이 게시 한 트리거 예제에 따라). 저는 트리거를 좋아하지는 않지만 구현하는 데 비교적 고통스럽지 않다는 큰 이점이 있습니다. 기존 코드는 트리거 및 감사 항목에 대해 알 필요가 없습니다.

비즈니스 요구 사항이 "과거에 주어진 날짜의 데이터 상태 표시"인 경우 시간에 따른 변화의 측면이 솔루션에 들어왔다는 의미입니다. 감사 테이블을 보는 것만으로 데이터베이스의 상태를 재구성 할 수는 있지만 어렵고 오류가 발생하기 쉬우 며 복잡한 데이터베이스 논리의 경우 다루기가 어려워집니다. 예를 들어, 기업이 "매월 1 일에 미납 송장이있는 고객에게 보내야하는 편지의 주소를 찾으려면"6 개의 감사 테이블을 검색해야합니다.

대신 시간에 따른 변화의 개념을 스키마 디자인에 적용 할 수 있습니다 (Keethanjan이 제안하는 두 번째 옵션입니다). 이것은 확실히 비즈니스 로직 및 지속성 수준에서 응용 프로그램에 대한 변경 사항이므로 사소한 것은 아닙니다.

예를 들어 다음과 같은 테이블이있는 경우 :

CUSTOMER
---------
CUSTOMER_ID PK
CUSTOMER_NAME
CUSTOMER_ADDRESS

시간이 지남에 따라 추적하려면 다음과 같이 수정해야합니다.

CUSTOMER
------------
CUSTOMER_ID            PK
CUSTOMER_VALID_FROM    PK
CUSTOMER_VALID_UNTIL   PK
CUSTOMER_STATUS
CUSTOMER_USER
CUSTOMER_NAME
CUSTOMER_ADDRESS

레코드를 업데이트하는 대신 고객 레코드를 변경할 때마다 현재 레코드의 VALID_UNTIL을 NOW ()로 설정하고 VALID_FROM (현재) 및 null VALID_UNTIL을 사용하여 새 레코드를 삽입합니다. "CUSTOMER_USER"상태를 현재 사용자의 로그인 ID로 설정합니다 (유지해야하는 경우). 고객을 삭제해야하는 경우 CUSTOMER_STATUS 플래그를 사용하여이를 표시합니다.이 테이블에서 레코드를 삭제할 수 없습니다.

이렇게하면 주어진 날짜에 대한 고객 테이블의 상태를 항상 찾을 수 있습니다. 주소는 무엇입니까? 이름이 바뀌 었나요? valid_from 및 valid_until 날짜가 유사한 다른 테이블에 조인하여 전체 그림을 역사적으로 재구성 할 수 있습니다. 현재 상태를 찾으려면 VALID_UNTIL 날짜가 null 인 레코드를 검색합니다.

다루기 어렵습니다 (엄격히 말하면 valid_from이 필요하지 않지만 쿼리가 조금 더 쉬워집니다). 설계와 데이터베이스 액세스가 복잡해집니다. 그러나 그것은 세상을 훨씬 더 쉽게 재구성 할 수있게합니다.

---

이를 수행하는 간단한 방법은 다음과 같습니다.

먼저 추적하려는 각 데이터 테이블에 대한 기록 테이블을 만듭니다 (아래 예제 쿼리). 이 테이블에는 데이터 테이블의 각 행에서 수행되는 각 삽입, 업데이트 및 삭제 쿼리에 대한 항목이 있습니다.

히스토리 테이블의 구조는 3 개의 추가 열을 제외하고 추적하는 데이터 테이블과 동일합니다 : 발생한 작업을 저장하는 열 ( 'action'이라고합시다), 작업 날짜 및 시간, 열 작업마다 증가하고 데이터 테이블의 기본 키 열로 그룹화되는 시퀀스 번호 ( '개정')를 저장합니다.

이 시퀀싱 동작을 수행하기 위해 기본 키 열과 개정 열에 두 개의 열 (복합) 인덱스가 생성됩니다. 히스토리 테이블에서 사용하는 엔진이 MyISAM 인 경우에만이 방식으로 시퀀싱을 수행 할 수 있습니다 ( 이 페이지의 'MyISAM Notes'참조).

히스토리 테이블은 생성하기가 매우 쉽습니다. 아래 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

편집 : 오 와우, 사람들은 6 년 전의 내 역사 테이블을 좋아합니다 : P

My implementation of it is still humming along, getting bigger and more unwieldy, I would assume. I wrote views and pretty nice UI to look at the history in this database, but I don't think it was ever used much. So it goes.

To address some comments in no particular order:

• I did my own implementation in PHP that was a little more involved, and avoided some of the problems described in comments (having indexes transferred over, signifcantly. If you transfer over unique indexes to the history table, things will break. There are solutions for this in the comments). Following this post to the letter could be an adventure, depending on how established your database is.

• If the relationship between the primary key and the revision column seems off it usually means the composite key is borked somehow. On a few rare occasions I had this happen and was at a loss to the cause.

• I found this solution to be pretty performant, using triggers as it does. Also, MyISAM is fast at inserts, which is all the triggers do. You can improve this further with smart indexing (or lack of...). Inserting a single row into a MyISAM table with a primary key shouldn't be an operation you need to optimize, really, unless you have significant issues going on elsewhere. In the entire time I was running the MySQL database this history table implementation was on, it was never the cause of any of the (many) performance problems that came up.

• if you're getting repeated inserts, check your software layer for INSERT IGNORE type queries. Hrmm, can't remember now, but I think there are issues with this scheme and transactions which ultimately fail after running multiple DML actions. Something to be aware of, at least.

• It's important that the fields in the history table and the data table match up. Or, rather, that your data table doesn't have MORE columns than the history table. Otherwise, insert/update/del queries on the data table will fail, when the inserts to the history tables put columns in the query that don't exist (due to d.* in the trigger queries), and the trigger fails. t would be awesome if MySQL had something like schema-triggers, where you could alter the history table if columns were added to the data table. Does MySQL have that now? I do React these days :P

---

You could create triggers to solve this. Here is a tutorial to do so (archived link).

Setting constraints and rules in the database is better than writing special code to handle the same task since it will prevent another developer from writing a different query that bypasses all of the special code and could leave your database with poor data integrity.

For a long time I was copying info to another table using a script since MySQL didn’t support triggers at the time. I have now found this trigger to be more effective at keeping track of everything.

This trigger will copy an old value to a history table if it is changed when someone edits a row. Editor ID and last mod are stored in the original table every time someone edits that row; the time corresponds to when it was changed to its current form.

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

Another solution would be to keep an Revision field and update this field on save. You could decide that the max is the newest revision, or that 0 is the most recent row. That's up to you.

---

Here is how we solved it

a Users table looked like this

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

And the business requirement changed and we were in a need to check all previous addresses and phone numbers a user ever had. new schema looks like this

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

To find the current address of any user, we search for UserData with revision DESC and LIMIT 1

To get the address of a user between a certain period of time we can use created_on bewteen (date1 , date 2)

---

Why not simply use bin log files? If the replication is set on the Mysql server, and binlog file format is set to ROW, then all the changes could be captured.

A good python library called noplay can be used. More info here.

---

Just my 2 cents. I would create a solution which records exactly what changed, very similar to transient's solution.

My ChangesTable would simple be:

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

1) When an entire row is changed in the main table, lots of entries will go into this table, BUT that is very unlikely, so not a big problem (people are usually only changing one thing) 2) OldVaue (and NewValue if you want) have to be some sort of epic "anytype" since it could be any data, there might be a way to do this with RAW types or just using JSON strings to convert in and out.

Minimum data usage, stores everything you need and can be used for all tables at once. I'm researching this myself right now, but this might end up being the way I go.

For Create and Delete, just the row ID, no fields needed. On delete a flag on the main table (active?) would be good.

---

MariaDB supports System Versioning since 10.3 which is the standard SQL feature that does exactly what you want: it stores history of table records and provides access to it via SELECT queries. MariaDB is an open-development fork of MySQL. You can find more on its System Versioning via this link:

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

---

The direct way of doing this is to create triggers on tables. Set some conditions or mapping methods. When update or delete occurs, it will insert into 'change' table automatically.

But the biggest part is what if we got lots columns and lots of table. We have to type every column's name of every table. Obviously, It's waste of time.

To handle this more gorgeously, we can create some procedures or functions to retrieve name of columns.

We can also use 3rd-part tool simply to do this. Here, I write a java program Mysql Tracker

참고URL : https://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records