MySQL - Generated Columns

概要

MySQL Generated Columns (生成列) は、MySQL 5.7.6で導入されたテーブル定義機能である。
テーブル内の他のカラムから値を計算して生成される特殊なカラムである。

Generated Columnsには、VIRTUAL (仮想列) と STORED (格納列) の2つのタイプがある。

• VIRTUAL (仮想列)

  データを物理的に格納せず、行読み取り時にオンザフライで計算する。

  デフォルトのタイプはVIRTUALである。

• STORED (格納列)

  INSERT / UPDATE時に計算結果を物理的に格納する。

Generated Columnsは、繰り返し計算される値の自動化、JSON列からの値抽出とインデックス作成、複雑な式のクエリ簡略化に利用される。
Generated Columnsに対してインデックスを作成することにより、計算結果に基づく高速な検索が可能になる。

ストレージエンジンは、InnoDBが完全対応している。
MyISAMは基本機能のみサポートしており、VIRTUAL列へのインデックス作成は非対応である。

Generated Columnsは、関数インデックス (MySQL 8.0.13以降) と密接に関連している。
関数インデックスは内部的にVIRTUAL Generated Columnとして実装されている。

基本構文

CREATE TABLEでの定義

Generated Columnsは、CREATE TABLE 文で定義する。

基本構文を以下に示す。

 CREATE TABLE table_name (
    col_name data_type [GENERATED ALWAYS] AS (expression)
      [VIRTUAL | STORED]
      [NOT NULL | NULL]
      [UNIQUE [KEY]]
      [[PRIMARY] KEY]
      [COMMENT 'string']
 );

下表に、構文要素を示す。

生成列の構文要素

構文要素	説明
GENERATED ALWAYS	省略可能なキーワード 明示的に生成列であることを示す
AS (expression)	生成式を指定 括弧で囲む必要がある
VIRTUAL | STORED	生成タイプを指定 省略時はVIRTUALがデフォルト
NOT NULL | NULL	NULL許可属性
UNIQUE [KEY]	UNIQUE制約 VIRTUAL列とSTORED列の両方で使用可能
[PRIMARY] KEY	プライマリキー制約 STORED列でのみ使用可能

• フルネーム生成の例

   CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      full_name VARCHAR(101) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
   );
  

  
  

• 税込価格計算の例

   CREATE TABLE products (
      id INT PRIMARY KEY,
      name VARCHAR(100),
      price DECIMAL(10, 2),
      tax_rate DECIMAL(5, 2),
      price_with_tax DECIMAL(10, 2) AS (price * (1 + tax_rate / 100)) STORED
   );
  

  
  

• GENERATED ALWAYS キーワードの省略例

   CREATE TABLE orders (
      id INT PRIMARY KEY,
      order_date DATE,
      order_year INT AS (YEAR(order_date)) VIRTUAL,
      order_month INT AS (MONTH(order_date)) VIRTUAL
   );
  

ALTER TABLEでの追加

既存のテーブルにGenerated Columnを追加する場合、ALTER TABLE 文を使用する。

基本構文を以下に示す。

 ALTER TABLE table_name
 ADD COLUMN col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED];

• VIRTUAL列の追加例を以下に示す。

   ALTER TABLE users
   ADD COLUMN email_domain VARCHAR(100) AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL;
  

  
  

• STORED列の追加例

   ALTER TABLE products
   ADD COLUMN discounted_price DECIMAL(10, 2) AS (price * 0.9) STORED;
  

  
  

• Generated Columnの削除は、通常のカラムと同様にDROP COLUMN句を使用する。

   ALTER TABLE users DROP COLUMN full_name;
  

  
  

• VIRTUALからSTOREDへの変換は、ALTERで可能である。

   ALTER TABLE users
   MODIFY COLUMN full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) STORED;
  

ALTER TABLE 操作のアルゴリズムは、生成タイプによって異なる。
VIRTUALカラムの追加は、ALGORITHM=INSTANT で実行される。
STOREDカラムの追加は、ALGORITHM=COPY でテーブル全体を再構築する。

ALTER TABLE操作のアルゴリズム比較

操作	VIRTUAL	STORED
カラム追加	INSTANT (メタデータ変更のみ)	COPY (テーブル全体再構築)
カラム削除 (8.0.29+)	INSTANT	INSTANT
式の変更	COPY	COPY
VIRTUAL -> STORED変換	COPY	-
並行DML許可	可 (追加時)	不可 (追加時)

