MySQL Ver. 5.7.6 부터 테이블을 생성할 때 Generated Columns를 만들 수 있다. Generated Column은 2가지의 방식을 지원한다. VIRTUAL 방식과 STORED 방식이다.
VIRTUAL 방식
데이터를 저장하지 않고 정의만 정의하여 딕셔너리만 추가하는 방식이다. 해당 컬럼을 읽으려고 할 때, 보여주어야 하는 데이터에 대한 작업이 진행되는데, BEFORE TRIGGER 동작 후에 계산 작업이 진행된다. 그래서, 이 방식으로 사용하게 되면 저장 공간은 늘어나지 않지만, SELECT 구문 실행 시 마다 계산이 일어나기 때문에 CPU를 좀 더 많이 사용하게 된다. MySQL Ver. 5.7.8 부터 해당 컬럼을 사용하여 인덱스를 생성하는 것이 가능하다.
STORED 방식
딕셔너리 정보 뿐 아니라 데이터도 같이 저장하는 방식으로 데이터가 입력되거나 수정될 때 해당 컬럼의 데이터도 같이 갱신된다. 그래서, 이 방식으로 사용하게 되면 저장공간이 더 늘어나게 되고, DML 시 마다 이중으로 작업이 늘어나게 된다.
아무것도 지정하지 않는 경우 사용자는 VIRTUAL 타입으로 Generated Columns를 생성하게 된다.
사용방법
생성 구문은 다음과 같다.
col_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
[[NOT] NULL] [[PRIMARY] KEY]
(expression)에는 컬럼을 생성하는 계산식이 들어가게 된다. [GENERATED ALWAYS]는 다른 의미는 없고 명시적으로 생성된다는 의미만을 지닌 용어로 사용된다.
-- 하나의 테이블에 두가지 타입의 Generated Column을 만들 수 있다.
-- Literal, Deterministic Built-in 함수, Operator는 사용할수 있다. 즉, Non-Deterministic한 내부 함수는 사용할 수 없다.
-- Subquery, parameter, variable, stored function, user-defined function은 허용되지 않는다.
-- Generated Column에 AUTO_INCREMENT 속성을 추가할 수 없다.
-- Generated Column안에서 참조하는 컬럼은 AUTO_INCREMENT 속성을 가지지 않아야 한다.
-- Generated Column을 사용한 파티션이 가능하다.
-- Stored Column 컬럼을 FK로 설정할 때, ON DELETE SET NULL, ON UPDATE SET NULL, ON UPDATE CASCADE를 사용할 수 없다.
-- Virtual Column을 FK 제약조건의 부분으로 참조되어질 수 없다.
-- DML 구문에서 해당 Gnerated Column에 값을 넣고자 하면, 해당 컬럼은 기본 값으로 값이 채워진다.
-- CREATE TABLE ... LIKE 구문을 사용하면 기존 테이블의 Generated Columns 정보를 그대로 가져와서 생성한다.
-- CREATE TABLE ... SELECT 구문을 사용하면 기존 테이블의 Generated Columns 정보를 그래로 가져와서 생성하지 못한다. 추출되는 데이터의 속성으로 컬럼 정보가 정의된다.
기본 사용 예제
위에 기술된 내용이 정말로 제대로 실행되는지 몇가지 테스트를 해보도록 하자.
다음과 같이 실행해 보면 두가지 타입의 Generated Column을 하나의 테이블에서 생성하여 사용하는것이 가능하다.
[root@my57-db1][test 14:23:22] > show create table tri;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tri | CREATE TABLE `tri` (
`a` double DEFAULT NULL,
`b` double DEFAULT NULL,
`c` double GENERATED ALWAYS AS (sqrt(((`a` * `a`) + (`b` * `b`)))) VIRTUAL,
`d` double GENERATED ALWAYS AS ((`a` + `b`)) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Deterministic 하지 않는 함수를 사용하면 다음과 같이 에러가 발생한다.
[root@my57-db1][test 14:23:28] > create table tri_1 ( a double, b double , creat_time datetime as (now()) );
ERROR 3102 (HY000): Expression of generated column 'creat_time' contains a disallowed function.
Generated Column을 참조하는 Generated Column 생성도 가능하다.
[root@my57-db1][test 14:26:52] > show create table tri_1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tri_1 | CREATE TABLE `tri_1` (
`a` double DEFAULT NULL,
`b` double DEFAULT NULL,
`c` double GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
`d` double GENERATED ALWAYS AS ((`c` * `c`)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Auto-Increment 속성을 가진 컬럼을 참조하게 생성 구문을 작성하면 다음과 같은 에러가 발생한다.
[root@my57-db1][sakila 14:38:24] > create table test.tri2 ( id int auto_increment, b int, c int as (id +1));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
CREATE TABLE ...LIKE와 SELECT가 어떻게 다르게 진행되는지 확인해 보도록 하자.
[root@my57-db1][test 14:23:22] > show create table tri;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tri | CREATE TABLE `tri` (
`a` double DEFAULT NULL,
`b` double DEFAULT NULL,
`c` double GENERATED ALWAYS AS (sqrt(((`a` * `a`) + (`b` * `b`)))) VIRTUAL,
`d` double GENERATED ALWAYS AS ((`a` + `b`)) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@my57-db1][test 14:50:44] > create table tri_cp1 like tri ;
Query OK, 0 rows affected (0.02 sec)
[root@my57-db1][test 14:51:08] > create table tri_cp2 select * from tri;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
[root@my57-db1][test 14:51:21] > show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.00 sec)
[root@my57-db1][test 14:51:53] > set global enforce_gtid_consistency= OFF;
Query OK, 0 rows affected (0.00 sec)
[root@my57-db1][test 14:52:13] > create table tri_cp2 select * from tri;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
[root@my57-db1][test 14:52:17] > show create table tri_cp1;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tri_cp1 | CREATE TABLE `tri_cp1` (
`a` double DEFAULT NULL,
`b` double DEFAULT NULL,
`c` double GENERATED ALWAYS AS (sqrt(((`a` * `a`) + (`b` * `b`)))) VIRTUAL,
`d` double GENERATED ALWAYS AS ((`a` + `b`)) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@my57-db1][test 14:52:24] > show create table tri_cp2;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tri_cp2 | CREATE TABLE `tri_cp2` (
`a` double DEFAULT NULL,
`b` double DEFAULT NULL,
`c` double DEFAULT NULL,
`d` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@my57-db1][test 14:52:34] >
두개의 구문이 같은 듯 하지만 다르게 동작하는 것을 확인할 수 있다.
트리거와 Generated Columns
트리거를 생성할 때 Generated Columns를 사용하여도 문제가 없는지 간단히 테스트 해보도록 하자.
[root@my57-db1][test 15:32:45] > create table tri2 (
-> `a` double DEFAULT NULL,
-> `b` double DEFAULT NULL,
-> `c` double GENERATED ALWAYS AS (sqrt(((`a` * `a`) + (`b` * `b`)))) VIRTUAL,
-> `d` double );
Query OK, 0 rows affected (0.03 sec)
[root@my57-db1][test 15:36:05] > create trigger tri2_ins before insert on test.tri2 for each row
-> begin
-> if new.c > 0 then
-> set new.d = 1;
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)
[root@my57-db1][test 15:36:57] > insert into tri2 ( a, b ) value ( 1 , 2);
-> //
Query OK, 1 row affected (0.01 sec)
[root@my57-db1][test 15:37:54] > delimiter ;
[root@my57-db1][test 15:38:01] > select * from tri2;
+------+------+------------------+------+
| a | b | c | d |
+------+------+------------------+------+
| 1 | 2 | 2.23606797749979 | 1 |
+------+------+------------------+------+
1 row in set (0.00 sec)
[root@my57-db1][test 15:38:07] > delimiter //
[root@my57-db1][test 15:44:55] > create trigger tri2_ins2 before insert on test.tri2 for each row
-> begin
-> if new.c > 0 then
-> set new.d = new.c +1;
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)
생성하는데에 아무런 문제가 없는 것을 확인할 수 있다. 하지만, 레퍼런스에서는 사용할 수 없다고 정리되어있다. (Triggers cannot use NEW.col_name or use OLD.col_name to refer to generated columns.) 현재로서는 레퍼런스의 버그가 아닐까 싶다.
파티션과 Generated Columns
Generated Columns를 키로 하여 파티션을 구성하는데에 문제가 되는지 아닌지 예제를 통해 확인해 보도록 하자.
RANGE Partition을 생성해 보면 다음과 같이 생성됨을 확인할 수 있다.
[root@my57-db1][test 17:59:55] > show create table tb_part;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_part | CREATE TABLE `tb_part` (
`a` varchar(20) COLLATE utf8mb4_bin NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) GENERATED ALWAYS AS ((`b` - 10)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (c)
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
LIST Partition을 생성해 보면 다음과 같이 생성됨을 확인할 수 있다.
[root@my57-db1][test 18:06:35] > show create table tb_part2;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_part2 | CREATE TABLE `tb_part2` (
`a` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`b` int(11) NOT NULL,
`c` int(11) GENERATED ALWAYS AS ((`b` % 4)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY LIST (c)
(PARTITION r0 VALUES IN (0,2) ENGINE = InnoDB,
PARTITION r1 VALUES IN (1,3) ENGINE = InnoDB) */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
HASH Partition을 생성해 보면 다음과 같이 생성됨을 확인할 수 있다.
[root@my57-db1][test 18:11:45] > show create table tb_part3;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_part3 | CREATE TABLE `tb_part3` (
`a` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`c` int(11) GENERATED ALWAYS AS (ascii(`a`)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY HASH (c) */ |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
인덱스와 Generated Columns
MySQL Ver. 5.7.8 부터 InnoDB의 Secondary 인덱스의 키로 Virtual 타입의 Generated Column을 사용하는 것이 가능하다. 이때 Generated Column은 Virtual 타입이지만, Secondary Index에서는 해당 값이 저장된다. 인덱스의 Covering Index 기능을 사용하기 위해서 그러하다. 그래서, Virtual Column을 키로 secondary 인덱스를 만들면, 읽기위해 사용해야 하는 cpu 비용을 줄일 수 있다. ( virtual column의 값을 만들어 내기 위한 계산작업을 하지 않아도 되기 때문이다.)
-- 하나 또는 두개 이상의 Virtual 타입의 Generated Column을 사용하는 것이 가능하다.
-- 일반 컬럼과 같이 조합해서 생성하는 것도 가능하다.
-- 인덱스 생성 작업은 in-place 작업으로 진행된다.
-- Virtual 타입의 Generated Column을 키로하는 인덱스는 FK를 위한 인덱스로 사용될 수 없다.
-- FK에 참조되는 컬럼을 기본 컬럼으로 사용하는 Virtual 타입의 Generated Column은 인덱스의 키로 사용할 수 없다.
다음과 같이 간단히 생성할 수 있다.
[root@my57-db1][test 18:51:07] > show create table tb1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1 | CREATE TABLE `tb1` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` double GENERATED ALWAYS AS (sqrt(((`a` * `a`) + (`b` * `b`)))) VIRTUAL,
PRIMARY KEY (`a`),
KEY `i` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
일반 컬럼과의 조합도 가능하다.
[root@my57-db1][test 18:52:10] > show create table tb2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb2 | CREATE TABLE `tb2` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` double GENERATED ALWAYS AS (sqrt(((`a` * `a`) + (`b` * `b`)))) VIRTUAL,
PRIMARY KEY (`a`),
KEY `i` (`c`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
