MySQL - Generated Columnsのソースを表示

== 概要 ==
MySQL Generated Columns (生成列) は、MySQL 5.7.6で導入されたテーブル定義機能である。<br>
テーブル内の他のカラムから値を計算して生成される特殊なカラムである。<br>
<br>
Generated Columnsには、<u>VIRTUAL (仮想列)</u> と <u>STORED (格納列)</u> の2つのタイプがある。<br>
* VIRTUAL (仮想列)
*: データを物理的に格納せず、行読み取り時にオンザフライで計算する。
*: デフォルトのタイプはVIRTUALである。
* STORED (格納列)
*: INSERT / UPDATE時に計算結果を物理的に格納する。
<br>
Generated Columnsは、繰り返し計算される値の自動化、JSON列からの値抽出とインデックス作成、複雑な式のクエリ簡略化に利用される。<br>
Generated Columnsに対してインデックスを作成することにより、計算結果に基づく高速な検索が可能になる。<br>
<br>
ストレージエンジンは、InnoDBが完全対応している。<br>
MyISAMは基本機能のみサポートしており、VIRTUAL列へのインデックス作成は非対応である。<br>
<br>
Generated Columnsは、関数インデックス (MySQL 8.0.13以降) と密接に関連している。<br>
<u>関数インデックスは内部的にVIRTUAL Generated Columnとして実装されている。</u><br>
<br><br>

== 基本構文 ==
==== CREATE TABLEでの定義 ====
Generated Columnsは、<code>CREATE TABLE</code> 文で定義する。<br>
<br>
基本構文を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    col_name data_type [GENERATED ALWAYS] AS (expression)
      [VIRTUAL | STORED]
      [NOT NULL | NULL]
      [UNIQUE [KEY]]
      [[PRIMARY] KEY]
      [COMMENT 'string']
 );
 </syntaxhighlight>
<br>
下表に、構文要素を示す。<br>
<br>
<center>
{| class="wikitable"
|+ 生成列の構文要素
|-
! 構文要素 !! 説明
|-
| <code>GENERATED ALWAYS</code>
|
* 省略可能なキーワード
* 明示的に生成列であることを示す
|-
| <code>AS (expression)</code>
|
* 生成式を指定
* 括弧で囲む必要がある
|-
| <code>VIRTUAL &#124; STORED</code>
|
* 生成タイプを指定
* 省略時はVIRTUALがデフォルト
|-
| <code>NOT NULL &#124; NULL</code>
| NULL許可属性
|-
| <code>UNIQUE [KEY]</code>
|
* UNIQUE制約
* VIRTUAL列とSTORED列の両方で使用可能
|-
| <code>[PRIMARY] KEY</code>
|
* プライマリキー制約
* STORED列でのみ使用可能
|}
</center>
<br>
* フルネーム生成の例
*: <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
*: <br>
* 税込価格計算の例
*: <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
*: <br>
* <code>GENERATED ALWAYS</code> キーワードの省略例
*: <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
<br>
==== ALTER TABLEでの追加 ====
既存のテーブルにGenerated Columnを追加する場合、<code>ALTER TABLE</code> 文を使用する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
 ADD COLUMN col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED];
 </syntaxhighlight>
<br>
* VIRTUAL列の追加例を以下に示す。
*: <syntaxhighlight lang="mysql">
 ALTER TABLE users
 ADD COLUMN email_domain VARCHAR(100) AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL;
 </syntaxhighlight>
*: <br>
* STORED列の追加例
*: <syntaxhighlight lang="mysql">
 ALTER TABLE products
 ADD COLUMN discounted_price DECIMAL(10, 2) AS (price * 0.9) STORED;
 </syntaxhighlight>
*: <br>
* Generated Columnの削除は、通常のカラムと同様に<code>DROP COLUMN</code>句を使用する。
*: <syntaxhighlight lang="mysql">
 ALTER TABLE users DROP COLUMN full_name;
 </syntaxhighlight>