式の記述ルール

Generated Columnsの式には、使用可能な要素と使用不可な要素が定義されている。

下表に、使用可能な要素を示す。

生成列の式で使用可能な要素

要素	説明
リテラル値	数値、文字列、日付リテラル
同一テーブル内の他のカラム参照	同じ行の他のカラムを参照可能
決定的組み込み関数	同じ入力に対して常に同じ結果を返す関数
演算子	算術演算子、比較演算子、論理演算子

下表に、使用不可な要素を示す。

生成列の式で使用不可な要素

要素	説明
サブクエリ	SELECT文を式内で使用不可
ストアドプロシージャ / 関数	ユーザ定義関数の呼び出し不可
ユーザ変数	@variable 形式の変数不可
システム変数	@@variable 形式の変数不可
非決定的関数	NOW(), RAND(), CURRENT_USER(), CONNECTION_ID() 等
AUTO_INCREMENTカラムの参照	AUTO_INCREMENT カラムを式で参照不可
動的パラメータ	プリペアドステートメントの ? パラメータ不可

使用可能な関数と使用不可な関数の一覧を以下に示す。

使用可能/不可な関数の例

カテゴリ	使用可能	使用不可
文字列関数	CONCAT, SUBSTRING, UPPER, LOWER, LENGTH	LOAD_FILE, UUID
数学関数	ABS, ROUND, FLOOR, CEIL, MOD	RAND
日付関数	YEAR, MONTH, DAY, DATE_FORMAT	NOW, CURDATE, CURTIME, CURRENT_TIMESTAMP
JSON関数	JSON_EXTRACT, JSON_UNQUOTE	-
システム関数	-	CURRENT_USER, CONNECTION_ID, LAST_INSERT_ID

他のGenerated Columnを参照することも可能である。
ただし、定義順序に従って参照する必要があり、循環参照は禁止されている。

Generated Column間の参照例を以下に示す。

以下の例では、is_far カラムが distance_from_origin カラムを参照している。
distance_from_origin が先に定義されているため、参照可能である。

 CREATE TABLE geometry (
    x DOUBLE,
    y DOUBLE,
    distance_from_origin DOUBLE AS (SQRT(x * x + y * y)) VIRTUAL,
    is_far BOOLEAN AS (distance_from_origin > 10) VIRTUAL
 );

仮想列 (VIRTUAL)

動作原理

VIRTUAL列は、データを物理的に格納しない。
行読み取り時にオンザフライで式を評価して、値を計算する。

VIRTUAL列の特性を以下に示す。

• ストレージ消費

  基本的にストレージを消費しない

  ただし、インデックス作成時はインデックス領域を消費する

• 計算タイミング

  SELECT文実行時、WHERE句評価時

  行読み取りのたびに再計算される

• パフォーマンス

  読み取り時に計算コストが発生

  書き込み時は影響なし

• インデックス

  セカンダリインデックスを作成可能 (InnoDB 8.0以降)

  プライマリキーは作成不可

VIRTUAL列が適している用途を以下に示す。

• 計算コストが低い式

  単純な文字列結合、算術演算

• アクセス頻度が低い列

  稀にしか参照されない計算値

• ストレージ節約が重要な場合

  ディスク容量を節約したい

• 頻繁に更新されるテーブル

  書き込みパフォーマンスを重視

VIRTUAL列の評価タイミングを以下に示す。

• SELECTリストに含まれる場合

  行読み取り時に評価

• WHERE句で使用される場合

  行フィルタリング時に評価

• ORDER BY句で使用される場合

  ソート時に評価

• GROUP BY句で使用される場合

  グループ化時に評価

使用例

• フルネーム生成の例

   CREATE TABLE employees (
      id INT PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
   );
  

  
  

• データ挿入の例

   INSERT INTO employees (first_name, last_name) VALUES
   ('John', 'Doe'),
   ('Jane', 'Smith'),
   ('Alice', 'Johnson');
  

• VIRTUAL列を含む検索の例

   SELECT id, full_name FROM employees;
  

  
  

  結果を以下に示す。

   +----+--------------+
   | id | full_name    |
   +----+--------------+
   |  1 | John Doe     |
   |  2 | Jane Smith   |
   |  3 | Alice Johnson|
   +----+--------------+
  

• 日付分解の例

   CREATE TABLE events (
      id INT PRIMARY KEY,
      event_name VARCHAR(100),
      event_date DATE,
      event_year INT AS (YEAR(event_date)) VIRTUAL,
      event_month INT AS (MONTH(event_date)) VIRTUAL,
      event_day INT AS (DAY(event_date)) VIRTUAL
   );
  

  
  

