[MySQL] CREATE TABLE과 Generated Columns

CREATE TABLE은 생성된 컬럼의 특정 사항 지원합니다. 생성된 컬럼의 값은 컬럼 정의에 포함된 표현식에 의해서 표현식대로 표현되거나 계산됩니다.

 

생성된 컬럼은 MySQL NDB Cluster 7.5.3부터 NDB 스토리지 엔진에서 지원됩니다.

 

다음 간단한 예제는 sidea 및 sideb 컬럼에 직각 삼각형 변의 길이를 저장하고 sidec(다른 면의 제곱합에 대한 제곱근입니다)의 빗변 길이를 계산하는 테이블을 보여줍니다.

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

 

표에서 선택하면 다음 결과가 생성됩니다.

mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

삼각형 테이블을 사용하는 모든 애플리케이션은 이를 계산하는 표현식을 지정하지 않고도 빗변 값에 액세스할 수 있습니다.

 

생성된 컬럼 정의에는 다음 구문이 있습니다.

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

AS (expr)는 컬럼이 생성되었음을 표시하고 컬럼 값을 계산하는데 사용되는 표현식을 정의합니다. AS 앞에 GENERATED ALWAYS가 추가되어 컬럼의 생성된 특성을 보다 명시적으로 만들 수 있습니다. 식에서 허용되거나 금지된 구문은 밑에서 설명합니다.

 

VIRTUAL 또는 STORED 키워드는 컬럼 값이 저장되는 방법을 나타내며 컬럼 사용에 영향을 줍니다.

• VIRTUAL : 컬럼 값이 저장되지 않지만 행을 읽을 때 BEFORE 트리거 직후에 평가됩니다. 가상 컬럼은 스토리지를 차지하지 않습니다.

InnoDB는 가상 컬럼에서 보조 인덱스를 지원합니다. BEFORE트리거를 수행해야 하는만큼 데이터 볼륨에 따라 CPU성능에 영향을 미칠수도 있습니다.

• STORED : 행이 삽입되거나 업데이트될 때 컬럼 값이 평가되고 저장됩니다. 저장된 컬럼에는 저장 공간이 필요하며 인덱싱 할 수 있습니다.

키워드가 지정되지 않은 경우 기본값은 VIRTUAL입니다.

테이블 내에서 VIRTUAL 및 STORED 컬럼을 혼합 할 수 있습니다.

 

컬럼이 인덱싱되었는지 또는 NULL인지 여부를 표시하거나 주석을 제공할 수 있습니다.

생성된 컬럼 표현식은 다음 규칙을 따라야합니다. 식에 허용되지 않는 구문이 포함된 경우 오류가 발생합니다.

• 리터럴, 결정적 내장 함수 및 연산자가 허용됩니다. 테이블의 동일한 데이터가 주어지면 여러 호출이 연결된 사용자와 관계없이 동일한 결과를 생성하는 경우 함수는 결정적입니다.

비결정적이고 이 정의에 실패하는 함수의 예 : CONNECTION_ID(), CURRENT_USER(), NOW().

• 저장된 함수 및 사용자 정의 함수는 허용되지 않습니다.

• 저장 프로시저 및 함수 매개 변수는 허용되지 않습니다.

• 변수(시스템 변수, 사용자 정의 변수 및 저장된 프로그램 로컬 변수)는 허용되지 않습니다.

• 하위 쿼리는 허용되지 않습니다.

• 생성된 컬럼 정의는 생성된 다른 컬럼을 참조할 수 있지만 테이블 정의에서 이전에 발생한 컬럼만 참조 할 수 있습니다. 생성된 컬럼 정의는 정의가 이전 또는 이후에 발생하는지 여부에 관계없이 테이블의 기본 (생성되지 않은)컬럼을 참조할 수 있습니다.

• AUTO_INCREMENT 속성은 생성된 컬럼 정의에서 사용할 수 없습니다.

• AUTO_INCREMENT 열은 생성된 컬럼 정의에서 기본 컬럼으로 사용할 수 없습니다.

• MySQL 5.7.10부터 표현식 평가로 인해 잘 리거나 함수에 잘못된 입력이 제공되면 CREATE TABLE 문이 오류와 함께 종료되고 DDL 작업이 거부됩니다.

 

표현식이 선언된 컬럼 유형과 다른 데이터 유형으로 평가되면 선언된 유형에 대한 암시적 강제 변환이 일반적인 MySQL 유형 변환 규칙에 따라 발생합니다.

 

