👋
MySQLの仮想カラム(Generated Column)について
MySQLの仮想カラム(Generated Column)について
仮想カラム（Generated Column）とは
定義した式に従って値を自動生成してカラムのように扱える仕組み(カラム)のことだ。
通常のカラムと仮想カラムの違いについて
| 項目 | 通常のカラム | 仮想カラム |
|---|---|---|
| データ入力 | データを直接挿入・更新する | 他のカラムの値から自動的に計算される |
| ストレージ | 物理的にディスクにデータが保存される | 計算式（Expression）で定義される |
| 操作方法 | INSERT/UPDATE文で明示的に値を指定する | INSERT/UPDATE時に値を指定することはできない |
| 値の更新 | 明示的に更新が必要 | 常に最新の計算結果が取得される |
仮想カラム | VIRTUAL vs STORED の2つのタイプ
MySQLの仮想カラムにはVIRTUALとSTOREDの2つのタイプがある。
| 項目 | VIRTUAL | STORED |
|---|---|---|
| データ保存 | 保存しない （計算結果は物理的に持たない） |
保存する （計算結果を物理的に持つ） |
| 計算タイミング | SELECT 実行時に毎回計算 | INSERT / UPDATE 時に計算 |
| ストレージ容量 | 消費しない | 消費する |
| SELECT 時の速度 | 計算コストが発生するため遅くなりやすい | 計算不要なので速い |
| INSERT / UPDATE 時の速度 | 計算不要なので速い | 計算が必要なためやや遅くなる |
| 利用例 | 計算頻度が低い列や、保存不要な一時的派生データ | 検索条件で頻繁に利用する計算結果や集計列 |
仮想カラムが登場した背景と必要性
仮想カラムが導入された背景には、以下のような課題がありました。
データ正規化と利便性のトレードオフ
従来は、計算結果を保存するために以下の選択肢があった：
- 計算結果をアプリケーション側で毎回計算する（処理負荷が高い）
- 計算結果を別カラムに保存する（データ不整合のリスク）
- ビューを使用する（複雑なクエリになりがち）
JSON データの普及
NoSQLの普及により、MySQLでもJSONデータ型が導入され、
JSONから特定の値を抽出して検索・ソートに使用したいニーズが高まりました。
パフォーマンスの要求
複雑な計算や文字列処理を含むクエリのパフォーマンス向上が求められるようになりました。
仮想カラムは、これらの課題を解決する現代的なソリューションとして位置づけられている。
使い分けの実務指針（要点）
VIRTUALを選ぶべき場合
- 計算処理が軽く、SELECT頻度が低い場合
- ストレージ容量を節約したい場合
- 一時的な計算結果やフィルタリング用途
- JSON データの特定フィールド抽出
STOREDを選ぶべき場合
- 頻繁にSELECTされる計算結果
- 複雑な計算処理を含む場合（計算コストが高い）
- 検索条件として頻繁に使用される場合
- レスポンス速度を重視する場合
具体的な使用例
基本的な仮想カラムの作成
-- 単価と数量から総額を計算する VIRTUAL カラム
CREATE TABLE orders (
id INT PRIMARY KEY,
unit_price DECIMAL(10,2),
quantity INT,
total_amount DECIMAL(12,2) AS (unit_price * quantity) VIRTUAL
);
-- 姓名から氏名を結合する STORED カラム
CREATE TABLE users (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
full_name VARCHAR(101) AS (CONCAT(last_name, ' ', first_name)) STORED
);
JSON データからの値抽出
-- JSON データから特定のフィールドを抽出
CREATE TABLE products (
id INT PRIMARY KEY,
product_data JSON,
category VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.category'))) STORED,
price DECIMAL(10,2) AS (JSON_EXTRACT(product_data, '$.price')) VIRTUAL
);
-- 使用例
INSERT INTO products (id, product_data) VALUES
(1, '{"category": "electronics", "price": 29999, "brand": "Sony"}');
-- 仮想カラムを使用した検索
SELECT * FROM products WHERE category = 'electronics';
SELECT * FROM products WHERE price > 20000;
日付計算の例
-- 注意：CURDATE()は非決定的関数のため実際にはエラーになる
-- 以下は概念的な例として記載
CREATE TABLE employees (
id INT PRIMARY KEY,
birth_date DATE,
hire_date DATE,
-- 実際の実装では固定日付や別の方法を使用
birth_year INT AS (YEAR(birth_date)) VIRTUAL,
hire_year INT AS (YEAR(hire_date)) STORED
);
パフォーマンス比較
計算コストの比較
| 処理内容 | VIRTUAL | STORED | 通常のカラム |
|---|---|---|---|
| SELECT時の計算 | 毎回実行 | 不要 | 不要 |
| INSERT時の計算 | 不要 | 実行 | 不要 |
| UPDATE時の計算 | 不要 | 実行（関連カラム更新時） | 不要 |
| ストレージ使用量 | 0 | 通常カラムと同じ | 通常カラムと同じ |
注意点・制限事項
1. 式の制限
-
許可される要素：
- リテラル値
- 決定的な組み込み関数（CONCAT、SQRT、YEAR等）
- 演算子
-
禁止される要素：
- 非決定的関数（NOW()、RAND()、CONNECTION_ID()等）
- ストアドファンション、ユーザー定義関数
- サブクエリ
- 変数（システム変数、ユーザー定義変数等）
-- NG例：非決定的関数
CREATE TABLE logs (
id INT PRIMARY KEY,
message TEXT,
created_at TIMESTAMP AS (NOW()) VIRTUAL -- エラー
);
-- OK例：決定的関数
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
name_length INT AS (CHAR_LENGTH(name)) VIRTUAL
);
2. その他の制限事項（公式ドキュメント準拠）
- AUTO_INCREMENT属性は仮想カラムで使用不可
- AUTO_INCREMENTカラムは仮想カラムの式で参照不可
- INSERT/UPDATE時に仮想カラムに値を指定する場合はDEFAULTのみ可能
3. 注意点
- SQLモードが異なると結果が変わる可能性がある
- 式の評価でエラーが発生するとCREATE TABLE文が失敗する
実装時のベストプラクティス
1. 命名規則
-- 仮想カラムであることを明示する命名
CREATE TABLE orders (
unit_price DECIMAL(10,2),
quantity INT,
calc_total_amount DECIMAL(12,2) AS (unit_price * quantity) VIRTUAL,
derived_tax_amount DECIMAL(10,2) AS (calc_total_amount * 0.1) STORED
);
2. 段階的な導入
-- 既存テーブルへの仮想カラム追加
ALTER TABLE existing_table
ADD COLUMN calc_field INT AS (field1 + field2) VIRTUAL;
3. パフォーマンステスト
-- 実行計画の確認
EXPLAIN SELECT * FROM products WHERE calc_total_amount > 1000;
-- インデックス効果の確認（STOREDの場合）
EXPLAIN SELECT * FROM users WHERE full_name LIKE 'Smith%';
4. モニタリング
- 計算コストの高い仮想カラムは定期的にパフォーマンスを確認
- ストレージ使用量の変化を監視（STOREDの場合）
まとめ
- VIRTUAL: 軽い計算、低頻度アクセス、ストレージ節約重視
- STORED: 重い計算、高頻度アクセス、検索性能重視
Discussion