*: <br>
* VIRTUALからSTOREDへの変換は、ALTERで可能である。
*: <syntaxhighlight lang="mysql">
 ALTER TABLE users
 MODIFY COLUMN full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) STORED;
 </syntaxhighlight>
<br>
<code>ALTER TABLE</code> 操作のアルゴリズムは、生成タイプによって異なる。<br>
VIRTUALカラムの追加は、<code>ALGORITHM=INSTANT</code> で実行される。<br>
STOREDカラムの追加は、<code>ALGORITHM=COPY</code> でテーブル全体を再構築する。<br>
<br>
<center>
{| class="wikitable"
|+ ALTER TABLE操作のアルゴリズム比較
! 操作 !! VIRTUAL !! STORED
|-
| カラム追加 || INSTANT (メタデータ変更のみ) || COPY (テーブル全体再構築)
|-
| カラム削除 (8.0.29+) || INSTANT || INSTANT
|-
| 式の変更 || COPY || COPY
|-
| VIRTUAL -> STORED変換 || COPY || -
|-
| 並行DML許可 || 可 (追加時) || 不可 (追加時)
|}
</center>
<br>
==== 式の記述ルール ====
Generated Columnsの式には、使用可能な要素と使用不可な要素が定義されている。<br>
<br>
下表に、使用可能な要素を示す。<br>
<center>
{| class="wikitable"
|+ 生成列の式で使用可能な要素
|-
! 要素 !! 説明
|-
| リテラル値 || 数値、文字列、日付リテラル
|-
| 同一テーブル内の他のカラム参照 || 同じ行の他のカラムを参照可能
|-
| 決定的組み込み関数 || 同じ入力に対して常に同じ結果を返す関数
|-
| 演算子 || 算術演算子、比較演算子、論理演算子
|}
</center>
<br>
下表に、使用不可な要素を示す。<br>
<center>
{| class="wikitable"
|+ 生成列の式で使用不可な要素
|-
! 要素 !! 説明
|-
| サブクエリ || SELECT文を式内で使用不可
|-
| ストアドプロシージャ / 関数 || ユーザ定義関数の呼び出し不可
|-
| ユーザ変数 || <code>@variable</code> 形式の変数不可
|-
| システム変数 || <code>@@variable</code> 形式の変数不可
|-
| 非決定的関数 || <code>NOW()</code>, <code>RAND()</code>, <code>CURRENT_USER()</code>, <code>CONNECTION_ID()</code> 等
|-
| <code>AUTO_INCREMENT</code>カラムの参照 || <code>AUTO_INCREMENT</code> カラムを式で参照不可
|-
| 動的パラメータ || プリペアドステートメントの ? パラメータ不可
|}
</center>
<br>
使用可能な関数と使用不可な関数の一覧を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 使用可能/不可な関数の例
! カテゴリ !! 使用可能 !! 使用不可
|-
| 文字列関数 || <code>CONCAT</code>, <code>SUBSTRING</code>, <code>UPPER</code>, <code>LOWER</code>, <code>LENGTH</code> || <code>LOAD_FILE</code>, <code>UUID</code>
|-
| 数学関数 || <code>ABS</code>, <code>ROUND</code>, <code>FLOOR</code>, <code>CEIL</code>, <code>MOD</code> || <code>RAND</code>
|-
| 日付関数 || <code>YEAR</code>, <code>MONTH</code>, <code>DAY</code>, <code>DATE_FORMAT</code> || <code>NOW</code>, <code>CURDATE</code>, <code>CURTIME</code>, <code>CURRENT_TIMESTAMP</code>
|-
| JSON関数 || <code>JSON_EXTRACT</code>, <code>JSON_UNQUOTE</code> || -
|-
| システム関数 || - || <code>CURRENT_USER</code>, <code>CONNECTION_ID</code>, <code>LAST_INSERT_ID</code>
|}
</center>
<br>
他のGenerated Columnを参照することも可能である。<br>
ただし、定義順序に従って参照する必要があり、循環参照は禁止されている。<br>
<br>
Generated Column間の参照例を以下に示す。<br>
<br>
以下の例では、<u>is_far</u> カラムが <u>distance_from_origin</u> カラムを参照している。<br>
<u>distance_from_origin</u> が先に定義されているため、参照可能である。<br>
<br>
 <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
