MySQL GENERATED 칼럼 사용법

MySQL의 GENERATED 칼럼은 다른 칼럼 값을 기반으로 자동 계산되는 칼럼으로, 가상 컬럼(Virtual) 또는 저장된 컬럼(Stored) 형태로 사용된다.

 

왜 이걸 찾아보았는가...

 

1. JSON 내부 필드 인덱스 문제

• 10개 이상의 필드를 가진 JSON 타입을 저장해야 했다.
• 근데 MySQL은 JSON 타입 내부의 특정 필드에 직접 인덱스를 걸 수 없다.
• WHERE JSON_EXTRACT(data, '$.field') = '값' 형태의 조건은 풀 테이블 스캔이 발생했다.

 

2. 해결 방법 → Generated Column을 써볼까.

• GENERATED COLUMN으로 JSON 내부 필드를 추출한 별도 칼럼을 정의해보자.
• 추출된 칼럼을 STORED 형태로 저장한 뒤 인덱스를 걸어 조회하는 테스트를 했다.

 

 

기본 문법

column_name data_type GENERATED ALWAYS AS (expression) [VIRTUAL | STORED]

 

키워드	의미
expression	계산식 (기존 컬럼 기반)
VIRTUAL	계산값을 디스크에 저장하지 않고 필요 시 계산 (기본값)
STORED	계산 결과를 디스크에 저장 (인덱스 가능)

 

예시

1. 기본 가상 컬럼

CREATE TABLE orders (
    product_price DECIMAL(10,2),
    quantity INT,
    total_amount DECIMAL(12,2) GENERATED ALWAYS AS (product_price * quantity) VIRTUAL
);

• total_amount는 자동으로 계산되고 저장되지는 않음
• INSERT 시  total_amount를 직접 입력할 수 없음

 

2. JSON 필드에서 값 추출

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON,

    sku VARCHAR(50) GENERATED ALWAYS AS (
        JSON_UNQUOTE(JSON_EXTRACT(data, '$.sku'))
    ) STORED,

    category VARCHAR(50) GENERATED ALWAYS AS (
        JSON_UNQUOTE(JSON_EXTRACT(data, '$.category'))
    ) STORED,

    created_at DATE GENERATED ALWAYS AS (
        STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(data, '$.created')), '%Y-%m-%d')
    ) STORED,

    INDEX idx_sku (sku),
    INDEX idx_category (category),
    INDEX idx_created (created_at)
);

• sku, category, created_at 값이 JSON에서 추출되어 인덱스로 활용됨
• STORED 를 사용해 인덱스 설정 가능

 

3. JSON 인덱스 활용 예시

CREATE TABLE order_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON,

    first_item VARCHAR(100)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.items[0].name')))
        STORED,

    INDEX idx_first_item (first_item)
);

INSERT INTO order_logs (data) VALUES
('{"items": [{"name": "Laptop"}, {"name": "Mouse"}]}'),
('{"items": [{"name": "Phone"}]}'),
('{"items": [{"id": 1001}]}'),
('{"note": "empty order"}');

 

3-1. 데이터 삽입 결과

+----+---------------------------------------------------+------------+
| id | data                                              | first_item |
+----+---------------------------------------------------+------------+
|  1 | {"items": [{"name": "Laptop"}, {"name":"Mouse"}]} | Laptop     |
|  2 | {"items": [{"name": "Phone"}]}                    | Phone      |
|  3 | {"items": [{"id": 1001}]}                         | NULL       |
|  4 | {"note": "empty order"}                           | NULL       |
+----+---------------------------------------------------+------------+

• first_item는 items[0].name 경로에서 추출된다.
• 경로에 해당하는 키가 없으면 NULL

 

3-2. JSON 경로 존재 여부 체크

SELECT id, JSON_CONTAINS_PATH(data, 'one', '$.items[0].name') AS exists_path
FROM order_logs;

+----+-------------+
| id | exists_path |
+----+-------------+
|  1 |           1 |
|  2 |           1 |
|  3 |           0 |
|  4 |           0 |
+----+-------------+

 

3-3. 인덱스 사용 확인

EXPLAIN SELECT * FROM order_logs WHERE first_item = 'Laptop';

• 인덱스 idx_first_item가 사용되었다.

 

3-4. 인덱스 대상이 아닌 row 확인

SELECT COUNT(*) FROM order_logs WHERE first_item IS NULL;

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

• JSON 경로가 없어 NULL이 된 행이 2건 확인됨
• 경로가 없는 row는 인덱스에 포함되지 않음