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

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

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

히스토리 테이블의 구조는 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

내 구현은 여전히 ​​윙윙 거리며 점점 커지고 다루기 어려워집니다. 이 데이터베이스의 히스토리를보기 위해 뷰와 꽤 멋진 UI를 작성했지만 많이 사용 된 적이 없다고 생각합니다. 그래서 간다.

특정 순서없이 일부 의견을 처리하려면 :

• 나는 PHP에서 조금 더 관련이있는 내 자신의 구현을 수행했고 주석에 설명 된 문제 중 일부를 피했습니다 (인덱스가 크게 전송되었습니다. 고유 인덱스를 히스토리 테이블로 전송하면 문제가 발생합니다. 이것은 주석에서). 이 포스트를 따르는 것은 데이터베이스가 어떻게 구축되었는지에 따라 모험이 될 수 있습니다.

• 기본 키와 개정 열 사이의 관계가 꺼져있는 것처럼 보이면 일반적으로 복합 키가 어떻게 든 지루함을 의미합니다. 드물게 저는 이런 일이 발생하여 원인을 잃었습니다.

• 이 솔루션은 트리거를 그대로 사용하여 성능이 매우 우수하다는 것을 알았습니다. 또한 MyISAM은 삽입 속도가 빠르며 모든 트리거가 수행합니다. 스마트 인덱싱 (또는 부족한 …)을 통해이를 더욱 개선 할 수 있습니다. 기본 키를 사용하여 MyISAM 테이블에 단일 행을 삽입하는 것은 다른 곳에서 중요한 문제가 발생하지 않는 한 실제로 최적화해야하는 작업이 아닙니다. 이 히스토리 테이블 구현이있는 MySQL 데이터베이스를 실행하는 동안 내내 발생한 (많은) 성능 문제의 원인은 아닙니다.

• 반복적으로 삽입되는 경우 소프트웨어 계층에서 INSERT IGNORE 유형 쿼리를 확인하십시오. 흠, 지금은 기억이 나지 않지만 여러 DML 작업을 실행 한 후 궁극적으로 실패하는이 체계와 트랜잭션에 문제가 있다고 생각합니다. 적어도 알아야 할 것.

• 기록 테이블과 데이터 테이블의 필드가 일치하는 것이 중요합니다. 또는 데이터 테이블에 히스토리 테이블보다 더 많은 열이 없습니다. 그렇지 않으면 데이터 테이블에 대한 삽입 / 업데이트 / 삭제 쿼리가 실패하고, 기록 테이블에 대한 삽입이 존재하지 않는 쿼리에 열을 넣고 (트리거 쿼리의 d. *로 인해) 트리거가 실패합니다. MySQL에 스키마 트리거와 같은 것이 있으면 데이터 테이블에 열이 추가되면 히스토리 테이블을 변경할 수 있습니다. MySQL에 지금이 있습니까? 요즘 React를합니다 : P

답변

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

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

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