はじめに
MySQLでテーブル設計をしていると、次のような場面に遭遇することがあります。
- この値は毎回アプリケーション側で計算して保存している
- 他のカラムから必ず導出できる値を持っている
- 検索やインデックス用に欲しいが、冗長なカラムを増やしたくない
こうした課題を解決する手段の一つがGenerated Column です。
Generated Columnを一言で表すと
他のカラムの値から自動的に計算されるカラム
を指します。
アプリケーション側で値を設定する必要はなく、
MySQLが定義された式に基づいて自動的に値を生成・管理することができます。
例:姓、名、フルネームを保存する場合
CREATE TABLE users (
first_name VARCHAR(50),
last_name VARCHAR(50),
full_name VARCHAR(101)
GENERATED ALWAYS AS (CONCAT(last_name, ' ', first_name)) -- Generated Column
);
この例では、first_name と last_name を登録するだけで
full_nameが自動的に計算されます
full_name に対して INSERT や UPDATE を行うことはできません
そのため、計算結果の整合性が常に保証されます。
VIRTUAL と STORED
Generated Column には VIRTUAL と STORED の2種類があります。
VIRTUAL（デフォルト）
full_name VARCHAR(101)
GENERATED ALWAYS AS (...) VIRTUAL
特徴
- 実体データを持たない
- SELECT 時に値を計算する（計算コストが発生する）
- ディスク容量を消費しない
主な用途
- 表示専用のカラム
- 軽量な計算結果
STORED
full_name VARCHAR(101)
GENERATED ALWAYS AS (...) STORED
特徴
- 計算結果を物理的に保存する
- INSERT / UPDATE 時に計算される
- ディスク容量を消費する
- 読み取り性能が高い
主な用途
- WHERE句や ORDER BY で使用する値
- インデックスを張りたいカラム
おわりに
最後まで読んでいただきありがとうございます！
頻繁にSELECTしないデータはVIRTUALのGenerated Columnを使うのが良さそうです！
一方で計算式が複雑でコストが高かったり、INDEXを貼ったりする場合はSTOREDのGenerated Columnを使うのが良さそうです！