• JSON値抽出の例

   CREATE TABLE user_profiles (
      id INT PRIMARY KEY,
      data JSON,
      email VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.email'))) VIRTUAL,
      age INT AS (JSON_EXTRACT(data, '$.age')) VIRTUAL
   );
  

  
  

• データ挿入の例

   INSERT INTO user_profiles (id, data) VALUES
   (1, '{"email": "user1@example.com", "age": 25}'),
   (2, '{"email": "user2@example.com", "age": 30}');
  

  
  

• VIRTUAL列を使った検索の例

  以下の例では、VIRTUAL列 age がWHERE句で評価される。
  

   SELECT id, email, age FROM user_profiles WHERE age > 28;
  

格納列 (STORED)

動作原理

STORED列は、計算結果を物理的にディスクに格納する。
INSERT/UPDATE時に式を評価して、計算結果をテーブルに保存する。

下表に、STORED列の特性を示す。

STORED列の特性

特性	説明
ストレージ消費	データ型に応じたストレージを消費する インデックスも追加のストレージを消費する
計算タイミング	INSERT時、UPDATE時 (依存カラムが更新された場合) 読み取り時は再計算されない
パフォーマンス	読み取り時は高速 (計算不要) 書き込み時に計算コストが発生
インデックス	セカンダリインデックス、プライマリキー、UNIQUE制約全て可能

下表に、STORED列が適している用途を示す。

STORED列が適している用途

用途	説明
計算コストが高い式	複雑な数学計算、文字列処理
アクセス頻度が高い列	頻繁に検索される計算値
インデックスを作成したい列	プライマリキーやUNIQUE制約が必要な場合
参照頻度が更新頻度より高い場合	読み取りパフォーマンスを重視

下表に、STORED列の更新トリガー条件を示す。

STORED列の更新トリガー条件

条件	説明
INSERT時	必ず計算される
UPDATE時	依存カラムが更新された場合のみ再計算 依存カラムが更新されない場合は再計算されない

使用例

• 税込価格計算の例

   CREATE TABLE products (
      id INT PRIMARY KEY,
      name VARCHAR(100),
      price DECIMAL(10, 2),
      tax_rate DECIMAL(5, 2) DEFAULT 10.00,
      price_with_tax DECIMAL(10, 2) AS (price * (1 + tax_rate / 100)) STORED
   );
  

  
  

• データ挿入の例

   INSERT INTO products (id, name, price) VALUES
   (1, 'Product A', 100.00),
   (2, 'Product B', 200.00);
  

  
  

• 格納された値の確認

   SELECT id, name, price, price_with_tax FROM products;
  

  
  

  結果を以下に示す。

   +----+-----------+--------+----------------+
   | id | name      | price  | price_with_tax |
   +----+-----------+--------+----------------+
   |  1 | Product A | 100.00 |         110.00 |
   |  2 | Product B | 200.00 |         220.00 |
   +----+-----------+--------+----------------+
  

  
  

• 価格更新時の再計算例

   UPDATE products SET price = 150.00 WHERE id = 1;
  

  
  

• 更新後の確認

   SELECT id, name, price, price_with_tax FROM products WHERE id = 1;
  

  
  

  結果を以下に示す。

   +----+-----------+--------+----------------+
   | id | name      | price  | price_with_tax |
   +----+-----------+--------+----------------+
   |  1 | Product A | 150.00 |         165.00 |
   +----+-----------+--------+----------------+
  

  
  

• STORED列をプライマリキーとして使用する例

   CREATE TABLE composite_keys (
      year INT,
      month INT,
      day INT,
      date_key VARCHAR(10) AS (CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0'))) STORED,
      PRIMARY KEY (date_key)
   );
  

  
  

• JSON列からの値抽出とSTOREDの組み合わせ例

  以下の例では、JSON列から抽出した値をSTORED列に格納し、インデックスを作成している。

  これにより、JSON列への直接検索よりも高速な検索が可能になる。

   CREATE TABLE orders (
      id INT PRIMARY KEY,
      customer_data JSON,
      customer_email VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(customer_data, '$.email'))) STORED,
      INDEX idx_email (customer_email)
   );
  

VIRTUALとSTOREDの比較

VIRTUALとSTOREDの違いを体系的に比較する。

VIRTUAL vs STORED 比較表