<br><br>

== 仮想列 (VIRTUAL) ==
==== 動作原理 ====
VIRTUAL列は、データを物理的に格納しない。<br>
行読み取り時にオンザフライで式を評価して、値を計算する。<br>
<br>
VIRTUAL列の特性を以下に示す。<br>
* ストレージ消費
*: 基本的にストレージを消費しない
*: ただし、インデックス作成時はインデックス領域を消費する
* 計算タイミング
*: SELECT文実行時、WHERE句評価時
*: 行読み取りのたびに再計算される
* パフォーマンス
*: 読み取り時に計算コストが発生
*: 書き込み時は影響なし
* インデックス
*: セカンダリインデックスを作成可能 (InnoDB 8.0以降)
*: プライマリキーは作成不可
<br>
VIRTUAL列が適している用途を以下に示す。<br>
* 計算コストが低い式
*: 単純な文字列結合、算術演算
* アクセス頻度が低い列
*: 稀にしか参照されない計算値
* ストレージ節約が重要な場合
*: ディスク容量を節約したい
* 頻繁に更新されるテーブル
*: 書き込みパフォーマンスを重視
<br>
VIRTUAL列の評価タイミングを以下に示す。<br>
* SELECTリストに含まれる場合
*: 行読み取り時に評価
* WHERE句で使用される場合
*: 行フィルタリング時に評価
* ORDER BY句で使用される場合
*: ソート時に評価
* GROUP BY句で使用される場合
*: グループ化時に評価
<br>
==== 使用例 ====
* フルネーム生成の例
*: <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
*: <br>
* データ挿入の例
*: <syntaxhighlight lang="mysql">
 INSERT INTO employees (first_name, last_name) VALUES
 ('John', 'Doe'),
 ('Jane', 'Smith'),
 ('Alice', 'Johnson');
 </syntaxhighlight>
<br>
* VIRTUAL列を含む検索の例
*: <syntaxhighlight lang="mysql">
 SELECT id, full_name FROM employees;
 </syntaxhighlight>
*: <br>
*: 結果を以下に示す。
*: <syntaxhighlight lang="text">
 +----+--------------+
 | id | full_name    |
 +----+--------------+
 |  1 | John Doe     |
 |  2 | Jane Smith   |
 |  3 | Alice Johnson|
 +----+--------------+
 </syntaxhighlight>
<br>
* 日付分解の例
*: <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
*: <br>
* JSON値抽出の例
*: <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
*: <br>
* データ挿入の例
*: <syntaxhighlight lang="mysql">
 INSERT INTO user_profiles (id, data) VALUES
 (1, '{"email": "user1@example.com", "age": 25}'),
 (2, '{"email": "user2@example.com", "age": 30}');
 </syntaxhighlight>
*: <br>
* VIRTUAL列を使った検索の例
*: 以下の例では、VIRTUAL列 <code>age</code> がWHERE句で評価される。<br>
*: <syntaxhighlight lang="mysql">
 SELECT id, email, age FROM user_profiles WHERE age > 28;
 </syntaxhighlight>
<br><br>

