Last Updated on 2월 9, 2025 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL의 Generated Columns 기능에 대해서 살펴보도록 하겠습니다.
Generated Columns
Generated Columns은 MySQL 5.7 버전에서 추가된 기능으로 이 컬럼의 데이터는 지정된 식을 기반으로 계산되어 생성되거나 지정된 표현 형태로 출력을 하는 기능을 합니다.
Generated Columns 은 저장 방식에 따라서 VIRTUAL 과 STORED 타입으로 나눌 수 있습니다.
Generated Columns 기능은 아래의 간단한 예시를 통해서 기능을 이해할 수 있습니다.
mysql> CREATE TABLE triangle ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)), primary key(id) ); mysql> INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8); mysql> SELECT * FROM triangle; +----+-------+-------+--------------------+ | id | sidea | sideb | sidec | +----+-------+-------+--------------------+ | 1 | 1 | 1 | 1.4142135623730951 | | 2 | 3 | 4 | 5 | | 3 | 6 | 8 | 10 | +----+-------+-------+--------------------+
Generated Columns 은 이와 같이 지정된 계산식이나 표현식에 따라서 표현되는 컬럼 기능이며, 컬럼 타입 키워드를 지정하지 않으면 기본값은 VIRTUAL 입니다.
VIRTUAL
VIRTUAL 타입의 Generated Columns 은 값은 저장되지 않지만, 행을 읽을 때, 모든 Before 트리거 직후에 평가됩니다. VIRTUAL 타입은 데이터를 저장하지 않음으로 공간을 차지하지 않습니다.
위의 설명에서 "모든 Before 트리거 직후에 평가됩니다." 라는 의미는 다음의 예제를 통해서 내용을 확인할 수 있습니다.
먼저 아래와 같이 테이블을 생성하도록 하겠습니다.
mysql> CREATE TABLE tb_test ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', col1 varchar(50), col2 varchar(50), created_dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록날짜시간', gen_created_dt datetime GENERATED ALWAYS AS (created_dt) VIRTUAL NULL, primary key(id) );
테스트 시스템의 sql_mode는 기본값이며 아래와 같습니다.
mysql> select replace(@@session.sql_mode,',','\n') as sql_mode\G *************************** 1. row *************************** sql_mode: ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION
이제 데이터를 입력해보겠습니다.
mysql> insert into tb_test (col1,col2)
values('a','b');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'gen_created_dt' at row 1
ERROR 1292 에러가 발생되었습니다.
Generated Columns 으로 만들어진 gen_created_dt 컬럼은 created_dt 값을 그대로 표현하는 것으로 설정되어 있습니다.
데이터가 입력 시 "DEFAULT CURRENT_TIMESTAMP" 에 의해서 현재 날짜시간 값이 입력될 때 before trigger 에 의해서 gen_created_dt 에는 0000-00-00 00:00:00 이 먼저 입력된 후에 Generated Columns에서 설정된 표현식으로 평가가 되는 과정에서 에러가 발생되게 됩니다.
문서에서 나오는 "모든 Before 트리거 직후에 평가됩니다." 라는 의미는 이런 부분을 의미합니다.
아래와 같이 sql_mode를 제외하고 다시 데이터를 입력하면 정상적으로 입력되면서 설정된 Generated Columns의 평가까지 적용된 것을 확인할 수 있습니다.
mysql> SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_IN_DATE');
mysql> SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_DATE');
mysql> SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ENGINE_SUBSTITUTION');
mysql> insert into tb_test (col1,col2)
values('a','b');
mysql> select * from tb_test;
+----+------+------+---------------------+---------------------+
| id | col1 | col2 | created_dt | gen_created_dt |
+----+------+------+---------------------+---------------------+
| 1 | a | b | 2023-03-20 16:53:00 | 2023-03-20 16:53:00 |
+----+------+------+---------------------+---------------------+
위에서 생성한 테이블 triangle은 VIRTUAL 타입의 Generated Columns 입니다.
*************************** 1. row ***************************
Table: triangle
Create Table: CREATE TABLE `triangle` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`sidea` double DEFAULT NULL,
`sideb` double DEFAULT NULL,
`sidec` double GENERATED ALWAYS AS (sqrt(((`sidea` * `sidea`) + (`sideb` * `sideb`)))) VIRTUAL
-- <!!---
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
기존에 생성된 generate column 을 source column으로 해서 새로운 generate column 을 생성할 수 있습니다.
mysql> alter table triangle add column sided double GENERATED ALWAYS AS (sidec) VIRTUAL; mysql> SELECT * FROM triangle; +----+-------+-------+--------------------+--------------------+ | id | sidea | sideb | sidec | sided | +----+-------+-------+--------------------+--------------------+ | 1 | 1 | 1 | 1.4142135623730951 | 1.4142135623730951 | | 2 | 3 | 4 | 5 | 5 | | 3 | 6 | 8 | 10 | 10 | +----+-------+-------+--------------------+--------------------+
다만, generate 컬럼은 auto_increment 컬럼을 대상으로는 생성할 수 없습니다.
mysql> alter table triangle add column gen_id_col double GENERATED ALWAYS AS (id) VIRTUAL; ERROR 3109 (HY000): Generated column 'gen_id_col' cannot refer to auto-increment column.
InnoDB 에서는 VIRTUAL 타입의 Generated Columns 에 Secondary indexes 생성을 할 수 있습니다.
이와 같이 Generated Columns 은 기존 컬럼을 바탕으로 지정된 수식이나 함수를 적용된 결과를 표현하며, index 를 생성해서 사용할 수 있습니다.
STORED
STORED 방식은 행을 삽입하거나 업데이트할 때 열 값이 평가되고 저장됩니다. 저장된 열에는 저장 공간이 필요하며 인덱싱할 수 있습니다.
함수나 수식에 의해서 평가 및 계산된 값이 저장되는 방식이기 때문에 실제로 저장 공간을 차지합니다.
아래는 "GENERATED ALWAYS AS (참조_컬럼명)" 이와 같은 표현식을 사용해서 참조 컬럼 내용을 그대로 사용하는 형식으로 생성시에 용량을 비교해본 내용입니다.
각 테이블은 다음과 같은 구성을 가지고 있습니다
• tb_stored_test1 : 참조 char(120) 컬럼 1개 와 char(120) 인 STORED 유형의 Generate column
• tb_stored_test2 : 참조 char(120) 컬럼 1개 일 경우
• tb_stored_test3 : 참조 char(120) 컬럼 1개 와 char(120) 인 VIRTUAL 유형의 Generate column
+-------------------+------------+---------------+ | TABLE_NAME | TABLE_ROWS | DATA_SIZE(MB) | +-------------------+------------+---------------+ | tb_stored_test1 | 491150 | 140.67 | | tb_stored_test2 | 495210 | 76.61 | | tb_stored_test3 | 495210 | 76.61 | +-------------------+------------+---------------+
VIRTUAL 타입은 저장된 값이 아닌 필요할 때마다 계산된 값으로 생성되며, STORED 타입은 이미 계산된 값을 저장하여 빠르게 조회할 수 있도록 합니다.
STORED 타입은 저장할 때마다 새로운 값을 계산하고 해당 값을 업데이트합니다.
이렇게 함으로써 사전에 계산된 값을 저장하기 때문에 다시 계산하지 않아도 빠르게 조회할 수 있습니다.
다만 컬럼 타입 과 사용되는 길이만큼 실제의 공간을 사용하기 때문에 이 부분은 고려해야 할 사항입니다.
Online DDL 수행 방식 차이
Generated Columns 컬럼을 추가하거나 삭제할 때 저장 방식인 VIRTUAL 과 STORED 타입에 따라서 Online DDL 동작이 달라지게 됩니다.
| Operation | Instant | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata |
|---|---|---|---|---|---|
Adding a STORED column |
No | No | Yes | No | No |
Modifying STORED column order |
No | No | Yes | No | No |
Dropping a STORED column |
No | Yes | Yes | Yes | No |
Adding a VIRTUAL column |
Yes | Yes | No | Yes | Yes |
Modifying VIRTUAL column order |
No | No | Yes | No | No |
Dropping a VIRTUAL column |
Yes | Yes | No | Yes | Yes |
위의 표에서 볼 수 있는 것처럼 VIRTUAL인지 STORED 인지에 따라서 INSTANT 또는 INPLACE를 사용할 수 있는지 여부가 달라집니다.
VIRTUAL 은 컬럼 추가시 INSTANT와 INPLACE 모두 사용 가능하지만, STORED는 둘다 불가능하고 COPY 방식으로 사용됩니다.
컬럼 삭제시에는 VIRTUAL은INSTANT와 INPLACE 모두 사용 가능하고, STORED는 INPLACE로만 가능합니다.
이와 같이 방식에 따라서 Online DDL 사용 가능 기능이 차이를 보입니다.
Generated Columns의 활용
Generated Columns을 유용하게 활용할 수 있는 몇 가지 케이스에 대해서 확인해보도록 하겠습니다.
1) 함수기반 인덱스 대신 사용
먼저 MySQL 5.7 버전에서 사용될 수 있는 케이스입니다.
MySQL 에서는 인덱스를 설정할 때, 컬럼의 앞의 일부만 인덱스로 설정하는 Prefix 인덱스 기능을 제공하고 있습니다.
Prefix 인덱스 기능을 통해서 컬럼의 앞 일부분을 인덱스로 생성하여 필요한 조회 요건 및 저장 공간을 더 줄일 수 있는 장점을 제공하고 있습니다.
다만 앞이 아닌 뒤나 중간일 경우에는 불가능 합니다.
이런 경우 MySQL 8.0 에서는 새로 추가된 함수 기반 인덱스를 통해서 사용할 수 있지만, MySQL 5.7 에서는 함수 기반 인덱스 기능이 아직 사용할 수 없기 때문에, 이런 경우에는 Generated Columns 를 활용하여 index 를 사용할 수 있습니다.
예제 데이터로는 다음과 같습니다.
mysql> select id,k,pad from sbtest1 limit 0,5; +----+----+-------------------------------------------------------------+ | id | k | pad | +----+----+-------------------------------------------------------------+ | 1 | 9 | 67847967377-48000963322-62604785301-91415491898-96926520291 | | 2 | 14 | 23183251411-36241541236-31706421314-92007079971-60663066966 | | 3 | 19 | 38615512647-91458489257-90681424432-95014675832-60408598704 | | 4 | 15 | 63947013338-98809887124-59806726763-79831528812-45582457048 | | 5 | 10 | 34551750492-67990399350-81179284955-79299808058-21257255869 | +----+----+-------------------------------------------------------------+
pad 컬럼의 마지막 11자리 부분만을 검색 조건으로 하는 인덱스를 생성한다고 하면 다음과 같이 Generated Columns 생성과 인덱스를 생성할 수 있습니다.
-- 컬럼 추가 mysql> alter table sbtest1 add column gen_pad_col varchar(20) GENERATED ALWAYS AS (substr(pad,49,11)) VIRTUAL; -- 인덱스 추가 mysql> alter table sbtest1 add index idx_gen_pad_col(gen_pad_col);
컬럼 추가와 인덱스 추가가 완료되었기 때문에 실행 계획을 살펴보도록 하겠습니다.
mysql> explain select id,k,pad,gen_pad_col from sbtest1 where gen_pad_col='60408598704'; +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | sbtest1 | ref | idx_gen_pad_col | idx_gen_pad_col | 83 | const | 1 | 100.00 | NULL | +----+-------------+---------+------+-----------------+-----------------+---------+-------+------+----------+-------+
위의 내용과 같이 인덱스를 정상적으로 사용할 수 있음을 알 수 있습니다.
테이블간 조인(Join)시에도 사용할 수 있습니다.
예를 들어 sqrt 함수를 통해서 계산된 결과를 다른 테이블(sbtest2)에 입력한 데이터와 조인을 해야 하는 경우의 케이스를 예로 들어 보도록 하겠습니다.
sqrt(((`k` * `k`) + (`k` * `k`)))
• 조인할 예제 테이블 생성(sbtest2)
mysql> create table sbtest2 (
`id` int NOT NULL AUTO_INCREMENT,
col_sqrt double,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
mysql> insert into sbtest2(col_sqrt)
select sqrt(((`k` * `k`) + (`k` * `k`))) from sbtest1 ;
-- 조인 할 테이블(2)
mysql> select * from sbtest2 limit 0,5;
+----+--------------------+
| id | col_sqrt |
+----+--------------------+
| 1 | 5.477225575051661 |
| 2 | 6.164414002968976 |
| 3 | 6 |
| 4 | 10.954451150103322 |
| 5 | 9.486832980505138 |
+----+--------------------+
-- 조인 할 테이블(1)
mysql> select id,k,pad from sbtest1 limit 0,5;
+----+----+-------------------------------------------------------------+
| id | k | pad |
+----+----+-------------------------------------------------------------+
| 1 | 9 | 67847967377-48000963322-62604785301-91415491898-96926520291 |
| 2 | 14 | 23183251411-36241541236-31706421314-92007079971-60663066966 |
| 3 | 19 | 38615512647-91458489257-90681424432-95014675832-60408598704 |
| 4 | 15 | 63947013338-98809887124-59806726763-79831528812-45582457048 |
| 5 | 10 | 34551750492-67990399350-81179284955-79299808058-21257255869 |
+----+----+-------------------------------------------------------------+
위와 같이 sbtest2 테이블의 col_sqrt 는 sbtest1 테이블의 k 컬럼 값을 통해서 계산되어 입력된 값입니다.
조인을 하기 위해서 참조되었던 sbtest1 테이블에 Generated Column 과 인덱스를 추가하도록 하겠습니다.
-- 컬럼 추가 mysql> alter table sbtest1 add column gen_k double GENERATED ALWAYS AS (sqrt(((`k` * `k`) + (`k` * `k`)))) VIRTUAL; -- 인덱스 추가 mysql> alter table sbtest1 add index idx_gen_k(gen_k);
이제 조인 실행 계획을 확인해보도록 하겠습니다.
mysql> explain select a.id,a.k,a.gen_pad_col,b.col_sqrt from sbtest1 a join sbtest2 b on a.gen_k = b.col_sqrt and b.id=3; +----+-------------+-------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | a | ref | idx_gen_k | idx_gen_k | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+-------+---------------+-----------+---------+-------+------+----------+-------+
조인에 인덱스가 정상적으로 사용되고 있음을 확인할 수 있습니다.
이와 같이 함수 기반 인덱스가 지원되지 않은 MySQL 5.7이나 또는 8.0에서도 함수 기반 인덱스 대체하여 사용할 수 있습니다.
2) 함수나 수식이 적용된 컬럼
Generated Column의 기본적인 사용 유형인 함수나 수식이 적용된 새로운 컬럼을 이용하는 방법입니다.
이 부분은 활용 및 사용할 수 있는 방안이 다양하게 있을 것입니다.
2-1) 수식 계산
먼저 간단한 예시로 마이그레이션이나 기타 작업 시 작업 전 값, 작업 후 값을 입력 후 차이가 있는지를 확인하는 데이터를 테이블에 입력한다고 할 경우 아래와 같은 예제 테이블을 사용할 수 있습니다.
mysql> CREATE TABLE migration_progress_count (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`table_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '테이블 이름',
`source_cnt` bigint DEFAULT NULL,
`target_cnt` bigint DEFAULT NULL,
diff bigint GENERATED ALWAYS AS ((target_cnt - source_cnt)) STORED,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
mysql> insert into migration_progress_count(table_name,source_cnt) values('A',5);
mysql> select * from migration_progress_count;
+----+------------+------------+------------+------+
| id | table_name | source_cnt | target_cnt | diff |
+----+------------+------------+------------+------+
| 1 | A | 5 | NULL | NULL |
+----+------------+------------+------------+------+
먼저 source count 값을 입력하였고, 아직 target count 와 2개의 차이(diff) 값은 입력이 되지 않은 상태입니다.
target count 값을 update 해보도록 하겠습니다.
mysql> update migration_progress_count set target_cnt=5 where table_name='A'; mysql> select * from migration_progress_count; +----+------------+------------+------------+------+ | id | table_name | source_cnt | target_cnt | diff | +----+------------+------------+------------+------+ | 1 | A | 5 | 5 | 0 | +----+------------+------------+------------+------+
간단한 예제이지만, 작업 후 값을 입력하고 차이(비교, diff) 를 확인해서 다시 입력하기 위해서는 쿼리가 조금 더 길어진다든가, 입력 후 diff 값을 계산 후 update 하는 과정 것처럼 한 두번의 과정이 더 수행이 될 수도 있습니다.
그에 비해서 Generated Column 을 사용하면 비교적 간편하게 사용할 수 있습니다.
2-2) JSON 데이터에 대해서 활용
JSON 데이터를 사용시에 특정 attribute을 컬럼 형태로 사용하고자 하는 요건이 있을 수 있거나, 사용 편의상 컬럼 형태로 사용하고자 할 수 있습니다.
다음과 같은 JSON 데이터를 가지고 있는 테이블이 있을 경우에 대해서 설명하도록 하겠습니다.
mysql> select * from latest_event;
+----+---------------------------------------------------------------------+
| id | event_json_string |
+----+---------------------------------------------------------------------+
| 1 | {"Date": "2023-03-05T18:00:00.472000+00:00", "EventId": "E-ID-123"} |
| 2 | {"Date": "2023-03-06T18:00:00.472000+00:00", "EventId": "E-ID-456"} |
| 3 | {"Date": "2023-03-07T18:00:00.472000+00:00", "EventId": "E-ID-789"} |
+----+---------------------------------------------------------------------+
아래와 같이 2개의 Generated Column을 추가 후에 조회해보도록 하겠습니다.
mysql> alter table latest_event add column event_id varchar(100)
GENERATED ALWAYS AS (event_json_string->>"$.EventId") VIRTUAL not null ;
mysql> alter table latest_event add column event_created_dt datetime
GENERATED ALWAYS AS (date_format(event_json_string->>"$.Date", '%Y-%m-%d %T')) VIRTUAL not null ;
mysql> select event_id,event_created_dt,event_json_string
from latest_event;
+----+----------+---------------------+---------------------------------------------------------------------+
| id | event_id | event_created_dt | event_json_string |
+----+----------+---------------------+---------------------------------------------------------------------+
| 1 | E-ID-123 | 2023-03-05 18:00:00 | {"Date": "2023-03-05T18:00:00.472000+00:00", "EventId": "E-ID-123"} |
| 2 | E-ID-456 | 2023-03-06 18:00:00 | {"Date": "2023-03-06T18:00:00.472000+00:00", "EventId": "E-ID-456"} |
| 3 | E-ID-789 | 2023-03-07 18:00:00 | {"Date": "2023-03-07T18:00:00.472000+00:00", "EventId": "E-ID-789"} |
+----+----------+---------------------+---------------------------------------------------------------------+
2개의 추가한 Generated Column 에서는 입력된 JSON 특정 attribute 만 추출하여 표현하고 있으며, 일반 컬럼의 데이터처럼 사용하고 있습니다.
필요한 요건에 의해서 또는 테이블 조회 시 조회 편의성 등으로 이와 같이 복잡한 JSON 중에서 특정 attribute 만 컬럼으로 표현해서 사용할 수 있습니다.
MySQL의 JSON 컬럼에는 일반 인덱스를 직접 사용할 수 없습니다. 기본적인 방법으로 인덱싱 하려면 열을 간접적으로 참조하여 정보를 추출하는 Generated Columns을 정의한 다음, 해당 컬럼에 일반 인덱스를 생성해서 사용해야 합니다.
참고 정보로 MySQL 8.0.17 버전 이상에서 InnoDB 스토리지 엔진은 JSON 배열의 Multi-Valued Indexes(다중 값 인덱스)를 지원합니다.
Multi-Valued Indexes(다중 값 인덱스)를 통해서 JSON 배열 데이터에 대해서 인덱싱 할 수 있습니다.
일부 제약사항은 있으며 다음 함수가 WHERE 절에 지정된 경우 Multi-Valued Indexes(다중 값 인덱스)를 사용하여 레코드를 가져올 수 있습니다.
- MEMBER OF()
- JSON_CONTAINS()
- JSON_OVERLAPS()
이와 관련해서는 다음 포스팅을 참조하시면 됩니다.
위의 예제와 같이 JSON에 대해서 조회 방식의 편리성을 위해서 특정 attribute를 Generated Columns 으로 생성하였다면 이전의 설명과 같이 인덱스를 생성하여 사용할 수 있습니다.
먼저 인덱스 생성전에 실행계획을 살펴보면 다음과 같이 Full Scan 을 하는 것을 확인할 수 있습니다.
mysql> explain select * from latest_event where event_id='E-ID-456'; +----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | latest_event | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------------+------+---------------+------+---------+------+------+----------+-------------+
생성한 Generated Columns 에 인덱스를 생성후에 다시 실행계획을 살펴보도록 하겠습니다.
mysql> alter table latest_event add index idx_event_id(event_id); mysql> explain select * from latest_event where event_id='E-ID-456'; +----+-------------+--------------+------+---------------+--------------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------+---------------+--------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | latest_event | ref | idx_event_id | idx_event_id | 402 | const | 1 | 100.00 | NULL | +----+-------------+--------------+------+---------------+--------------+---------+-------+------+----------+-------+
예상과 같이 인덱스를 사용하여 실행되는 것을 확인할 수 있습니다. MySQL 에서 JSON을 사용할 경우 Generated Columns 를 사용하면 이와 같이 여러 좋은 활용할 수 있는 점이 있습니다.
3) 다른 timezone 값을 표현
DB에 설정된 timezone 이 Asia/Seoul 에 따라서 현재 한국 시간이 입력된 datetime 컬럼 타입의 컬럼 값에 대해서 UTC 값을 표현하는 컬럼이 추가로 생성하여 사용하는 예제입니다.
테이블은 아래와 같이 생성하도록 하겠습니다.
create table audit_record ( id bigint unsigned not null auto_increment comment '순번 id', audit_data varchar(500), time_record datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, event_id_utc datetime generated always as (CONVERT_TZ(time_record,'+09:00','+00:00')), primary key (id) );
참조 되는 컬럼 datetime 컬럼 타입의 컬럼이며, Generated Column 은 CONVERT_TZ 을 이용하여 timezone 을 변경하는 표현식을 사용하고 있습니다.
데이터를 입력하고, 조회를 해보도록 하겠습니다.
mysql> insert into audit_record(audit_data) values ('AAAAAA');
mysql> select * from audit_record;
+----+------------+---------------------+---------------------+
| id | audit_data | time_record | event_id_utc |
+----+------------+---------------------+---------------------+
| 1 | AAAAAA | 2023-03-20 04:47:34 | 2023-03-19 19:47:34 |
+----+------------+---------------------+---------------------+
조회 결과와 같이 CONVERT_TZ 함수가 적용되어 UTC 시간이 표현이 되고 있습니다.
이와 같이 다양한 방법으로 Generated Columns 을 활용할 수 있으며, 활용 케이스는 아주 다양할 것으로 예상합니다.
이번 포스팅에서는 MySQL의 Generated Columns 기능에 대한 내용과 사용 몇 가지의 사용 케이스에 대해서 확인해보았습니다.
이번 포스팅 여기서 포스팅은 여기서 마무리하도록 하겠습니다.
긴 글 읽어 주셔서 감사합니다.
Reference
Reference URL
• mysql.com/create-table-generated-columns
• mysql.com/indexing-json-documents-via-virtual-columns
연관된 다른 글
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