項目	VIRTUAL	STORED
ストレージ使用	なし (インデックス除く)	あり (データ型に応じた容量)
計算タイミング	読み取り時 (SELECT, WHERE, ORDER BY等)	書き込み時 (INSERT, UPDATE)
読み取りパフォーマンス	計算コスト発生 (遅い)	高速 (計算不要)
書き込みパフォーマンス	影響なし (高速)	計算コスト発生 (遅い)
セカンダリインデックス	対応 (InnoDB 8.0+)	対応
プライマリキー	非対応	対応
UNIQUE制約	対応	対応
外部キー参照	非対応	一部制約あり (CASCADE等不可)
デフォルト	はい	いいえ
ALTER TABLE追加アルゴリズム	INSTANT	COPY

下表に、選択基準を示す。

VIRTUAL / STORED の選択基準

タイプ	選択すべき場合	説明
VIRTUAL	ストレージ容量を節約したい	ディスク容量が限られている
	計算式が単純	文字列結合、単純な算術演算
	アクセス頻度が低い	稀にしか参照されない
	更新頻度が高い	頻繁にINSERT / UPDATEされる
	セカンダリインデックスのみ必要	プライマリキーとして使用しない
STORED	読み取りパフォーマンスが重要	頻繁に検索される
	計算式が複雑	複雑な文字列処理、JSON解析、数学計算
	プライマリキーとして使用	テーブルの主キーとして必要
	インデックスを作成したい	高速検索が必要
	更新頻度が低い	書き込みパフォーマンスへの影響が小さい

パフォーマンス比較例を以下に示す。

 -- VIRTUAL版
 CREATE TABLE test_virtual (
    id INT PRIMARY KEY,
    value INT,
    calculated INT AS (value * 100) VIRTUAL,
    INDEX idx_calc (calculated)
 );
 
 -- STORED版
 CREATE TABLE test_stored (
    id INT PRIMARY KEY,
    value INT,
    calculated INT AS (value * 100) STORED,
    INDEX idx_calc (calculated)
 );

100万行のINSERTパフォーマンス比較を以下に示す。

• VIRTUAL版

  約10秒 (計算不要)

• STORED版

  約12秒 (計算と格納が必要)

インデックス付き検索パフォーマンス比較を以下に示す。

 SELECT * FROM test_virtual WHERE calculated = 5000;
 SELECT * FROM test_stored WHERE calculated = 5000;

• VIRTUAL版

  約0.05秒 (インデックス使用)

• STORED版

  約0.05秒 (インデックス使用)

インデックスを使用する場合、検索パフォーマンスはほぼ同等である。

インデックスとの組み合わせ

VIRTUAL列へのインデックス

MySQL 8.0以降のInnoDBでは、VIRTUAL列にセカンダリインデックスを作成できる。

VIRTUAL列インデックスの特性を以下に示す。

• インデックス作成

  セカンダリインデックスのみ作成可能

  プライマリキーは作成不可

• 実体化タイミング

  INSERT/UPDATE時にインデックスレコード内で値が実体化される

  インデックスレコードには計算結果が格納される

• パフォーマンス影響

  INSERT/UPDATE時にインデックス計算コストが発生

  SELECT時はインデックスから直接読み取り可能

• VIRTUAL列へのインデックス作成例

   CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
      INDEX idx_full_name (full_name)
   );
  

  
  

• インデックスを使用した検索例

   SELECT id, full_name FROM users WHERE full_name = 'John Doe';
  

  
  

• 実行計画を確認する。

   EXPLAIN SELECT id, full_name FROM users WHERE full_name = 'John Doe';
  

  
  

  結果を以下に示す。

  idx_full_name インデックスが使用されていることが確認できる。

   +----+-------------+-------+------+---------------+---------------+---------+-------+
   | id | select_type | table | type | possible_keys | key           | key_len | rows  |
   +----+-------------+-------+------+---------------+---------------+---------+-------+
   |  1 | SIMPLE      | users | ref  | idx_full_name | idx_full_name | 404     |     1 |
   +----+-------------+-------+------+---------------+---------------+---------+-------+
  

  
  

• カバリングインデックスの例

   CREATE TABLE employees (
      id INT PRIMARY KEY,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      salary DECIMAL(10, 2),
      full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
      INDEX idx_full_name_salary (full_name, salary)
   );
  

  
  