== 格納列 (STORED) ==
==== 動作原理 ====
STORED列は、計算結果を物理的にディスクに格納する。<br>
INSERT/UPDATE時に式を評価して、計算結果をテーブルに保存する。<br>
<br>
下表に、STORED列の特性を示す。<br>
<center>
{| class="wikitable"
|+ STORED列の特性
|-
! 特性 !! 説明
|-
| ストレージ消費
|
* データ型に応じたストレージを消費する
* インデックスも追加のストレージを消費する
|-
| 計算タイミング
|
* INSERT時、UPDATE時 (依存カラムが更新された場合)
* 読み取り時は再計算されない
|-
| パフォーマンス
|
* 読み取り時は高速 (計算不要)
* 書き込み時に計算コストが発生
|-
| インデックス || セカンダリインデックス、プライマリキー、UNIQUE制約全て可能
|}
</center>
<br>
下表に、STORED列が適している用途を示す。<br>
<center>
{| class="wikitable"
|+ STORED列が適している用途
|-
! 用途 !! 説明
|-
| 計算コストが高い式 || 複雑な数学計算、文字列処理
|-
| アクセス頻度が高い列 || 頻繁に検索される計算値
|-
| インデックスを作成したい列 || プライマリキーやUNIQUE制約が必要な場合
|-
| 参照頻度が更新頻度より高い場合 || 読み取りパフォーマンスを重視
|}
</center>
<br>
下表に、STORED列の更新トリガー条件を示す。<br>
<center>
{| class="wikitable"
|+ STORED列の更新トリガー条件
|-
! 条件 !! 説明
|-
| INSERT時 || 必ず計算される
|-
| UPDATE時
|
* 依存カラムが更新された場合のみ再計算
* 依存カラムが更新されない場合は再計算されない
|}
</center>
<br>
==== 使用例 ====
* 税込価格計算の例
*: <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
*: <br>
* データ挿入の例
*: <syntaxhighlight lang="mysql">
 INSERT INTO products (id, name, price) VALUES
 (1, 'Product A', 100.00),
 (2, 'Product B', 200.00);
 </syntaxhighlight>
*: <br>
* 格納された値の確認
*: <syntaxhighlight lang="mysql">
 SELECT id, name, price, price_with_tax FROM products;
 </syntaxhighlight>
*: <br>
*: 結果を以下に示す。
*: <syntaxhighlight lang="text">
 +----+-----------+--------+----------------+
 | id | name      | price  | price_with_tax |
 +----+-----------+--------+----------------+
 |  1 | Product A | 100.00 |         110.00 |
 |  2 | Product B | 200.00 |         220.00 |
 +----+-----------+--------+----------------+
 </syntaxhighlight>
*: <br>
* 価格更新時の再計算例
*: <syntaxhighlight lang="mysql">
 UPDATE products SET price = 150.00 WHERE id = 1;
 </syntaxhighlight>
*: <br>
* 更新後の確認
*: <syntaxhighlight lang="mysql">
 SELECT id, name, price, price_with_tax FROM products WHERE id = 1;
 </syntaxhighlight>
*: <br>
*: 結果を以下に示す。
*: <syntaxhighlight lang="text">
 +----+-----------+--------+----------------+
 | id | name      | price  | price_with_tax |
 +----+-----------+--------+----------------+
 |  1 | Product A | 150.00 |         165.00 |
 +----+-----------+--------+----------------+
 </syntaxhighlight>
*: <br>
* STORED列をプライマリキーとして使用する例
*: <syntaxhighlight lang="mysql">
 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)
 );
 </syntaxhighlight>
*: <br>
* JSON列からの値抽出とSTOREDの組み合わせ例
*: 以下の例では、JSON列から抽出した値をSTORED列に格納し、インデックスを作成している。
*: これにより、JSON列への直接検索よりも高速な検索が可能になる。
*: <syntaxhighlight lang="mysql">
 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)
 );
 </syntaxhighlight>
<br><br>

