레코드의 변경 내역을 추적하는 MySQL 옵션/기능이 있습니까?
MySQL 데이터베이스의 레코드에 대한 변경 사항을 추적할 수 있는지 물어봤습니다.따라서 필드가 변경되면 이전과 새 필드를 사용할 수 있으며 이 작업이 수행된 날짜가 표시됩니다.이를 위한 기능 또는 일반적인 기술이 있습니까?
만약 그렇다면, 저는 이런 것을 하려고 생각하고 있었습니다.''라는 표를 보세요.changes마스터 테이블과 동일한 필드를 포함하지만 앞에 old 및 new가 붙습니다.단, 실제로 변경된 필드만 해당됩니다.TIMESTAMP네, 네, 네.이 인덱스는 다음 명령어를 사용하여 인덱싱됩니다.ID 하면 ★★★★★★★★★★★★★★★★★★★★★★」SELECT보고서를 실행하여 각 레코드의 이력을 표시할 수 있습니다.게게좋 ?은 법? ???★★★★★★★★★★★★★★★★★★!
이를 위한 간단한 방법은 다음과 같습니다.
먼저 추적하려는 각 데이터 테이블에 대한 기록 테이블을 만듭니다(아래 쿼리 예).이 테이블에는 데이터 테이블의 각 행에 대해 수행된 각 삽입, 업데이트 및 삭제 쿼리에 대한 항목이 있습니다.
이력 테이블의 구조는 3개의 추가 컬럼을 제외하고 추적하는 데이터 테이블과 동일합니다.이 컬럼은 발생한 조작을 저장하는 컬럼('액션'이라고 부름), 조작 날짜와 시간, 시퀀스 번호('revision')를 저장하는 컬럼입니다.이 컬럼은 조작별로 증가하며 프라이머리 키 컬럼으로 그룹화 됩니다.데이터 테이블에서 선택합니다.
이 시퀀싱 동작을 수행하기 위해 기본 키 열과 수정기호 열에 두 개의 열(복합) 인덱스가 생성됩니다.기록 테이블에 사용되는 엔진이 MyISAM인 경우에만 이 방식으로 시퀀싱을 수행할 수 있습니다('My' 참조).ISAM 메모' (이 페이지)
이력표는 작성하기가 꽤 쉽습니다.아래의 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년 전 내 역사표 같은 거 좋아하네.
내 생각에 내 실장은 여전히 순조롭게 진행되고 있고, 점점 더 커지고 다루기 어려워지고 있는 것 같다.이 데이터베이스의 이력을 보기 위해 뷰와 꽤 멋진 UI를 작성했지만, 많이 사용된 적은 없었던 것 같습니다.그래, 그렇게.
일부 코멘트를 특정 순서 없이 처리하려면:
PHP에 실장하고, 코멘트에 기재되어 있는 문제의 일부를 회피했습니다(인덱스 전송이 현저하게 이루어졌습니다).고유한 인덱스를 기록 테이블로 전송하면 작업이 중단됩니다.이에 대한 해결책이 댓글에 있습니다.)데이터베이스의 확립 정도에 따라서는, 이 투고를 문자까지 계속하는 것은 모험이 될 수 있습니다.
프라이머리 키와 리비전 열과의 관계가 어긋나 있는 경우는, 통상, 복합 키가 구부러져 있는 것을 의미합니다.나는 드문 경우 몇 번 이런 일이 있어서 그 명분을 몰랐다.
이 솔루션은 트리거를 그대로 사용하여 성능이 상당히 우수하다는 것을 알게 되었습니다.또한 MyISAM은 삽입 속도가 빠릅니다.이것은 트리거의 전부입니다.스마트 인덱싱(또는 부족...)을 통해 이를 더욱 개선할 수 있습니다.프라이머리 키를 사용하여 MyISAM 테이블에 1개의 행을 삽입하는 것은 다른 곳에서 중대한 문제가 발생하지 않는 한 최적화할 필요가 없는 조작입니다.MySQL 데이터베이스를 실행하는 동안 이 이력 테이블 구현은 실행 중이었으며, 발생한 (많은) 성능 문제의 원인은 아니었습니다.
삽입이 반복되는 경우 소프트웨어 계층에서 INSERT IGNORE 유형 쿼리를 확인하십시오.Hrmm, 지금은 기억이 나지 않지만, 이 스킴과 트랜잭션에 문제가 있다고 생각합니다만, DML 액션을 여러 번 실행한 후에 최종적으로 실패합니다.최소한 알아둬야 할 것들이죠
이력 테이블과 데이터 테이블의 필드를 일치시키는 것이 중요합니다.또는 데이터 테이블에 기록 테이블보다 더 많은 열이 없습니다.그렇지 않으면 데이터 테이블에 대한 insert/update/del 쿼리는 실패합니다.데이터 테이블에 대한 insert/update/del 쿼리는 (트리거 쿼리의 d.*로 인해) 존재하지 않는 컬럼을 쿼리에 포함시키고 MySQL에 스키마 트리거와 같은 것이 있으면 트리거가 실패합니다.여기서 컬럼이 데이터 탭에 추가되면 이력 테이블을 변경할 수 있습니다.e. MySQL에 그것이 있습니까?요즘 리액트 하고 있어요p
그것은 미묘하다.
업무상 「데이터의 변경에 대해 감사하고 싶다.누가 언제 무엇을 했는가?」라고 하는 경우는, 통상, 감사표를 사용할 수 있습니다(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(NOW)과 늘 VALID_UNTIL을 포함한 새 레코드를 삽입합니다.CUSTOMER_USER 상태를 현재 사용자의 로그인 ID로 설정합니다(필요한 경우).고객을 삭제할 필요가 있는 경우 CUSTOMER_STATUS 플래그를 사용하여 이를 나타냅니다.이 테이블에서 레코드를 삭제할 수 없습니다.
그러면 지정된 날짜에 대한 고객 테이블의 상태를 언제든지 확인할 수 있습니다. 주소가 무엇이었습니까?이름이 바뀌었나요?유사한 valid_from 및 valid_until 날짜를 가진 다른 테이블에 결합하면 전체 그림을 이력적으로 재구성할 수 있습니다.현재 상태를 검색하려면 VALID_UNTIL 날짜가 null인 레코드를 검색합니다.
번거롭습니다(엄밀히 말하면 valid_from은 필요 없습니다만, 조금 더 쉽게 문의할 수 있습니다).설계와 데이터베이스 접근이 복잡해집니다.하지만 그것은 세상을 재건하는 것을 훨씬 더 쉽게 만듭니다.
이 문제를 해결하기 위해 트리거를 만들 수 있습니다.이를 위한 튜토리얼(아카이브 링크)을 다음에 나타냅니다.
데이터베이스에서 제약 조건 및 규칙을 설정하면 다른 개발자가 모든 특수 코드를 바이패스하는 다른 쿼리를 쓰는 것을 방지하고 데이터베이스의 데이터 무결성이 저하될 수 있으므로 동일한 작업을 처리하기 위해 특수 코드를 작성하는 것보다 더 좋습니다.
MySQL이 트리거를 지원하지 않았기 때문에 오랫동안 스크립트를 사용하여 다른 테이블에 정보를 복사했습니다.나는 이제 이 방아쇠가 모든 것을 추적하는 데 더 효과적이라는 것을 알게 되었다.
이 트리거는 다른 사용자가 행을 편집할 때 이전 값이 변경된 경우 기록 테이블에 복사됩니다.
Editor ID★★★★★★★★★★★★★★★★★」last mod해당 행을 편집할 때마다 원래 테이블에 저장됩니다.시간은 현재 양식으로 변경된 시간과 일치합니다.
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;
$$
다른 해결 방법은 Revision 필드를 유지하고 저장 시 이 필드를 업데이트하는 것입니다.max가 최신 리비전인지 0이 최신 행인지 확인할 수 있습니다.그건 너에게 달렸어.
해결 방법은 다음과 같습니다.
사용자 테이블은 다음과 같습니다.
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
사용자의 현재 주소를 찾기 위해 개정 DESC 및 LIMIT 1을 사용하여 UserData를 검색합니다.
특정 기간 사이에 사용자의 주소를 얻으려면 created_on bewteen(date1, date2)을 사용합니다.
Versioning을 이하고 MariaDB(R) 10.3 "System Versioning"을 통해 수 있습니다.이 기능은 사용자가 원하는 작업을 수행하는 표준 SQL 기능입니다.테이블 레코드의 이력을 저장하고 다음 방법으로 액세스할 수 있습니다.SELECT는 MySQL의입니다.MariaDB MySQL maria maria maria maria 。자세한 내용은 다음 링크를 참조하십시오.
https://mariadb.com/kb/en/library/system-versioned-tables/
단순히 bin 로그 파일을 사용하면 어떨까요?복제가 Mysql 서버에서 설정되어 있고 binlog 파일 형식이 ROW로 설정되어 있으면 모든 변경을 캡처할 수 있습니다.
noplay라고 불리는 좋은 python 라이브러리를 사용할 수 있습니다.자세한 것은 이쪽.
내 2센트만.과도기 솔루션과 매우 유사한 변경 사항을 정확히 기록하는 솔루션을 만듭니다.
My ChangesTable은 다음과 같습니다.
DateTime | WhoChanged | TableName | Action | ID |FieldName | OldValue
1) 메인 테이블에서 행 전체를 변경하면 이 테이블에는 많은 엔트리가 들어갑니다만, 별로 문제가 되지 않기 때문에(일반적으로 사람들은 한 가지만 변경하고 있습니다) 2) OldVaue(및 NewValue(필요한 경우)는 데이터가 될 수 있기 때문에, 이 RAW 타입이나 US 타입을 사용하는 방법이 있습니다.변환하는 G JSON 문자열.
최소 데이터 사용률. 필요한 모든 데이터를 저장하여 모든 테이블에 동시에 사용할 수 있습니다.지금은 제가 직접 알아보고 있지만, 이게 제가 가는 길이 될 수도 있어요.
[ Create ]및 [Delete]의 경우 행 ID만 사용할 수 있으며 필드는 필요하지 않습니다.메인 테이블에 있는 플래그(액티브?)를 삭제할 때 좋습니다.
이를 위한 직접적인 방법은 테이블에 트리거를 작성하는 것입니다.몇 가지 조건 또는 매핑 방법을 설정합니다.업데이트 또는 삭제가 발생하면 자동으로 '변경' 테이블에 삽입됩니다.
하지만 가장 큰 부분은 기둥과 테이블이 많을 경우입니다.모든 표의 모든 열의 이름을 입력해야 합니다.분명히 시간 낭비예요.
이를 보다 상세하게 처리하기 위해 몇 가지 절차 또는 함수를 만들어 열 이름을 검색할 수 있습니다.
또, 3부 툴을 간단하게 사용할 수도 있습니다.여기, 나는 자바 프로그램인 mysql Tracker를 쓴다.
MariaDB 10.5+에서는 이 설정은 다음과 같이 간단합니다.
CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME;
그런 다음 다음을 수행하여 과거 이력을 조회할 수 있습니다.
SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP '2016-10-09 08:07:06';
현재 MySQL에는 이에 대한 대응책이 없습니다.
상세한 것에 대하여는, 메뉴얼을 참조해 주세요.이전 버전의 MariaDB를 사용하는 경우 이 문서에는 MariaDB 10.3.4 이후 사용 가능한 대체 구문이 있습니다.
언급URL : https://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records
'programing' 카테고리의 다른 글
| unix 타임스탬프 문자열을 읽을 수 있는 날짜로 변환하는 중 (0) | 2023.01.21 |
|---|---|
| 열이 있는 경우 변경 (0) | 2023.01.21 |
| 한 줄로 동적으로 인쇄 (0) | 2023.01.21 |
| 주의: 정의되지 않은 오프셋: 0 in (0) | 2023.01.21 |
| 폴더 또는 파일 크기 가져오기 (0) | 2023.01.21 |