• カバリングインデックスを使用したクエリ例

  以下の例では、インデックスのみから結果を取得でき、テーブルアクセスが不要になる。

   SELECT full_name, salary FROM employees WHERE full_name LIKE 'John%';
  

JSON列への仮想インデックス

JSON列に対して直接インデックスを作成することはできない。
そのため、Generated Columnを使用してJSON列から値を抽出し、その列にインデックスを作成する。

JSON列仮想インデックスのパターンを以下に示す。

1. JSON列から値を抽出するGenerated Columnを定義
2. Generated Columnにインデックスを作成
3. WHERE句でGenerated Columnを使用して検索

• JSON列仮想インデックスの例

   CREATE TABLE products (
      id INT PRIMARY KEY,
      name VARCHAR(100),
      attributes JSON,
      category VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.category'))) STORED,
      price DECIMAL(10, 2) AS (JSON_EXTRACT(attributes, '$.price')) STORED,
      INDEX idx_category (category),
      INDEX idx_price (price)
   );
  

  
  

• データ挿入例

   INSERT INTO products (id, name, attributes) VALUES
   (1, 'Product A', '{"category": "electronics", "price": 299.99, "brand": "BrandX"}'),
   (2, 'Product B', '{"category": "books", "price": 19.99, "brand": "PublisherY"}'),
   (3, 'Product C', '{"category": "electronics", "price": 499.99, "brand": "BrandZ"}');
  

  
  

• カテゴリ検索例

   SELECT id, name, category, price FROM products WHERE category = 'electronics';
  

  
  

• 価格範囲検索例

   SELECT id, name, category, price FROM products WHERE price BETWEEN 100 AND 500;
  

  
  

• 複合インデックスの例

   CREATE TABLE user_logs (
      id INT PRIMARY KEY,
      user_id INT,
      log_data JSON,
      log_type VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(log_data, '$.type'))) VIRTUAL,
      log_timestamp DATETIME AS (JSON_UNQUOTE(JSON_EXTRACT(log_data, '$.timestamp'))) VIRTUAL,
      INDEX idx_type_timestamp (log_type, log_timestamp)
   );
  

  
  

• 複合インデックスを使用した検索例

   SELECT id, log_type, log_timestamp
   FROM user_logs
   WHERE log_type = 'login' AND log_timestamp > '2025-01-01';
  

関数インデックスとの関係

MySQL 8.0.13以降では、関数インデックス (Functional Index) がサポートされている。
関数インデックスは、内部的にVIRTUAL Generated Columnとして実装されている。

関数インデックスの構文を以下に示す。

 CREATE INDEX index_name ON table_name ((expression));

関数インデックスの例を以下に示す。

 CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100)
 );
 
 CREATE INDEX idx_email_domain ON users ((SUBSTRING_INDEX(email, '@', -1)));

上記のインデックスは、内部的に以下と等価である。

 ALTER TABLE users
 ADD COLUMN __hidden_generated_column VARCHAR(100)
   AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL;
 
 CREATE INDEX idx_email_domain ON users (__hidden_generated_column);

下表に、関数インデックスとGenerated Columnの比較を示す。

関数インデックス vs Generated Column + インデックス

項目	関数インデックス	Generated Column + インデックス
構文簡潔性	シンプル (1ステップ)	複雑 (2ステップ)
カラム可視性	隠し列 (SELECTで見えない)	通常列 (SELECTで見える)
明示的参照	不可	可能
オプティマイザ認識	式パターンマッチングが必要	明示的カラム参照で最適化
内部実装	VIRTUAL Generated Column	VIRTUAL Generated Column
対応バージョン	8.0.13+	5.7.6+

関数インデックスを使用した検索例を以下に示す。

 SELECT id, email FROM users WHERE SUBSTRING_INDEX(email, '@', -1) = 'example.com';

オプティマイザは、式を認識して関数インデックスを使用する。

Generated Columnのメリット

メリット	説明
明示的にカラムを参照できる	SELECT句で直接使用可能
複数のインデックスで共有可能	同じ計算結果を複数のインデックスで利用
バージョン互換性	MySQL 5.7.6以降で使用可能

下表に、

関数インデックスのメリット

メリット	説明
構文がシンプル	1ステップでインデックス作成
スキーマ汚染を回避	不要なカラムがテーブル定義に表示されない

サンプルクエリ

実用的なGenerated Columnsの使用例を示す。