== VIRTUALとSTOREDの比較 ==
VIRTUALとSTOREDの違いを体系的に比較する。<br>
<br>
<center>
{| class="wikitable"
|+ VIRTUAL vs STORED 比較表
! 項目 !! VIRTUAL !! STORED
|-
| ストレージ使用 || なし (インデックス除く) || あり (データ型に応じた容量)
|-
| 計算タイミング || 読み取り時 (SELECT, WHERE, ORDER BY等) || 書き込み時 (INSERT, UPDATE)
|-
| 読み取りパフォーマンス || 計算コスト発生 (遅い) || 高速 (計算不要)
|-
| 書き込みパフォーマンス || 影響なし (高速) || 計算コスト発生 (遅い)
|-
| セカンダリインデックス || 対応 (InnoDB 8.0+) || 対応
|-
| プライマリキー || 非対応 || 対応
|-
| UNIQUE制約 || 対応 || 対応
|-
| 外部キー参照 || 非対応 || 一部制約あり (CASCADE等不可)
|-
| デフォルト || はい || いいえ
|-
| ALTER TABLE追加アルゴリズム || INSTANT || COPY
|}
</center>
<br>
下表に、選択基準を示す。<br>
<br>
<center>
{| class="wikitable"
|+ VIRTUAL / STORED の選択基準
|-
! タイプ !! 選択すべき場合 !! 説明
|-
| rowspan="5" | VIRTUAL
| ストレージ容量を節約したい || ディスク容量が限られている
|-
| 計算式が単純 || 文字列結合、単純な算術演算
|-
| アクセス頻度が低い || 稀にしか参照されない
|-
| 更新頻度が高い || 頻繁にINSERT / UPDATEされる
|-
| セカンダリインデックスのみ必要 || プライマリキーとして使用しない
|-
| rowspan="5" | STORED
| 読み取りパフォーマンスが重要 || 頻繁に検索される
|-
| 計算式が複雑 || 複雑な文字列処理、JSON解析、数学計算
|-
| プライマリキーとして使用 || テーブルの主キーとして必要
|-
| インデックスを作成したい || 高速検索が必要
|-
| 更新頻度が低い || 書き込みパフォーマンスへの影響が小さい
|}
</center>
<br>
パフォーマンス比較例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 -- 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)
 );
 </syntaxhighlight>
<br>
100万行のINSERTパフォーマンス比較を以下に示す。<br>
* VIRTUAL版
*: 約10秒 (計算不要)
* STORED版
*: 約12秒 (計算と格納が必要)
<br>
インデックス付き検索パフォーマンス比較を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT * FROM test_virtual WHERE calculated = 5000;
 SELECT * FROM test_stored WHERE calculated = 5000;
 </syntaxhighlight>
<br>
* VIRTUAL版
*: 約0.05秒 (インデックス使用)
* STORED版
*: 約0.05秒 (インデックス使用)
<br>
インデックスを使用する場合、検索パフォーマンスはほぼ同等である。<br>
<br><br>

== インデックスとの組み合わせ ==
==== VIRTUAL列へのインデックス ====
MySQL 8.0以降のInnoDBでは、VIRTUAL列にセカンダリインデックスを作成できる。<br>
<br>
VIRTUAL列インデックスの特性を以下に示す。<br>
* インデックス作成
*: セカンダリインデックスのみ作成可能
*: プライマリキーは作成不可
* 実体化タイミング
*: INSERT/UPDATE時にインデックスレコード内で値が実体化される
*: インデックスレコードには計算結果が格納される
* パフォーマンス影響
*: INSERT/UPDATE時にインデックス計算コストが発生
*: SELECT時はインデックスから直接読み取り可能
<br>
* VIRTUAL列へのインデックス作成例
*: <syntaxhighlight lang="mysql">
 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)
 );
 </syntaxhighlight>
*: <br>
* インデックスを使用した検索例
*: <syntaxhighlight lang="mysql">
 SELECT id, full_name FROM users WHERE full_name = 'John Doe';
 </syntaxhighlight>
*: <br>
* 実行計画を確認する。
*: <syntaxhighlight lang="mysql">
 EXPLAIN SELECT id, full_name FROM users WHERE full_name = 'John Doe';
 </syntaxhighlight>
