MySQL Generated Column 가상 컬럼

MySQL Generated Column이란?

MySQL 5.7에서 도입된 Generated Column(생성 컬럼)은 다른 컬럼의 값을 기반으로 자동 계산되는 가상 컬럼입니다. 직접 INSERT/UPDATE 할 수 없으며, 정의된 표현식에 따라 값이 자동으로 결정됩니다. JSON 필드 인덱싱, 계산 컬럼 캐싱, 복합 검색 조건 최적화에 핵심적인 기능입니다.

VIRTUAL vs STORED: 두 가지 타입

타입	저장	계산 시점	인덱스	적합한 용도
VIRTUAL	디스크에 저장 안 됨	읽을 때마다 계산	InnoDB에서 보조 인덱스 가능	단순 계산, JSON 추출
STORED	디스크에 물리 저장	INSERT/UPDATE 시 1회	모든 인덱스 가능	복잡한 계산, Full-Text 인덱스

-- VIRTUAL 컬럼: 디스크 공간 없음, 읽을 때 계산
CREATE TABLE products (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    price       DECIMAL(12,2) NOT NULL,
    tax_rate    DECIMAL(4,2) NOT NULL DEFAULT 0.10,
    quantity    INT NOT NULL DEFAULT 0,
    
    -- VIRTUAL: 세금 포함 가격 (읽을 때 계산)
    price_with_tax DECIMAL(12,2) AS (price * (1 + tax_rate)) VIRTUAL,
    
    -- VIRTUAL: 재고 금액
    stock_value DECIMAL(14,2) AS (price * quantity) VIRTUAL,
    
    -- STORED: 검색용 소문자 이름 (디스크에 저장)
    name_lower  VARCHAR(200) AS (LOWER(name)) STORED,
    
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 데이터 삽입: Generated Column에는 값 지정 불가
INSERT INTO products (name, price, tax_rate, quantity)
VALUES ('맥북 프로 16인치', 3690000, 0.10, 50);

-- 자동 계산된 값 확인
SELECT name, price, price_with_tax, stock_value, name_lower
FROM products;
-- | 맥북 프로 16인치 | 3690000.00 | 4059000.00 | 184500000.00 | 맥북 프로 16인치 |

JSON 컬럼 인덱싱: Generated Column의 핵심 용도

MySQL에서 JSON 필드에 직접 인덱스를 생성할 수 없습니다. Generated Column으로 JSON 값을 추출한 후 인덱싱하는 것이 공식 권장 패턴입니다:

CREATE TABLE user_profiles (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    profile     JSON NOT NULL,
    
    -- JSON에서 값 추출 → Generated Column
    email       VARCHAR(255) AS (JSON_UNQUOTE(profile->'$.email')) STORED,
    age         INT AS (profile->'$.age') VIRTUAL,
    city        VARCHAR(100) AS (JSON_UNQUOTE(profile->'$.address.city')) STORED,
    is_premium  BOOLEAN AS (profile->'$.subscription.isPremium') VIRTUAL,
    
    -- Generated Column에 인덱스 생성
    INDEX idx_email (email),
    INDEX idx_city (city),
    INDEX idx_age (age),
    
    UNIQUE KEY uk_user_id (user_id)
);

-- 데이터 삽입
INSERT INTO user_profiles (user_id, profile) VALUES
(1, '{
    "email": "user@example.com",
    "age": 28,
    "address": {"city": "서울", "district": "강남"},
    "subscription": {"isPremium": true, "plan": "annual"}
}');

-- Generated Column으로 빠른 검색 (인덱스 사용)
SELECT * FROM user_profiles WHERE email = 'user@example.com';
-- → Index Scan on idx_email

SELECT * FROM user_profiles WHERE city = '서울' AND age > 25;
-- → Index Scan

-- EXPLAIN으로 인덱스 사용 확인
EXPLAIN SELECT * FROM user_profiles WHERE email = 'user@example.com';
-- type: ref, key: idx_email, rows: 1

복합 검색 조건 최적화

CREATE TABLE orders (
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT NOT NULL,
    amount       DECIMAL(12,2) NOT NULL,
    discount     DECIMAL(12,2) NOT NULL DEFAULT 0,
    status       ENUM('pending','paid','shipped','delivered','cancelled') NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 실결제 금액 (할인 적용)
    net_amount   DECIMAL(12,2) AS (amount - discount) STORED,
    
    -- 주문 연-월 (월별 집계용)
    order_month  CHAR(7) AS (DATE_FORMAT(created_at, '%Y-%m')) STORED,
    
    -- 고액 주문 플래그
    is_high_value BOOLEAN AS (amount - discount >= 100000) VIRTUAL,
    
    -- 인덱스: 월별 + 상태별 조회 최적화
    INDEX idx_month_status (order_month, status),
    INDEX idx_net_amount (net_amount)
);

-- 월별 매출 집계 (Generated Column 활용)
SELECT order_month, status,
       COUNT(*) as order_count,
       SUM(net_amount) as total_revenue
FROM orders
WHERE order_month = '2026-03'
GROUP BY order_month, status;
-- → idx_month_status 인덱스 활용, Full Scan 없음

-- 고액 주문 검색
SELECT * FROM orders WHERE is_high_value = true AND status = 'paid';

-- vs 기존 방식 (매번 계산)
SELECT * FROM orders WHERE (amount - discount) >= 100000 AND status = 'paid';
-- → Generated Column이 더 깔끔하고, STORED면 인덱스 가능

Spring JPA + Generated Column

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false, precision = 12, scale = 2)
    private BigDecimal price;

    @Column(name = "tax_rate", nullable = false)
    private BigDecimal taxRate;

    @Column(nullable = false)
    private Integer quantity;

    // Generated Column: insertable/updatable = false 필수!
    @Column(name = "price_with_tax",
            insertable = false,
            updatable = false)
    private BigDecimal priceWithTax;

    @Column(name = "stock_value",
            insertable = false,
            updatable = false)
    private BigDecimal stockValue;

    @Column(name = "name_lower",
            insertable = false,
            updatable = false)
    private String nameLower;
}