• フルネーム生成と検索の例

   CREATE TABLE employees (
      id INT PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
      INDEX idx_full_name (full_name)
   );
   
   INSERT INTO employees (first_name, last_name) VALUES
   ('John', 'Doe'),
   ('Jane', 'Smith');
   
   SELECT * FROM employees WHERE full_name = 'John Doe';
  

  
  

• 税込価格計算の例

   CREATE TABLE products (
      id INT PRIMARY KEY,
      name VARCHAR(100),
      price DECIMAL(10, 2),
      tax_rate DECIMAL(5, 2) DEFAULT 10.00,
      price_with_tax DECIMAL(10, 2) AS (price * (1 + tax_rate / 100)) STORED
   );
   
   INSERT INTO products (id, name, price) VALUES (1, 'Product A', 100.00);
   
   SELECT name, price, price_with_tax FROM products;
  

  
  

• 日付分解とパーティショニングの例

   CREATE TABLE orders (
      id INT PRIMARY KEY,
      order_date DATE,
      customer_id INT,
      amount DECIMAL(10, 2),
      order_year INT AS (YEAR(order_date)) STORED,
      order_month INT AS (MONTH(order_date)) STORED
   )
   PARTITION BY RANGE (order_year) (
      PARTITION p2023 VALUES LESS THAN (2024),
      PARTITION p2024 VALUES LESS THAN (2025),
      PARTITION p2025 VALUES LESS THAN (2026),
      PARTITION pmax VALUES LESS THAN MAXVALUE
   );
  

  
  

• JSON列からの値抽出とインデックスの例

   CREATE TABLE user_profiles (
      id INT PRIMARY KEY,
      username VARCHAR(50),
      profile_data JSON,
      email VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(profile_data, '$.email'))) STORED,
      age INT AS (JSON_EXTRACT(profile_data, '$.age')) STORED,
      country VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(profile_data, '$.country'))) STORED,
      INDEX idx_email (email),
      INDEX idx_age (age),
      INDEX idx_country (country)
   );
   
   INSERT INTO user_profiles (id, username, profile_data) VALUES
   (1, 'user1', '{"email": "user1@example.com", "age": 25, "country": "USA"}'),
   (2, 'user2', '{"email": "user2@example.com", "age": 30, "country": "Japan"}');
   
   SELECT username, email, age FROM user_profiles WHERE country = 'Japan';
  

  
  

• POINT型座標計算の例

   CREATE TABLE locations (
      id INT PRIMARY KEY,
      name VARCHAR(100),
      x DOUBLE,
      y DOUBLE,
      coordinates POINT AS (POINT(x, y)) STORED,
      SPATIAL INDEX idx_coordinates (coordinates)
   );
   
   INSERT INTO locations (id, name, x, y) VALUES
   (1, 'Location A', 35.6895, 139.6917),
   (2, 'Location B', 34.6937, 135.5023);
   
   SELECT name FROM locations
   WHERE ST_Distance_Sphere(coordinates, POINT(35.0, 139.0)) < 100000;
  

  
  

• メールアドレスドメイン抽出の例

   CREATE TABLE users (
      id INT PRIMARY KEY,
      username VARCHAR(50),
      email VARCHAR(100),
      email_domain VARCHAR(100) AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL,
      INDEX idx_email_domain (email_domain)
   );
   
   INSERT INTO users (id, username, email) VALUES
   (1, 'user1', 'user1@gmail.com'),
   (2, 'user2', 'user2@yahoo.com'),
   (3, 'user3', 'user3@gmail.com');
   
   SELECT username, email FROM users WHERE email_domain = 'gmail.com';
  

  
  

• ハッシュ値計算とユニーク制約の例

   CREATE TABLE documents (
      id INT PRIMARY KEY AUTO_INCREMENT,
      content TEXT,
      content_hash VARCHAR(64) AS (SHA2(content, 256)) STORED,
      UNIQUE KEY uk_content_hash (content_hash)
   );
   
   INSERT INTO documents (content) VALUES ('Document content 1');
   
   -- 重複挿入を試みる
   INSERT INTO documents (content) VALUES ('Document content 1');
   -- Error: Duplicate entry for key 'uk_content_hash'
  

  
  

• 大文字小文字を区別しない検索の例

   CREATE TABLE articles (
      id INT PRIMARY KEY,
      title VARCHAR(200),
      title_lower VARCHAR(200) AS (LOWER(title)) VIRTUAL,
      INDEX idx_title_lower (title_lower)
   );
   
   INSERT INTO articles (id, title) VALUES
   (1, 'MySQL Tutorial'),
   (2, 'Advanced MySQL'),
   (3, 'mysql best practices');
   
   SELECT title FROM articles WHERE title_lower LIKE '%mysql%';
  