*: <br>
*: 結果を以下に示す。
*: <u>idx_full_name</u> インデックスが使用されていることが確認できる。
*: <syntaxhighlight lang="text">
 +----+-------------+-------+------+---------------+---------------+---------+-------+
 | id | select_type | table | type | possible_keys | key           | key_len | rows  |
 +----+-------------+-------+------+---------------+---------------+---------+-------+
 |  1 | SIMPLE      | users | ref  | idx_full_name | idx_full_name | 404     |     1 |
 +----+-------------+-------+------+---------------+---------------+---------+-------+
 </syntaxhighlight>
*: <br>
* カバリングインデックスの例
*: <syntaxhighlight lang="mysql">
 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)
 );
 </syntaxhighlight>
*: <br>
* カバリングインデックスを使用したクエリ例
*: 以下の例では、インデックスのみから結果を取得でき、テーブルアクセスが不要になる。
*: <syntaxhighlight lang="mysql">
 SELECT full_name, salary FROM employees WHERE full_name LIKE 'John%';
 </syntaxhighlight>
<br>
==== JSON列への仮想インデックス ====
<u>JSON列に対して直接インデックスを作成することはできない。</u><br>
<u>そのため、Generated Columnを使用してJSON列から値を抽出し、その列にインデックスを作成する。</u><br>
<br>
JSON列仮想インデックスのパターンを以下に示す。<br>
# JSON列から値を抽出するGenerated Columnを定義
# Generated Columnにインデックスを作成
# WHERE句でGenerated Columnを使用して検索
<br>
* JSON列仮想インデックスの例
*: <syntaxhighlight lang="mysql">
 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)
 );
 </syntaxhighlight>
*: <br>
* データ挿入例
*: <syntaxhighlight lang="mysql">
 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"}');
 </syntaxhighlight>
*: <br>
* カテゴリ検索例
*: <syntaxhighlight lang="mysql">
 SELECT id, name, category, price FROM products WHERE category = 'electronics';
 </syntaxhighlight>
*: <br>
* 価格範囲検索例
*: <syntaxhighlight lang="mysql">
 SELECT id, name, category, price FROM products WHERE price BETWEEN 100 AND 500;
 </syntaxhighlight>
*: <br>
* 複合インデックスの例
*: <syntaxhighlight lang="mysql">
 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)
 );
 </syntaxhighlight>
*: <br>
* 複合インデックスを使用した検索例
*: <syntaxhighlight lang="mysql">
 SELECT id, log_type, log_timestamp
 FROM user_logs
 WHERE log_type = 'login' AND log_timestamp > '2025-01-01';
 </syntaxhighlight>
<br>

==== 関数インデックスとの関係 ====
<u>MySQL 8.0.13以降では、関数インデックス (Functional Index) がサポートされている。</u><br>
<u>関数インデックスは、内部的にVIRTUAL Generated Columnとして実装されている。</u><br>
<br>
関数インデックスの構文を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 CREATE INDEX index_name ON table_name ((expression));
 </syntaxhighlight>
<br>
関数インデックスの例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100)
 );
 
 CREATE INDEX idx_email_domain ON users ((SUBSTRING_INDEX(email, '@', -1)));
 </syntaxhighlight>
<br>
上記のインデックスは、内部的に以下と等価である。<br>
 <syntaxhighlight lang="mysql">
 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);
 </syntaxhighlight>
<br>
下表に、関数インデックスとGenerated Columnの比較を示す。<br>
<br>
<center>
{| class="wikitable"
|+ 関数インデックス vs Generated Column + インデックス
! 項目 !! 関数インデックス !! Generated Column + インデックス
|-
| 構文簡潔性 || シンプル (1ステップ) || 複雑 (2ステップ)
|-
| カラム可視性 || 隠し列 (SELECTで見えない) || 通常列 (SELECTで見える)
|-
| 明示的参照 || 不可 || 可能
|-
| オプティマイザ認識 || 式パターンマッチングが必要 || 明示的カラム参照で最適化
|-
| 内部実装 || VIRTUAL Generated Column || VIRTUAL Generated Column
|-
| 対応バージョン || 8.0.13+ || 5.7.6+
|}
</center>
<br>
関数インデックスを使用した検索例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT id, email FROM users WHERE SUBSTRING_INDEX(email, '@', -1) = 'example.com';
 </syntaxhighlight>
