MySQL에서는 생성 컬럼(Generated Column)을 활용하면 데이터를 직접 INSERT하거나 UPDATE하지 않아도, 전체 테이블을 조회할 때 자동으로 계산된 값이 화면에 나타나도록 테이블 구조를 만들 수 있습니다. 이번 글에서는 두 개의 점수 컬럼 값을 더해 평균을 내는 'Average Score' 필드를 추가하는 과정을 단계별로 살펴보겠습니다.
기본 문법
기존 테이블에 생성 컬럼을 추가하는 기본 문법은 다음과 같습니다.
ALTER TABLE yourTableName
ADD COLUMN yourColumnName yourDataType
GENERATED ALWAYS AS ((yourColumnName1 + yourColumnName2 + ... N) / N) VIRTUAL;
1단계: 테이블 생성
먼저 예제로 사용할 테이블을 만들어 보겠습니다.
mysql> CREATE TABLE demo32
-> (
-> value1 INT,
-> value2 INT
-> );
Query OK, 0 rows affected (1.42 sec)
2단계: 샘플 데이터 입력
INSERT 명령어를 사용해 몇 개의 레코드를 입력합니다.
mysql> INSERT INTO demo32 VALUES(30,60);
Query OK, 1 row affected (0.16 sec)
mysql> INSERT INTO demo32 VALUES(20,40);
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO demo32 VALUES(35,35);
Query OK, 1 row affected (0.08 sec)
3단계: 현재 데이터 확인
SELECT 문으로 저장된 레코드를 조회해 보겠습니다.
mysql> SELECT * FROM demo32;
실행 결과는 다음과 같습니다.
+--------+--------+
| value1 | value2 |
+--------+--------+
| 30 | 60 |
| 20 | 40 |
| 35 | 35 |
+--------+--------+
3 rows in set (0.00 sec)
4단계: 생성 컬럼으로 'Average Score' 필드 추가
이제 ALTER TABLE 명령어와 GENERATED ALWAYS AS 구문을 사용하여, 별도의 INSERT나 UPDATE 없이도 평균 점수가 자동으로 계산되는 가상 컬럼(Virtual Column)을 추가합니다.
mysql> ALTER TABLE demo32 ADD COLUMN `Average Score` FLOAT
-> GENERATED ALWAYS AS ((value1 + value2) / 2) VIRTUAL;
Query OK, 0 rows affected (1.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
5단계: 결과 확인
다시 SELECT 문으로 전체 테이블을 조회해 보겠습니다.
mysql> SELECT * FROM demo32;
'Average Score' 필드가 자동으로 계산되어 출력되는 것을 확인할 수 있습니다.
+--------+--------+---------------+
| value1 | value2 | Average Score |
+--------+--------+---------------+
| 30 | 60 | 45 |
| 20 | 40 | 30 |
| 35 | 35 | 35 |
+--------+--------+---------------+
3 rows in set (0.00 sec)
VIRTUAL 옵션의 의미와 장점
위 예제에서 사용한 VIRTUAL 키워드는 해당 컬럼의 값이 디스크에 물리적으로 저장되지 않고, 쿼리가 실행될 때마다 실시간으로 계산된다는 의미입니다. 이 방식은 다음과 같은 장점이 있습니다.
- 저장 공간 절약: 계산된 값이 테이블에 저장되지 않으므로 스토리지를 추가로 사용하지 않습니다.
- 데이터 일관성 유지: 원본 컬럼(value1, value2)의 값이 변경되면 평균 점수도 항상 자동으로 최신 상태를 반영합니다.
- 별도의 애플리케이션 로직 불필요: INSERT 또는 UPDATE 시 평균값을 직접 관리할 필요가 없습니다.
만약 계산된 값을 물리적으로 저장하고 싶다면 VIRTUAL 대신 STORED 옵션을 사용하면 되며, 이 경우 인덱스 생성 등에도 활용할 수 있습니다.