制限事項

Generated Columnsには、いくつかの制限事項がある。

DML操作の制限を以下に示す。

• INSERT時の値指定

  Generated Columnには値を直接指定できない

  DEFAULTのみ指定可能

• UPDATE時の値変更

  Generated Columnを直接更新できない

  依存カラムを更新すると自動的に再計算される

INSERT時の値指定例を以下に示す。

 -- 正常: Generated Columnを省略
 INSERT INTO users (first_name, last_name) VALUES ('John', 'Doe');
 
 -- 正常: DEFAULTを指定
 INSERT INTO users (first_name, last_name, full_name) VALUES ('John', 'Doe', DEFAULT);
 
 -- エラー: 値を直接指定
 INSERT INTO users (first_name, last_name, full_name) VALUES ('John', 'Doe', 'John Doe');
 -- Error: The value specified for generated column 'full_name' is not allowed

外部キー制約の制限を以下に示す。

• VIRTUAL列

  外部キーとして使用不可

• STORED列

  外部キーとして使用可能

  ただし、CASCADE、SET NULL、SET DEFAULTは使用不可

外部キー制約の例を以下に示す。

 CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    code VARCHAR(10) AS (UPPER(LEFT(name, 3))) STORED
 );
 
 -- エラー: STORED列へのCASCADE制約
 CREATE TABLE products (
    id INT PRIMARY KEY,
    category_code VARCHAR(10),
    FOREIGN KEY (category_code) REFERENCES categories(code) ON DELETE CASCADE
 );
 -- Error: Cannot define foreign key with ON DELETE CASCADE on generated column
 
 -- 正常: NO ACTIONまたはRESTRICTのみ
 CREATE TABLE products (
    id INT PRIMARY KEY,
    category_code VARCHAR(10),
    FOREIGN KEY (category_code) REFERENCES categories(code) ON DELETE RESTRICT
 );

パーティショニングの制限を以下に示す。

• 式は決定的である必要がある

  同じ入力に対して常に同じ結果を返す

• 非決定的関数は使用不可

  NOW(), RAND()等

パーティショニングの例を以下に示す。

 -- 正常: 決定的関数
 CREATE TABLE events (
    id INT PRIMARY KEY,
    event_date DATE,
    event_year INT AS (YEAR(event_date)) STORED
 )
 PARTITION BY RANGE (event_year) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
 );
 
 -- エラー: 非決定的関数
 CREATE TABLE logs (
    id INT PRIMARY KEY,
    log_time DATETIME,
    created_date DATE AS (CURDATE()) STORED
 )
 PARTITION BY RANGE (YEAR(created_date)) (
    PARTITION p2024 VALUES LESS THAN (2025)
 );
 -- Error: Constant, random or timezone-dependent expressions are not allowed

レプリケーションの制限を以下に示す。

• マスタとスレーブで生成式が同一である必要がある

  異なる式の場合、レプリケーションエラーが発生

• 非決定的関数の使用は避ける

  マスタとスレーブで結果が異なる可能性

ストレージエンジンの対応状況を以下に示す。

ストレージエンジン対応状況

ストレージエンジン	VIRTUAL	STORED	VIRTUALセカンダリインデックス
InnoDB	完全対応	完全対応	対応 (8.0+)
MyISAM	基本機能のみ	基本機能のみ	非対応
Memory	非対応	非対応	非対応
CSV	非対応	非対応	非対応

トリガーとの関係を以下に示す。

• BEFORE INSERT/UPDATE

  トリガー実行直後にGenerated Column計算

• AFTER INSERT/UPDATE

  Generated Column計算後にトリガー実行

トリガー内でのGenerated Column参照例を以下に示す。

 CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(50),
    full_info TEXT
 );
 
 CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) STORED
 );
 
 DELIMITER $$
 CREATE TRIGGER after_user_insert
 AFTER INSERT ON users
 FOR EACH ROW
 BEGIN
    INSERT INTO audit_log (user_id, action, full_info)
    VALUES (NEW.id, 'INSERT', NEW.full_name);
 END$$
 DELIMITER ;

上記のトリガーでは、AFTER INSERTでGenerated Column NEW.full_name を参照している。

パフォーマンス

Generated Columnsのパフォーマンス特性と最適化方法を示す。

下表に、VIRTUALのパフォーマンス特性を示す。