// Repository: Generated Column을 직접 조건으로 사용
public interface ProductRepository extends JpaRepository<Product, Long> {

    // price_with_tax 기준 범위 검색
    List<Product> findByPriceWithTaxBetween(
        BigDecimal min, BigDecimal max);

    // stock_value 기준 정렬
    List<Product> findByQuantityGreaterThanOrderByStockValueDesc(
        int minQuantity);

    // name_lower로 대소문자 무시 검색
    @Query("SELECT p FROM Product p WHERE p.nameLower LIKE :keyword")
    List<Product> searchByName(@Param("keyword") String keyword);
}

TypeORM + Generated Column

// TypeORM 엔티티
@Entity('orders')
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ name: 'user_id' })
  userId: number;

  @Column({ type: 'decimal', precision: 12, scale: 2 })
  amount: number;

  @Column({ type: 'decimal', precision: 12, scale: 2, default: 0 })
  discount: number;

  @Column({ type: 'enum', enum: OrderStatus })
  status: OrderStatus;

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  // Generated Column: select만 가능, insert/update 불가
  @Column({
    name: 'net_amount',
    type: 'decimal',
    precision: 12,
    scale: 2,
    insert: false,    // INSERT 시 무시
    update: false,    // UPDATE 시 무시
    nullable: true,
  })
  netAmount: number;

  @Column({
    name: 'order_month',
    type: 'char',
    length: 7,
    insert: false,
    update: false,
    nullable: true,
  })
  orderMonth: string;
}

// QueryBuilder에서 Generated Column 활용
const monthlyStats = await orderRepository
  .createQueryBuilder('o')
  .select('o.orderMonth', 'month')
  .addSelect('o.status', 'status')
  .addSelect('COUNT(*)', 'count')
  .addSelect('SUM(o.netAmount)', 'revenue')
  .where('o.orderMonth = :month', { month: '2026-03' })
  .groupBy('o.orderMonth')
  .addGroupBy('o.status')
  .getRawMany();

Prisma + Generated Column

