第150回　Generated Columnを利用してみる

今回は、MySQL 5.7.6から導入されたGenerated Columnに関して紹介していきます。この機能はテーブル定義で宣言した式に従って値を自動で生成し、カラムとして扱えるようになる機能です。アプリケーションで計算した値を加える場合に比べると、手作業などで挿入や更新を行った際に再計算を自動で行ってくれ、うっかりミスを防げる利点があります。

今回はそんな便利に使えるGenerated Columnを紹介していきます。

検証環境

今回は第125回　phpMyAdminでDockerで建てたMySQLにアクセスするで紹介したdocker-composeを利用して作成します。手元で簡単に試せるように、GitHubのわたしのレポジトリにサンプルコードとして置いてあるので、気軽に試したい方はgit cloneして試してみてください。試すにはdockerとdocker-composeが必要です。

Generated Columnとは？

Generated Columnは、定義した式に従って値を生成してカラムのように扱える仕組みです。Generate Columnには種類が2種類あり、VIRTUALとSTOREDになります。何も指定を行わなかった場合はVIRTUALがデフォルトで適用されます。VIRTUALは計算結果を保存せずにSELECTするたびに再計算される仕組みで、文字通り仮想的なカラムとして操作することができます。

対してSTOREDは、挿入と更新を行った際に事前に計算した結果をカラムに保存する仕組みになります。こちらは物理的に結果が保存されているので、通常のカラムと同様に扱うことができます。

使い分けとしては、基本的にはVIRTUALで問題になることはあまりないと思います。VIRTUALで宣言された場合でも、5.7.8からセカンダリインデックスが張れるようになりました。この際にはカラムには計算結果は保存されませんが、当然ですがインデックスには計算結果が保存されています。

現状では、VIRTUALを指定するとセカンダリインデックス以外のインデックスで使用することができないため、主キーや全文検索インデックス等のインデックスにしたい場合にはSTOREDにする必要があります。

また、カラムの特性上結果が一定にならない、NOW()等の結果が時刻で変わってしまう関数や、ストアドファンクション、ストアド・プロシージャ、サブクエリなども使用できません。このあたりの制約を確認したい場合は、MySQL公式のドキュメントを確認して試してみましょう。

宣言方法は、ALTER TABLEでADD COLUMNする方法と、CREATE TABLEをする際に指定する方法の2種類があります。具体的な宣言方法は以下のようになります。

CREATE TABLE テーブル名 (カラム名 型 [GENERATED ALWAYS] AS 式 [ VIRTUAL | STORED ]) 
ALTER TABLE テーブル名 ADD COLUMN カラム名 型 [GENERATED ALWAYS] AS 式 [ VIRTUAL | STORED ]

GENERATED ALWAYSは無くても良いですが、わかりやすくするために付けておいたほうが良いでしょう。VIRTUALとSTOREDは、指定を行わないとデフォルトではVIRTUALが選択されます。

Generated Columnを使用してみる

今回は、第2回　MySQLにはじめてのデータを入れてみるで紹介されているzipcodeテーブルに、regionカラム(地方カラム)を用意して、県ごとに地方を入れてみることにしましょう。

今回のregionカラムで定義する地方は、以下のようになります。

地方名	県名
北海道地方	北海道
東北地方	青森、秋田、岩手、山形、宮城、福島
関東地方	茨城、栃木、群馬、埼玉、東京、千葉、神奈川
中部地方	山梨、新潟、長野、静岡、岐阜、愛知、富山、石川
近畿地方	福井、滋賀、京都、三重、奈良、大阪、和歌山、兵庫
中国地方	鳥取、岡山、島根、広島、山口
四国地方	香川、徳島、愛媛、高知
九州地方	大分、宮崎、福岡、鹿児島、熊本、佐賀、長崎、沖縄

これをすでにあるzipcodeテーブルのprefectureカラムの情報から自動で生成して、regionカラムとして扱えるようにします。

Generated Columnを定義する

Generated Columnはテーブル定義として定義をします。そのため、CREATE TABLEで記述をするかALTER TABLEで記載する必要があります。まずはALTER TABLEのADD COLUMNを利用して、zipcodeテーブルに追加してみましょう。