VIRTUALのパフォーマンス特性

特性	説明
書き込み性能	INSERT/UPDATEへの影響なし インデックスがある場合はインデックス更新コスト発生
読み取り性能	行読み取りごとに式を評価 計算コストが高い式では遅延発生
ストレージ	基本的に追加容量不要 インデックスがある場合はインデックス領域消費

下表に、STOREDのパフォーマンス特性を示す。

STOREDのパフォーマンス特性

特性	説明
書き込み性能	INSERT/UPDATE時に式を評価 計算コストと格納コストが発生
読み取り性能	計算不要で高速 通常カラムと同等
ストレージ	データ型に応じた容量消費 インデックスも追加容量消費

• パフォーマンステストの例

   -- テストテーブル作成
   CREATE TABLE perf_test_virtual (
      id INT PRIMARY KEY AUTO_INCREMENT,
      value INT,
      calculated INT AS (value * 100 + value * 10 + value) VIRTUAL
   );
   
   CREATE TABLE perf_test_stored (
      id INT PRIMARY KEY AUTO_INCREMENT,
      value INT,
      calculated INT AS (value * 100 + value * 10 + value) STORED
   );
   
   -- 100万行挿入のパフォーマンス測定
   -- VIRTUAL版
   INSERT INTO perf_test_virtual (value)
   SELECT n FROM (SELECT @row := @row + 1 as n FROM
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
     (SELECT @row:=0) t3 LIMIT 1000000) numbers;
   -- 実行時間: 約10秒
   
   -- STORED版
   INSERT INTO perf_test_stored (value)
   SELECT n FROM (SELECT @row := @row + 1 as n FROM
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
     (SELECT @row:=0) t3 LIMIT 1000000) numbers;
   -- 実行時間: 約13秒
  

  
  

• インデックス付きVIRTUAL列のパフォーマンス

   CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
      INDEX idx_full_name (full_name)
   );
   
   -- 100万行挿入
   -- 実行時間: 約15秒 (インデックス更新コスト含む)
   
   -- インデックスを使用した検索
   SELECT * FROM users WHERE full_name = 'John Doe';
   -- 実行時間: 約0.001秒 (インデックス使用)
  

  
  

• カバリングインデックスによる最適化

   CREATE TABLE employees (
      id INT PRIMARY KEY,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      salary DECIMAL(10, 2),
      full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
      INDEX idx_full_name_covering (full_name, salary)
   );
   
   -- カバリングインデックスを使用するクエリ
   SELECT full_name, salary FROM employees WHERE full_name LIKE 'John%';
   -- インデックスのみからデータ取得、テーブルアクセス不要
   -- VIRTUAL列の再計算も不要
  

パフォーマンス最適化のガイドラインを以下に示す。

VIRTUALを最適化するには、以下を実施する。

• 計算式を単純に保つ

  複雑な式は避ける

• インデックスを活用

  検索対象列にはインデックスを作成

• カバリングインデックスを使用

  必要なカラムを全てインデックスに含める

STOREDを最適化するには、以下を実施する。

• 更新頻度を考慮

  頻繁に更新される列では使用を避ける

• ストレージサイズを考慮

  大きなデータ型は避ける

• 依存カラムの更新を最小化

  不要な更新を避ける

共通の最適化として、以下を実施する。

• 適切なタイプ選択

  VIRTUALとSTOREDの特性を理解して選択

• インデックス戦略

  必要最小限のインデックスを作成

• 式の決定性確保

  決定的関数のみ使用

下表に、パフォーマンス比較の目安を示す。

パフォーマンス比較の目安

操作	VIRTUAL	STORED	通常カラム
INSERT (100万行)	10秒	13秒	9秒
UPDATE (10万行)	2秒	3秒	2秒
SELECT (インデックスなし)	5秒 (計算あり)	1秒	1秒
SELECT (インデックスあり)	0.05秒	0.05秒	0.05秒
ストレージ消費 (100万行)	0MB (本体)	4MB (INT型)	4MB (INT型)

上記は目安であり、実際の値は式の複雑さ、データ型、ハードウェア構成により変動する。

関連ページ

• MySQL - インデックス

  インデックスの種類と作成方法

  セカンダリインデックス、関数インデックス

• MySQL - JSON関数

  JSON_EXTRACT, JSON_UNQUOTE等の関数

  JSON列からの値抽出

• MySQL - ALTER TABLE

  ALTER TABLE文の構文と操作

  ALGORITHM, LOCK句の詳細