<br>
オプティマイザは、式を認識して関数インデックスを使用する。<br>
<br>
<center>
{| class="wikitable"
|+ Generated Columnのメリット
|-
! メリット !! 説明
|-
| 明示的にカラムを参照できる || SELECT句で直接使用可能
|-
| 複数のインデックスで共有可能 || 同じ計算結果を複数のインデックスで利用
|-
| バージョン互換性 || MySQL 5.7.6以降で使用可能
|}
</center>
<br>
下表に、<br>
<center>
{| class="wikitable"
|+ 関数インデックスのメリット
|-
! メリット !! 説明
|-
| 構文がシンプル || 1ステップでインデックス作成
|-
| スキーマ汚染を回避 || 不要なカラムがテーブル定義に表示されない
|}
</center>
<br><br>

== サンプルクエリ ==
実用的なGenerated Columnsの使用例を示す。<br>
<br>
* フルネーム生成と検索の例
*: <syntaxhighlight lang="mysql">
 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';
 </syntaxhighlight>
*: <br>
* 税込価格計算の例
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* 日付分解とパーティショニングの例
*: <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
*: <br>
* JSON列からの値抽出とインデックスの例
*: <syntaxhighlight lang="mysql">
 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';
 </syntaxhighlight>
*: <br>
* POINT型座標計算の例
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* メールアドレスドメイン抽出の例
*: <syntaxhighlight lang="mysql">
 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';
 </syntaxhighlight>
*: <br>
* ハッシュ値計算とユニーク制約の例
*: <syntaxhighlight lang="mysql">
 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'
 </syntaxhighlight>
*: <br>
* 大文字小文字を区別しない検索の例
*: <syntaxhighlight lang="mysql">
 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%';
 </syntaxhighlight>
<br><br>

== 制限事項 ==
Generated Columnsには、いくつかの制限事項がある。<br>
<br>
DML操作の制限を以下に示す。<br>
* INSERT時の値指定
*: Generated Columnには値を直接指定できない
*: DEFAULTのみ指定可能
* UPDATE時の値変更
*: Generated Columnを直接更新できない
*: 依存カラムを更新すると自動的に再計算される
<br>
INSERT時の値指定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 正常: 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
 </syntaxhighlight>
<br>
外部キー制約の制限を以下に示す。<br>
* VIRTUAL列
*: 外部キーとして使用不可
* STORED列
*: 外部キーとして使用可能
*: ただし、CASCADE、SET NULL、SET DEFAULTは使用不可
<br>
外部キー制約の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
<br>
パーティショニングの制限を以下に示す。<br>
* 式は決定的である必要がある
*: 同じ入力に対して常に同じ結果を返す
* 非決定的関数は使用不可
*: NOW(), RAND()等
<br>
パーティショニングの例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 -- 正常: 決定的関数
 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
 </syntaxhighlight>
<br>
レプリケーションの制限を以下に示す。<br>
* マスタとスレーブで生成式が同一である必要がある
*: 異なる式の場合、レプリケーションエラーが発生
* 非決定的関数の使用は避ける
*: マスタとスレーブで結果が異なる可能性
<br>
ストレージエンジンの対応状況を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ ストレージエンジン対応状況
! ストレージエンジン !! VIRTUAL !! STORED !! VIRTUALセカンダリインデックス
|-
| InnoDB || 完全対応 || 完全対応 || 対応 (8.0+)
|-
| MyISAM || 基本機能のみ || 基本機能のみ || 非対応
|-
| Memory || 非対応 || 非対応 || 非対応
|-
| CSV || 非対応 || 非対応 || 非対応
|}
</center>
<br>
トリガーとの関係を以下に示す。<br>
* BEFORE INSERT/UPDATE
*: トリガー実行直後にGenerated Column計算
* AFTER INSERT/UPDATE
*: Generated Column計算後にトリガー実行
<br>
トリガー内でのGenerated Column参照例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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 ;
 </syntaxhighlight>