mysql> ALTER TABLE zipcode ADD COLUMN region VARCHAR(255) GENERATED ALWAYS AS (CASE 
 WHEN prefecture = '北海道' THEN '北海道地方'
 WHEN prefecture IN ('青森県', '秋田県', '岩手県', '山形県', '宮城県', '福島県') THEN '東北地方'
 WHEN prefecture IN ('茨城県', '栃木県', '群馬県', '埼玉県', '東京都', '千葉県', '神奈川県') THEN '関東地方'
 WHEN prefecture IN ('山梨県', '新潟県', '長野県', '静岡県', '岐阜県', '愛知県', '富山県', '石川県') THEN '中部地方'
 WHEN prefecture IN ('福井県','滋賀県', '京都府', '三重県', '奈良県', '大阪府', '和歌山県', '兵庫県') THEN '近畿地方'
 WHEN prefecture IN ('鳥取県', '岡山県', '島根県', '広島県', '山口県') THEN '中国地方'
 WHEN prefecture IN ('香川県', '徳島県', '愛媛県', '高知県') THEN '四国地方'
 WHEN prefecture IN ('大分県', '宮崎県', '福岡県', '鹿児島県', '熊本県', '佐賀県', '長崎県', '沖縄県') THEN '九州地方'
 END) VIRTUAL;
 
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

ちょっと大きくて戸惑うかもしれませんが、よく見るとCASE文で分岐して、先の表に従って定義を行っているのがわかります。

では、作成したカラムを利用して検索をしてみましょう。今回はシンプルにgroup byを利用して、地方と県で正しく定義が行われているかを確認したいと思います。

mysql> select region, prefecture from zipcode group by prefecture, region;
+-----------------+--------------+
| region          | prefecture   |
+-----------------+--------------+
| 北海道地方      | 北海道       |
| 東北地方        | 青森県       |
| 東北地方        | 岩手県       |
| 東北地方        | 宮城県       |
| 東北地方        | 秋田県       |
| 東北地方        | 山形県       |
   〈省略〉 
| 九州地方        | 長崎県       |
| 九州地方        | 熊本県       |
| 九州地方        | 大分県       |
| 九州地方        | 宮崎県       |
| 九州地方        | 鹿児島県     |
| 九州地方        | 沖縄県       |
+-----------------+--------------+
47 rows in set (0.21 sec)

このように、Generated Columnを利用してregionカラムを増やすことができました。今回追加したカラムに、セカンダリインデックスを定義してみましょう。

mysql> ALTER TABLE zipcode ADD INDEX region(region);
Query OK, 0 rows affected (1.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

特にエラーになることもなく、インデックスを張ることができました。では続いてregionカラムを利用するクエリでexplainしてみて、本当に張ったインデックスが使用されているか確認してみましょう。

mysql> EXPLAIN SELECT region FROM zipcode WHERE region = '北海道地方';
+----+-------------+---------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key    | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | zipcode | NULL       | ref  | region        | region | 1023    | const | 15110 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

上記の結果から、keyでregionインデックスが使用されていることがわかりました。

また、今回定義したregionカラムはVIRTUALを指定しているため、regionカラムを利用して主キー等のセカンダリインデックス以外のインデックスを張ることはできません。

mysql> ALTER TABLE zipcode ADD PRIMARY KEY(region);
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.

上記のようにエラーになってしまいました。このような使い方をしたい場合には、STOREDとして宣言を行いましょう。

まとめ

今回は、MySQLの5.7.6から使用できるようになったGenerated Columnについて紹介させていただきました。この機能を利用すると、定義した式から自動で値を生成してくれるので、非常に便利に扱うことができます。

アプリケーション側で生成するのとの違いとしては、VIRTUAL宣言してセカンダリインデックスを使用していない場合は取得時に生成され、STOREDで宣言した場合やVIRTUALで宣言した場合でもセカンダリインデックスに関しては、テーブルに行が挿入、更新されたタイミングで生成されるので、運用時の挿入、更新を行う際のオペレーションミスによる生成漏れなどを防ぐことができます。みなさんもぜひ一度試してみてはいかがでしょうか。