Last Updated on 3월 21, 2023 by Jade(정현호)
안녕하세요.
이번 포스팅은 MySQL Invisible Columns 을 활용한 table audit 내용 기록과 관련된 내용을 살펴보도록 하겠습니다.
포스팅은 lefred.be 사이트에서 다룬 내용을 번역 및 테스트를 진행한 내용으로 작성되어 있습니다.
invisible columns
invisible columns 은 MySQL 8.0.23 버전 부터 추가된 기능으로 일반적으로 쿼리에 숨겨져 있지만 명시 적으로 컬럼을 지정하여 조회하는 경우 액세스 할 수 있는 컬럼 기능 입니다.
invisible columns 을 유용하게 사용할 수 있는 상황으로는 응용 프로그램이 "SELECT * FROM 테이블" 과 같은 유형의 쿼리를 사용하여 테이블에 액세스하고 있을 경우 invisible columns 기능을 사용하여 컬럼을 추가하여 테이블 구조를 변경 하더라도 응용 프로그램에서는 오류나 별도의 변경 없이 계속 작업을 진행 할 수 있습니다.
수정 사항이 반영되어 새로 배포되는 응용 프로그램에서는 필요한 경우 명시 적으로 컬럼명을 지정하여 invisible column을 일반 컬럼 처럼 사용할 수도 있습니다.
관련한 더 자세한 사용방법은 이전의 포스팅을 참조하시면 됩니다.
Audit 기능 활용
invisible columns 은 위에서 그리고 이전 포스팅에서 설명한 것과 같이 SELECT * FROM 과 같은 실행에서는 해당 컬럼이 조회 되지 않으며 명시적으로 invisible columns 을 기재해야 조회 및 변경 등이 가능 합니다
그래서 애플리케이션에서는 업무적으로 사용하는 특정 컬럼을 지정하여 사용하거나, 컬럼수가 적어서 SELECT * FROM 으로 조회시 invisible columns 은 조회가 되지 않아 여러가지로 활용할 수가 있습니다
(물론 업무 쿼리에서 SELECT * FROM 유형으로 쓰는 경우는 많지 않다고 생각함)
위의 내용과 같이 보통의 경우는 invisible columns 이 보이지 않기 때문에 Audit 정보를 담은 컬럼을 invisible columns 속성으로 만들어서 사용한다면 그것도 좋은 활용 방안 중에 하나라고 생각 됩니다.
일반 타입의 컬럼을 활용
포스팅에서는 일반 컬럼 타입을 활용한 방법과 그 다음에 JSON 타입을 활용한 방법에 대해서 설명하려고 합니다.
먼저 일반 유형 타입의 컬럼을 사용한 audit 기능 활용에 대해서 확인 해보도록 하겠습니다.
테스트 테이블 과 invisible columns 컬럼도 추가하도록 하겠습니다.
테이블 생성 및 데이터 적재
CREATE TABLE `tb_test_demo` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`firstname` varchar(20) DEFAULT NULL,
`lastname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into tb_test_demo(firstname,lastname)
values('jade','jung'),('ethan','miller');
위와 같이 생성을 하였으며,테이블 생성 후 2건의 데이터를 입력 하였습니다.
audtit 정보를 담기 위한 invisible columns 속성의 컬럼을 4개 추가 하도록 하겠습니다.
alter table tb_test_demo
add created_at timestamp
default current_timestamp invisible,
add created_by varchar(60) not null
default '' invisible,
add updated_at timestamp
on update current_timestamp invisible,
add updated_by varchar(60) not null
default '' invisible ;
각 컬럼에 저장되는 데이터의 속성은 아래와 같습니다.
created_at : 레코드가 추가(insert) 될때의 timestamp 정보
created_by : 레코드 추가(insert) 되었을 때 작업 실행한 유저명
updated_at : 레코드가 갱신(update) 될때의 timestamp 정보
updated_by : 레코드를 마지막 갱신할 때 수행한 유저명
입력된 데이터는 아래와 같습니다.
select * from tb_test_demo; +----+-----------+----------+ | id | firstname | lastname | +----+-----------+----------+ | 1 | jade | jung | | 2 | ethan | miller | +----+-----------+----------+
invisible columns 속성의 컬럼은 SELECT * 로는 출력 되지 않습니다
데이터 변경시 기록을 남길 수 있도록 trigger 를 생성하도록 하겠습니다.
create trigger demo_insert before insert on tb_test_demo
for each row
set new.created_by=session_user();
create trigger demo_update before update on tb_test_demo
for each row
set new.updated_by=session_user();
지금 상태에서 조회를 아래와 같은 결과를 확인 할 수 있습니다.
select *, created_at, created_by, updated_at, updated_by from tb_test_demo; +----+-----------+----------+---------------------+------------+------------+------------+ | id | firstname | lastname | created_at | created_by | updated_at | updated_by | +----+-----------+----------+---------------------+------------+------------+------------+ | 1 | jade | jung | 2022-04-17 20:59:24 | | NULL | | | 2 | ethan | miller | 2022-04-17 20:59:24 | | NULL | | +----+-----------+----------+---------------------+------------+------------+------------+
다시 데이터 1건을 입력해보겠습니다.
insert into tb_test_demo (firstname, lastname)
values ('liam', 'harris');
다시 SELECT * 로 조회해보겠습니다.
select * from tb_test_demo; +----+-----------+----------+ | id | firstname | lastname | +----+-----------+----------+ | 1 | jade | jung | | 2 | ethan | miller | | 3 | liam | harris | +----+-----------+----------+
그 다음에는 invisible columns 속성 컬럼을 포함아여 모든 컬럼을 조회를 해보도록 하겠습니다.
select *, created_at, created_by, updated_at, updated_by from tb_test_demo; +----+-----------+----------+---------------------+---------------------+------------+------------+ | id | firstname | lastname | created_at | created_by | updated_at | updated_by | +----+-----------+----------+---------------------+---------------------+------------+------------+ | 1 | jade | jung | 2022-04-17 20:59:24 | | NULL | | | 2 | ethan | miller | 2022-04-17 20:59:24 | | NULL | | | 3 | liam | harris | 2022-04-17 21:15:41 | test_user@localhost | NULL | | +----+-----------+----------+---------------------+---------------------+------------+------------+
3번째 입력한 firstname 이 liam 인 유저의 레코드에는 creadted_by 정보가 표기 되는 것을 확인 할 수 있습니다.
created_at 과 updated_at은 default current_timestamp 속성으로 입력이나 갱신시 내용이 채워지게 됩니다.
이번에는 UPDATE 후 조회를 해보겠습니다.
update tb_test_demo set firstname='daniel' where id=2; select *, created_at, created_by, updated_at, updated_by from tb_test_demo; +----+-----------+----------+---------------------+---------------------+---------------------+---------------------+ | id | firstname | lastname | created_at | created_by | updated_at | updated_by | +----+-----------+----------+---------------------+---------------------+---------------------+---------------------+ | 1 | jade | jung | 2022-04-17 20:59:24 | | NULL | | | 2 | daniel | miller | 2022-04-17 20:59:24 | | 2022-04-17 21:17:42 | test_user@localhost | | 3 | liam | harris | 2022-04-17 21:15:41 | test_user@localhost | NULL | | +----+-----------+----------+---------------------+---------------------+---------------------+---------------------+
두번째 유저인 lastname 이 miller 유저의 first_name 을 갱신하였으며, 갱신 시간과 갱신을 실행한 유저의 정보가 기록된 것을 확인할 수 있습니다.
다른 DB 유저로 INSERT 실행 후 조회해 보도록 하겠습니다.
insert into tb_test_demo (firstname, lastname)
values ('benjamin', 'martin');
select id, firstname, created_by, updated_by
from tb_test_demo;
+----+-----------+---------------------+---------------------+
| id | firstname | created_by | updated_by |
+----+-----------+---------------------+---------------------+
| 1 | jade | | |
| 2 | daniel | | test_user@localhost |
| 3 | liam | test_user@localhost | |
| 4 | benjamin | root@localhost | | <-!!
+----+-----------+---------------------+---------------------+
first_name 이 benjamin 이라는 유저를 입력하였으며 입력을 실행한 유저가 이번에는 root@localhost 로 기록된 것을 확인 할 수 있습니다.
마지막으로 SELECT * 로 조회하면 invisible columns 속성 컬럼이 출력이 되지 않는 것을 확인 할 수 있습니다.
select *
-> from tb_test_demo;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
| 1 | jade | jung |
| 2 | daniel | miller |
| 3 | liam | harris |
| 4 | benjamin | martin |
+----+-----------+----------+
이와 같이 일반적으로 출력이 되지 않는 invisible columns 속성 컬럼을 사용하여 이와 같이 audit 정보를 담아두는 방법도 invisible columns 을 활용하는 한가지 방법이며, 컬럼의 구성과 트리거의 작성 내용에 따라서 더 많은 또는 유용한 정보를 남길수도 있습니다.
JSON 타입을 활용
MySQL 5.7.8 버전 부터 native JSON data type 을 지원하며, 그에 따라서 하나의 레코드내에서 여러가지 타입의 값이 있는 형태로 저장할 수 있게 되었으며, native JSON data type 을 지원함에 따라서 "문서 유효성 검사", "효율적인 액세스 지원", "성능", "편리성" 등의 기능을 제공하며, json_extract 나 json_pretty 와 같은 JSON 타입을 위한 함수도 지원되고 있습니다.
이러한 JSON 을 활용하는 예제를 살펴보도록 하겠습니다. 먼저 이전에 사용한 테이블을 삭제후 다시 생성 하도록 겠습니다.
테이블 재생성
drop table tb_test_demo;
CREATE TABLE `tb_test_demo` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`firstname` varchar(20) DEFAULT NULL,
`lastname` varchar(20) DEFAULT NULL,
`audit_info` json DEFAULT NULL invisible,
-- <--!!!
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
이번에는 테이블 생성시 JSON 타입의 컬럼을 추가하였습니다(audit_info)
이번에는 트리거 2개를 생성을 진행하도록 하겠으며 하나는 다소 복잡한 내용을 담고 있습니다.
트리거 생성
create trigger tb_test_demo_insert before insert on tb_test_demo
for each row
set new.audit_info=json_object('created_at', now(),
'created_by', session_user());
DELIMITER $$
create trigger tb_test_demo_update before update on tb_test_demo
for each row
BEGIN
declare modif json default null;
declare modif_arr json default null;
select json_object('updated_at', now(),
'updated_by', session_user()) into modif;
if old.id <> new.id then
select json_merge_patch(modif,
json_object('id',
json_object("old", old.id,
"new", new.id)
)
) into modif;
end if;
if old.firstname <> new.firstname then
select json_merge_patch(modif,
json_object('firstname',
json_object("old", old.firstname,
"new", new.firstname)
)
) into modif;
end if;
if old.lastname <> new.lastname then
select json_merge_patch(modif,
json_object('lastname',
json_object("old", old.lastname,
"new", new.lastname)
)
) into modif;
end if;
if json_extract(old.audit_info, "$.modifications")
is NULL then
set new.audit_info=json_merge_patch(old.audit_info,
json_set(old.audit_info,
"$.modifications", modif));
else
set new.audit_info=json_merge_patch(old.audit_info,
json_array_append(old.audit_info,
"$.modifications", modif));
end if;
END$$
DELIMITER ;
이제 데이터 2건을 추가 후에 조회를 해보도록 하겠습니다.
-- 데이터 INSERT
insert into tb_test_demo(firstname,lastname)
values('jade','jung'),('ethan','miller');
-- json_pretty 을 통해 JSON 조회
select *,json_pretty(audit_info)
from tb_test_demo\G
******************** 1. row ********************
id: 1
firstname: jade
lastname: jung
json_pretty(audit_info): {
"created_at": "2022-04-17 22:50:32.000000",
"created_by": "test_user@localhost"
}
******************** 2. row ********************
id: 2
firstname: ethan
lastname: miller
json_pretty(audit_info): {
"created_at": "2022-04-17 22:50:32.000000",
"created_by": "test_user@localhost"
}
생성한 트리거에 동작에 따라서 데이터 입력에 대한 정보가 JSON 타입의 audit_info 컬럼에 기록이 된 것을 확인 할 수 있습니다.
이제 데이터를 업데이트 후에 다시 한번 audit_info 컬럼을 조회해 보도록 하겠습니다.
-- 데이터 갱신
update tb_test_demo
set firstname='daniel'
where id=2;
-- json_pretty 을 통해 JSON 조회
mysql> select *,json_pretty(audit_info)
from tb_test_demo\G
********************* 1. row *********************
id: 1
firstname: jade
lastname: jung
json_pretty(audit_info): {
"created_at": "2022-04-17 22:50:32.000000",
"created_by": "test_user@localhost"
}
********************* 2. row *********************
id: 2
firstname: daniel
lastname: miller
json_pretty(audit_info): {
"created_at": "2022-04-17 22:50:32.000000",
"created_by": "test_user@localhost",
"modifications": {
"firstname": {
"new": "daniel",
"old": "ethan"
},
"updated_at": "2022-04-17 22:50:47.000000",
"updated_by": "test_user@localhost"
}
}
이 테이블 역시도 SELECT * 로 조회를 하여도 invisible columns 컬럼이기 때문에 출력은 되지 않습니다
select * from tb_test_demo; +----+-----------+----------+ | id | firstname | lastname | +----+-----------+----------+ | 1 | jade | jung | | 2 | daniel | miller | +----+-----------+----------+
위에서 확인 되는 내용과 같이 두번째 생성한 트리거인 "tb_test_demo_update" 에 의해서 UPDATE 실행시 보다 많은 정보가 남겨진 것을 확인 할 수 있습니다.
audit 또는 데이터 변경의 tracking 관점에서 변경시 많은 정보가 필요할 것 입니다. 그에 따라서 이와 같이 JSON 을 통해서 다양한 정보를 남기는 것도 invisible columns 속성 컬럼을 사용하는 좋은 방법이라고 생각 됩니다.
Reference
Reference URL
• lefred.be/table-audit-information
• lefred.be/table-audit-information-part2
• dev.mysql.com/json
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