<br>
上記のトリガーでは、AFTER INSERTでGenerated Column <u>NEW.full_name</u> を参照している。<br>
<br><br>

== パフォーマンス ==
Generated Columnsのパフォーマンス特性と最適化方法を示す。<br>
<br>
下表に、VIRTUALのパフォーマンス特性を示す。<br>
<center>
{| class="wikitable"
|+ VIRTUALのパフォーマンス特性
|-
! 特性 !! 説明
|-
| 書き込み性能
|
* INSERT/UPDATEへの影響なし
* インデックスがある場合はインデックス更新コスト発生
|-
| 読み取り性能
|
* 行読み取りごとに式を評価
* 計算コストが高い式では遅延発生
|-
| ストレージ
|
* 基本的に追加容量不要
* インデックスがある場合はインデックス領域消費
|}
</center>
<br>
下表に、STOREDのパフォーマンス特性を示す。<br>
<center>
{| class="wikitable"
|+ STOREDのパフォーマンス特性
|-
! 特性 !! 説明
|-
| 書き込み性能
|
* INSERT/UPDATE時に式を評価
* 計算コストと格納コストが発生
|-
| 読み取り性能
|
* 計算不要で高速
* 通常カラムと同等
|-
| ストレージ
|
* データ型に応じた容量消費
* インデックスも追加容量消費
|}
</center>
<br>
* パフォーマンステストの例
*: <syntaxhighlight lang="mysql">
 -- テストテーブル作成
 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秒
 </syntaxhighlight>
*: <br>
* インデックス付きVIRTUAL列のパフォーマンス
*: <syntaxhighlight lang="mysql">
 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秒 (インデックス使用)
 </syntaxhighlight>
*: <br>
* カバリングインデックスによる最適化
*: <syntaxhighlight lang="mysql">
 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列の再計算も不要
 </syntaxhighlight>
<br>
パフォーマンス最適化のガイドラインを以下に示す。<br>
<br>
VIRTUALを最適化するには、以下を実施する。<br>
* 計算式を単純に保つ
*: 複雑な式は避ける
* インデックスを活用
*: 検索対象列にはインデックスを作成
* カバリングインデックスを使用
*: 必要なカラムを全てインデックスに含める
<br>
STOREDを最適化するには、以下を実施する。<br>
* 更新頻度を考慮
*: 頻繁に更新される列では使用を避ける
* ストレージサイズを考慮
*: 大きなデータ型は避ける
* 依存カラムの更新を最小化
*: 不要な更新を避ける
<br>
共通の最適化として、以下を実施する。<br>
* 適切なタイプ選択
*: VIRTUALとSTOREDの特性を理解して選択
* インデックス戦略
*: 必要最小限のインデックスを作成
* 式の決定性確保
*: 決定的関数のみ使用
<br>
下表に、パフォーマンス比較の目安を示す。<br>
<br>
<center>
{| class="wikitable"
|+ パフォーマンス比較の目安
! 操作 !! 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型)
|}
</center>
<br>
上記は目安であり、実際の値は式の複雑さ、データ型、ハードウェア構成により変動する。<br>
<br><br>

== 関連ページ ==
* [[MySQL - インデックス]]
*: インデックスの種類と作成方法
*: セカンダリインデックス、関数インデックス
* [[MySQL - JSON関数]]
*: JSON_EXTRACT, JSON_UNQUOTE等の関数
*: JSON列からの値抽出
* [[MySQL - ALTER TABLE]]
*: ALTER TABLE文の構文と操作
*: ALGORITHM, LOCK句の詳細
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,MySQL,SQL,Database,Generated Columns,VIRTUAL,STORED,Index,JSON,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - MySQLの生成列 (VIRTUAL/STORED) の詳細解説 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]