// schema.prisma — Generated Column은 DB 레벨에서 관리
model Order {
  id         Int      @id @default(autoincrement())
  userId     Int      @map("user_id")
  amount     Decimal  @db.Decimal(12, 2)
  discount   Decimal  @default(0) @db.Decimal(12, 2)
  status     OrderStatus
  createdAt  DateTime @default(now()) @map("created_at")

  // Generated Column: @ignore 또는 읽기 전용으로 처리
  // Prisma는 Generated Column을 직접 지원하지 않으므로
  // DB 마이그레이션에서 별도 관리
  netAmount  Decimal? @map("net_amount") @db.Decimal(12, 2)
  orderMonth String?  @map("order_month") @db.Char(7)

  @@map("orders")
}

// Prisma에서 Generated Column 조회
const stats = await prisma.order.groupBy({
  by: ['orderMonth', 'status'],
  where: { orderMonth: '2026-03' },
  _count: true,
  _sum: { netAmount: true },
});

// Raw SQL로 Generated Column 활용
const highValue = await prisma.$queryRaw`
  SELECT * FROM orders
  WHERE net_amount >= 100000
    AND order_month = '2026-03'
  ORDER BY net_amount DESC
`;

마이그레이션: Generated Column 추가

-- 기존 테이블에 Generated Column 추가
ALTER TABLE orders
ADD COLUMN net_amount DECIMAL(12,2)
    AS (amount - discount) STORED
    AFTER discount;

ALTER TABLE orders
ADD COLUMN order_month CHAR(7)
    AS (DATE_FORMAT(created_at, '%Y-%m')) STORED
    AFTER created_at;

-- Generated Column에 인덱스 추가
ALTER TABLE orders ADD INDEX idx_net_amount (net_amount);
ALTER TABLE orders ADD INDEX idx_order_month (order_month, status);

-- TypeORM 마이그레이션
export class AddGeneratedColumns1711000000000 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE orders
      ADD COLUMN net_amount DECIMAL(12,2) AS (amount - discount) STORED,
      ADD COLUMN order_month CHAR(7) AS (DATE_FORMAT(created_at, '%Y-%m')) STORED
    `);
    await queryRunner.query(`
      ALTER TABLE orders
      ADD INDEX idx_net_amount (net_amount),
      ADD INDEX idx_order_month (order_month, status)
    `);
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE orders
      DROP INDEX idx_order_month,
      DROP INDEX idx_net_amount,
      DROP COLUMN order_month,
      DROP COLUMN net_amount
    `);
  }
}

제약 사항과 주의점

-- ❌ 서브쿼리 사용 불가
ALTER TABLE t ADD COLUMN c INT AS ((SELECT MAX(id) FROM t));
-- ERROR: Generated column cannot refer to a subquery

-- ❌ 비결정적 함수 사용 불가 (STORED만)
ALTER TABLE t ADD COLUMN c TIMESTAMP AS (NOW()) STORED;
-- ERROR: Expression of generated column contains a disallowed function

-- ✅ 비결정적 함수는 VIRTUAL로 가능 (MySQL 8.0.13+)
ALTER TABLE t ADD COLUMN c TIMESTAMP AS (NOW()) VIRTUAL;

-- ❌ 다른 Generated Column 참조 불가
-- price_with_tax를 참조하는 또 다른 Generated Column 생성 불가

-- ❌ AUTO_INCREMENT 불가
-- Generated Column은 AUTO_INCREMENT가 될 수 없음

-- ✅ 외래 키 참조 가능 (STORED만)
ALTER TABLE t ADD COLUMN ref_id INT AS (...) STORED;
ALTER TABLE t ADD FOREIGN KEY (ref_id) REFERENCES other(id);

핵심 정리

패턴	타입	용도
JSON 필드 인덱싱	STORED	JSON 값 추출 → 인덱스 생성
계산 컬럼 캐싱	STORED	복잡한 계산 결과 저장
실시간 계산 표시	VIRTUAL	단순 연산, 디스크 절약
검색 정규화	STORED	LOWER(), 날짜 포맷 등

Generated Column은 애플리케이션 로직을 DB 레벨로 이동하여 데이터 일관성을 보장하고 쿼리 성능을 개선합니다. 특히 JSON 컬럼 인덱싱은 MySQL에서 Generated Column이 유일한 공식 방법입니다. JPA/TypeORM에서는 반드시 insertable=false, updatable=false로 설정하세요. MySQL Covering Index 최적화와 TypeORM Enum·JSON 컬럼 심화도 함께 참고하세요.