メリークリスマス🎅 @10kei です！
withアドベントカレンダー18日目です。
MySQLには、テーブル内の他のカラムの値をもとに、計算結果を保存することができるGenerated Columnという便利な機能があります。
今回、Generated Columnを学ぶ機会がありましたので、その使い方や、試してみたことなどについてまとめてみようと思います。
本記事はMySQLのバージョン8.0における仕様となります。ご利用のMySQLバージョン次第で、記載の内容と異なる挙動になる場合があります。
Generated Columnとは
Generated Column（生成列）は、カラムを定義する際に式を定義することで、その値を勝手に生成してくれる機能です。
式には他のカラムの値を用いることができるので、Generated Column以外のカラムの値次第で、動的に値を入力するのが、主な使い方になると思います。
どうやって使えばいいの？
実際に例となるテーブルを作成しながら、Generated Columnの使い方を見ていきましょう。
商品の在庫管理を行うテーブル、itemsを作ってみます。
（今回は例なので、全カラムINTEGERで作成します）
- id
- stock: 商品の在庫数
- price: 単価
- total_price: 在庫している商品の合計金額
- stock * price で計算します
total_priceがGenerated Columnです。
では、早速テーブルを作ってみましょう。
mysql> CREATE TABLE items (
-> id INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> stock INT,
-> price INT,
-> total_price INT GENERATED ALWAYS AS (stock * price)
-> );
Query OK, 0 rows affected (0.15 sec)
カラム名 データ型 GENERATED ALWAYS AS ( 式 )
とすることで、Generated Columnを定義できます。
[GENERATED ALWAYS]は省略可能です。
上の例であれば、
total_price INT AS (stock * price)としても
Generated Columnが作成できます。
さて、無事にテーブルが作成できました！
続いてテストデータを挿入してみます。
mysql> INSERT INTO items (stock, price) VALUES (10, 500), (5, 200), (8, 150);
Query OK, 3 rows affected (0.04 sec)
作成されたデータを見てみると…？
mysql> SELECT * FROM items;
+----+-------+-------+-------------+
| id | stock | price | total_price |
+----+-------+-------+-------------+
| 1 | 10 | 500 | 5000 |
| 2 | 5 | 200 | 1000 |
| 3 | 8 | 150 | 1200 |
+----+-------+-------+-------------+
3 rows in set (0.02 sec)
total_priceにはデータを挿入していないにも関わらず、stockとpriceの積が値として入っていますね！
続いて、id = 1のデータを更新してみます。
mysql> UPDATE items SET stock = 15 WHERE id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM items;
+----+-------+-------+-------------+
| id | stock | price | total_price |
+----+-------+-------+-------------+
| 1 | 15 | 500 | 7500 |
| 2 | 5 | 200 | 1000 |
| 3 | 8 | 150 | 1200 |
+----+-------+-------+-------------+
3 rows in set (0.00 sec)
stockの値が更新されたことで、total_priceの値も変わっていますね。
これがGenerated Columnのとても便利なポイントですね！
感覚としては、Excelの関数に似た様なことが、DBでもできるよ！という感じでしょうか。
VIRTUALとSTORED
式の後に、VIRTUALまたはSTOREDとオプションを指定することで、Generated Columnの値の持ち方を指定することができます。
VIRTUAL
- VIRTUALを指定した場合、テーブルにデータを挿入したり、更新したりした際に、Generated Columnの値が計算されて保存されることはありません
- 必要なとき（SELECTする際など）に、その都度計算されます
- 実際にデータとして保存されるわけではないため、ストレージ容量を使用せず、節約できます
- 計算後の値に、セカンダリインデックス1を作成することができます
-
VIRTUALまたはSTOREDのいずれも指定しない場合、VIRTUALがデフォルトで指定されます
STORED
- STOREDを指定した場合、列の値は、テーブルにデータを挿入したり、更新したりした際に評価され、保存されます
- 保存された列にはストレージ容量が必要です
- VIRTUALと異なり、プライマリキーを設定することが可能です
これらの違いがあるので、用途に応じて使い分けましょう。
気になる挙動を色々試してみる
Generated Columnに対して、こういうときはどういう挙動になるの？という種々のパターンを試してみました。
Generated Columnを直接更新しようとすると？
先ほどのitemsテーブルを使って、total_priceを直接更新しようとしてみます。
mysql> UPDATE items SET total_price = 10000 WHERE id = 1;
ERROR 3105 (HY000): The value specified for generated column 'total_price' in table 'items' is not allowed.
✅ きちんとERRORになりますね！
Generated ColumnにNOT NULL制約はつけられる？
苗字と名前をそれぞれのカラムに持ち、フルネームをGenerated Columnで生成するテーブルを作成してみます。
CREATE TABLE names (
-> first_name VARCHAR(10),
-> last_name VARCHAR(10),
-> full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)) NOT NULL
-> );
Query OK, 0 rows affected (0.14 sec)
-- 姓名どちらにもデータを挿入してみる
mysql> INSERT INTO names (first_name, last_name) VALUES ('Taro', 'Hoge');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM names;
+------------+-----------+-----------+
| first_name | last_name | full_name |
+------------+-----------+-----------+
| Taro | Hoge | Taro Hoge |
+------------+-----------+-----------+
1 row in set (0.01 sec)
-- first_nameにのみデータを挿入しようとしてみると、NOT NULL制約によりエラーになる
mysql> INSERT INTO names (first_name) VALUES ('Hanako');
ERROR 1048 (23000): Column 'full_name' cannot be null
✅ Generated ColumnにNOT NULL制約をつけることはできる
注意点として、Generated Columnで参照されるカラムに、NOT NULL制約は必ずしも付いている必要はありません。
上の例のように、参照されるカラムの値がnullの時、Generated Columnもnullになってしまうような定義となっていると、参照されるカラムも暗黙的にNOT NULL制約がついてしまうと考えて良いでしょう。2
Generated ColumnにDEFAULT値はつけられる？
CREATE TABLE names (
-> first_name VARCHAR(10),
-> last_name VARCHAR(10),
-> full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)) DEFAULT 'anonymous'
-> );
ERROR 1221 (HY000): Incorrect usage of DEFAULT and generated column
❌ Generated ColumnにDEFAULTの値は設定できません！
Generated Columnと参照されるカラムとで、データ型が異なっても良い？
（こんなテーブルはなかなか無いと思いますが）VARCHAR型のデータを使って、INTEGER型のGenerated Columnにデータを生成してみます。
mysql> CREATE TABLE text_values (
-> value1 VARCHAR(10),
-> value2 VARCHAR(10),
-> total INT GENERATED ALWAYS AS (CAST(value1 AS UNSIGNED) + CAST(value2 AS UNSIGNED))
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO text_values (value1, value2) VALUES ('1', '2');
Query OK, 1 row affected (0.05 sec)
-- value1 + value2の計算が問題なく行われ、totalに3が入力されている
mysql> SELECT * FROM text_values;
+--------+--------+-------+
| value1 | value2 | total |
+--------+--------+-------+
| 1 | 2 | 3 |
+--------+--------+-------+
1 row in set (0.01 sec)
もう一つ、点数(INTEGER型)の値によって、成績(ENUM型)を生成するテーブルを例として見てみます。
mysql> CREATE TABLE test_scores (
-> score INT,
-> grade ENUM('不可', '可', '良', '優') GENERATED ALWAYS AS (
-> CASE
-> WHEN score < 60 THEN '不可'
-> WHEN score BETWEEN 60 AND 69 THEN '可'
-> WHEN score BETWEEN 70 AND 79 THEN '良'
-> ELSE '優'
-> END
-> )
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO test_scores (score) VALUES (75);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM test_scores;
+-------+-------+
| score | grade |
+-------+-------+
| 75 | 良 |
+-------+-------+
1 row in set (0.00 sec)
✅ Generated Columnと型が違うカラムを参照しても、条件に使用しているだけだったり、適切にCASTしている場合は問題なし！
式に動的な値を使うことは可能？
生年月日のデータと、CURDATE()により取得する今日の日付から、年齢を概算するGenerated Columnを例として考えてみます。
mysql> CREATE TABLE birthdays (
-> birthday DATE,
-> age INT GENERATED ALWAYS AS (YEAR(CURDATE()) - YEAR(birth_date)) STORED
-> );
ERROR 3763 (HY000): Expression of generated column 'age' contains a disallowed function: curdate.
❌ CURDATE()やNOW()など、環境や実行時の状態によって得られる結果が変わる関数は、Generated Columnの式に使用することはできません。
Generated ColumnにUNIQUE制約をつけられる？
書籍情報を管理するテーブルを考えてみます。
タイトルと著者は、同名の可能性がありますが、タイトルも著者も同じデータ（同じ本）の登録を禁止したいとします。
mysql> CREATE TABLE books (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> title VARCHAR(255) NOT NULL,
-> author VARCHAR(255) NOT NULL,
-> unique_identifier VARCHAR(512) GENERATED ALWAYS AS (CONCAT(title, '-', author)) UNIQUE
-> );
Query OK, 0 rows affected (0.09 sec)
-- 本のタイトル、著者のどちらかが他のデータと被っていても、挿入可能
mysql> INSERT INTO books (title, author) VALUES ('AAA', 'hoge'), ('BBB', 'hoge'), ('BBB', 'fuga');
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM books;
+----+-------+--------+-------------------+
| id | title | author | unique_identifier |
+----+-------+--------+-------------------+
| 1 | AAA | hoge | AAA-hoge |
| 2 | BBB | hoge | BBB-hoge |
| 3 | BBB | fuga | BBB-fuga |
+----+-------+--------+-------------------+
3 rows in set (0.01 sec)
-- タイトルも、著者も被っている(登録済み)のデータの挿入は、UNIQUE制約により不可
mysql> INSERT INTO books (title, author) VALUES ('AAA', 'hoge');
ERROR 1062 (23000): Duplicate entry 'AAA-hoge' for key 'books.unique_identifier'
✅ UNIQUE制約はGenerated Columnにも付与可能！
Generated Columnをキーにしたindexを作成するやり方を確認したい
先述した通り、indexはGenerated Columnにも貼れます。
VIRTUALとSTOREDで、それぞれ確認してみます。
VIRTUAL
上で例に使ったitemsを、total_priceをキーにしたindexを作成するように作り直してみます。
mysql> CREATE TABLE items (
-> id INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> stock INT,
-> price INT,
-> total_price INT GENERATED ALWAYS AS (stock * price) VIRTUAL,
-> INDEX idx_total_price (total_price) -- セカンダリインデックスを作成
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> SHOW INDEX FROM items;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| items | 1 | idx_total_price | 1 | total_price | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.08 sec)
✅ Generated Columnをキーにしたindexを作成することができました！
STORED
先述の通り、STOREDの場合、VIRTUALと異なりGenerated Columnをプライマリキーにすることも可能になります。
例として、メールアドレスからログイン時に用いるhashを自動で生成するテーブルを作成してみましょう。
そして、生成されたlogin_hashをプライマリキーとして利用することにします。
mysql> CREATE TABLE user_mails (
-> email VARCHAR(255) NOT NULL,
-> login_hash VARCHAR(64) GENERATED ALWAYS AS (SHA2(email, 256)) STORED,
-> PRIMARY KEY (login_hash) -- プライマリキーとして利用
-> );
Query OK, 0 rows affected (0.14 sec)
mysql> SHOW INDEX FROM user_mails;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_mails | 0 | PRIMARY | 1 | login_hash | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.03 sec)
-- 試しにデータを挿入してみる
mysql> INSERT INTO user_mails (email) VALUES ('hoge@example.com');
Query OK, 1 row affected (0.01 sec)
-- 再度同じメールアドレスのデータを挿入しようとすると、プライマリーキー制約によってERRORになる
mysql> INSERT INTO user_mails (email) VALUES ('hoge@example.com');
ERROR 1062 (23000): Duplicate entry '4e3a8e26a97bf093fbd651b1c4bd9aa785946d74460bc396cdbb42e2ccab40a0' for key 'user_mails.PRIMARY'
✅ STOREDを指定することで、セカンダリインデックスのみでなく、プライマリインデックスを作成することも可能！
実際に使ってみて思ったこと
Generated Columnは、上で試してみた通りかなり柔軟な定義が可能で
非常に便利な機能であると思いました。
反面、「多くのケースで、アプリケーション側の制御で事足りるのでは？🤔」 とも感じました。
例えば、ただ単に姓名を連結してフルネームを生成する というケースでは
Generated Columnを用いるよりも、連結の処理をアプリケーション側に寄せた方が明らかに分かりやすいですし
仕様の変更が発生した際の保守性も高いでしょう。
また、複数人で開発を行っている場合
〇〇テーブルの〇〇カラムがGenerated Columnであること
かつ、その定義された式の内容を簡単に把握できるようにしておかないと
人によっては挙動の理解ができず、生産性の低下を招くかもしれません。
Generated Columnには適切にコメントを付記し、仕様について迷わせないような工夫が大切なように思いました。
どんな時にGenerated Columnを使うと効果的だろう？
では、効果的にGenerated Columnを使うことができるケースは、どんな時でしょうか？いくつか思いつくものを考えてみます。
検索やフィルタリングの効率化
計算の結果や、正規化を行ったデータなどをGenerated Columnで生成することで、検索を容易にする。Generated Columnをキーにしたindexを作成すれば、必要なデータに、高速にアクセスできる。
- フルネームの完全一致検索
- 緯度と経度をもつ位置データから地理情報を計算し、結果にindexを作成する など
データの整形
保存データの整形をGenerated Columnで行うことによりデータの一貫性を保ち、アプリケーション側での処理負担を軽減する。
- 大文字・小文字を区別しない検索のためのデータ保存 など
ETLプロセスの補助
Generated Columnを利用して中間データを効率的に作成する。
- 生データの前処理を行った結果をGenerated Columnに保存し、後続のETL処理を簡素化する など
終わりに
今回、Generated Columnの各仕様について、さまざまな使用例を挙げつつまとめてみましたが、記事作成にあたって改めてその柔軟性と可能性に気付かされました。
工夫次第で、従来の手法では難しかった課題を解決し、より効率的なソリューションを生み出せる機能だな〜と感じました。
利用にあたっては注意すべきポイントもありますが、アプリケーションの実装に行き詰まった際には、Generated Columnの存在を思い出してみてください。
用法用量を守って、ステキなGenerated Columnライフを！⛄
参考
-
CREATE TABLE and Generated Columns
mySQL公式のドキュメント