참고사항

표현식의 구성 요소가 SQL 모드에 따라 달라지는 경우, 사용중인 모든 SQL 모드가 동일하지 않으면 테이블의 다른 사용에 대해 다른 결과가 발생할 수 있습니다.

 

CREATE TABLE ... LIKE의 경우 대상 테이블은 원본 테이블에서 생성된 컬럼 정보를 유지합니다.

 

CREATE TABLE ... SELECT의 경우 대상 테이블은 selected-from 테이블의 컬럼이 생성된 컬럼인지 여부에 대한 정보를 유지하지 않습니다. SELECT 부분 명령은 대상 테이블에서 생성된 컬럼에 값을 할당 할 수 없습니다.

 

Generated Column에 의한 파티셔닝이 허용됩니다.

 

저장된 generated column에 대한 외래키 제약 조건은 CASCADE, SET NULL 또는 SET DEFAULT를 ON UPDATE 참조 작업으로 사용할 수 없으며 SET NULL 또는 SET DEFAULT를 ON DELETE 참조 작업으로 사용할 수 없습니다.

 

저장된 Generated Column의 프리머리 컬럼에 대한 외래키 제약 조건은 CASCADE, SET NULL 또는 SET DEFAULT를 ON UPDATE 또는 ON DELETE 참조 작업으로 사용할 수 없습니다.

 

외래 키 제약 조건은 가상 생성 컬럼을 참조 할 수 없습니다.

 

트리거는 NEW.col_name을 사용하거나 OLD.col_name을 사용하여 생성된 컬럼을 참조 할 수 없습니다.

 

INSERT, REPLACE 및 UPDATE의 경우 생성된 컬럼이 명시 적으로 삽입, 대체 또는 업데이트되는 경우 허용되는 유일한 값은 DEFAULT입니다.

 

뷰에서 생성된 컬럼은 할당이 가능하므로 업데이트 가능한 것으로 간주됩니다. 그러나 이러한 컬럼이 명시 적으로 업데이트되는 경우 허용되는 유일한 값은 DEFAULT입니다.

 

Generated Column에는 다음과 같은 여러 사용 사례가 있습니다.

• 가상 생성 컬럼은 쿼리를 단순화하고 통합하는 방법으로 사용할 수 있습니다. 복잡한 조건을 생성된 컬럼으로 정의하고 테이블의 여러 쿼리에서 참조하여 모두 정확히 동일한 조건을 사용하도록할 수 있습니다.

• 저장된 Generated Column은 즉석에서 계산하는데 비용이 많이 드는 복잡한 조건에 대해 구체화된 캐시로 사용할 수 있습니다.

• Generated Column은 기능 색인을 시뮬레이션 할 수 있습니다. 생성된 컬럼을 사용하여 기능 표현식을 정의하고 색인을 생성합니다. 이는 JSON컬럼과 같이 직접 인덱싱 할 수 없는 유형의 컬럼 작업에 유용 할 수 있습니다.

• 저장된 Generated Column의 경우 이 방법의 단점은 값이 두 번 저장된다는 것입니다. 하나는 Generated Column의 값으로 한 번, 또다른 하나는 인덱스에 한 번.

• Generated Column이 인덱싱된 경우 최적화 프로그램은 컬럼 정의와 일치하는 쿼리 식을 인식하고 쿼리가 이름으로 컬럼을 직접 참조하지 않더라도 쿼리 실행 중에 컬럼의 인덱스를 적절하게 사용합니다.

 

예제:

테이블 t1에 first_name 및 last_name 컬럼이 포함되어 있고 애플리케이션이 다음과 같은 표현식을 사용하여 자주 전체 이름을 구성한다고 가정합니다.

 

 

표현식 작성을 피하는 한 가지 방법은 t1에 v1 뷰를 생성하는 것입니다. 그러면 표현식을 사용하지 않고 직접 full_name을 선택할 수 있도록 하여 애플리케이션을 단순화합니다.

CREATE VIEW v1 AS
SELECT *, CONCAT(first_name,' ',last_name) AS full_name FROM t1;
SELECT full_name FROM v1;

 

또한 Generated Column을 사용하면 view table을 정의할 필요없이 애플리케이션이 full_name을 직접 선택할 수 있습니다.

CREATE TABLE t1 (
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  full_name VARCHAR(255) AS (CONCAT(first_name,' ',last_name))
);
SELECT full_name FROM t1;