[MySQL] ALTER TABLE과 Generated Columns

Generated Column(생성된 컬럼)에 허용되는 ALTER TABLE 연산은 ADD, MODIFY 및 CHANGE입니다.

 

• Generated Column을 추가 할 수 있습니다.

CREATE TABLE t1 (c1 INT);
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

 

• Generated Column의 데이터 유형 및 표현식을 수정할 수 있습니다.

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
ALTER TABLE t1 MODIFY COLUMN c2 TINYINT GENERATED ALWAYS AS (c1 + 5) STORED;

 

• Generated Column은 다른 컬럼이 참조하지 않는 경우 이름을 바꾸거나 삭제할 수 있습니다.

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
ALTER TABLE t1 CHANGE c2 c3 INT GENERATED ALWAYS AS (c1 + 1) STORED;
ALTER TABLE t1 DROP COLUMN c3;

 

• 가상 Generated Column은 저장된 Generated Column으로 변경하거나 그 반대로 변경할 수 없습니다. 이 문제를 해결하려면 컬럼을 삭제 한 다음 새 정의로 추가합니다.

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL);
ALTER TABLE t1 DROP COLUMN c2;
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

 

• Nongenerated columns(생성되지 않은 컬럼)은 저장되도록 변경할 수 있지만 가상 생성 컬럼은 변경할 수 없습니다.

CREATE TABLE t1 (c1 INT, c2 INT);
ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

 

• 저장되었지만 가상 Generated column이 아닌것은 Nongenerated column로 변경 될 수 있습니다. 저장된 생성 값(generated values)은 생성되지 않은 컬럼의 값이됩니다.

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
ALTER TABLE t1 MODIFY COLUMN c2 INT;

 

• ADD COLUMN은 서버에서 식을 평가해야하므로 저장된 컬럼 (임시 테이블을 사용하지 않고 수행됨)에 대한 내부 작업이 아닙니다. 저장된 컬럼의 경우 인덱싱 변경이 in-place방식에서 수행되고 표현식(컬럼에 정의된 생성 방식)변경이 제자리(in-place방식)에서 수행되지 않습니다. 컬럼 주석에 대한 변경이 제자리에서 수행됩니다.

 

• 파티션되지 않은 테이블의 경우 ADD COLUMN 및 DROP COLUMN은 가상 컬럼에 대한 내부 작업입니다. 그러나 가상 컬럼 추가 또는 삭제는 다른 ALTER TABLE 조작과 결합하여 수행할 수 없습니다.

파티션을 나눈 테이블의 경우 ADD COLUMN 및 DROP COLUMN은 가상 컬럼에 대한 in-place 작업이 아닙니다.

 

• InnoDB는 가상 generated column에서 보조 인덱스를 지원합니다. 가상 generated column에서 보조 인덱스를 추가하거나 삭제하는 것은 in-place 작업입니다.

 

• VIRTUAL generated column이 테이블에 추가되거나 수정될 때 생성된 컬럼 식에 의해 계산되는 값이 해당 컬럼의 범위를 벗어난 것은 아닙니다. 이로 인해 일관성 없는 데이터가 반환되고 예기치 않게 명령문이 실패 할 수 있습니다. 이러한 컬럼에 대해 유효성 검사가 발생하는지 여부를 제어하기 위해 ALTER TABLE은 WITHOUT VALIDATION 및 WITH VALIDATION 명령문을 지원합니다.

-WITHOUT VALIDATION (술어(clause)가 지정되지 않은 경우 기본값)을 사용하면 in-place 작업이 수행되고 (가능한 경우) 데이터 무결성이 확인되지 않으며 명령문이 더 빨리 완료됩니다. 그러나 나중에 테이블에서 읽을때 값이 범위를 벗어난 경우 컬럼에 대한 경고 또는 오류를 보고할 수 있습니다.

-WITH VALIDATION을 사용하면 ALTER TABLE이 테이블을 복사합니다. 범위를 벗어났거나 다른 오류가 발생하면 명령문이 실패합니다. 테이블 복사가 수행되기 때문에 명령문이 더 오래 걸립니다.

 

WITHOUT VALIDATION 및 WITH VALIDATION은 ADD COLUMN, CHANGE COLUMN 및 MODIFY COLUMN 연산에서만 허용됩니다. 그렇지 않으면 ER_WRONG_USAGE 오류가 발생합니다.

 

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

 

• col_name컬럼의 기본값을 변경하는 ALTER TABLE 문은 DEFAULT (col_name)을 사용하여 컬럼을 참조하는 generated column 표현식의 값을 변경할 수도 있습니다. 이러한 이유로 MySQL 5.7.13부터 generated column 표현식이 DEFAULT()를 사용하는 경우 컬럼 정의를 변경하는 ALTER TABLE 작업으로 인해 테이블이 다시 작성됩니다.