DX事業部の @yyoshiki41（中川佳希）です。 現在は、LayerX インボイス という経理業務を行う方を対象ユーザーにした SaaS をメインで開発しています。
今回は、MySQL での Generated Column の活用についての紹介です。
Generated Column とは？
カラム定義時にロジックを組んでおくことで、演算や条件分岐ロジックの結果を値として、仮想的に参照可能にするもしくは記憶領域に格納することが出来る機能です。
ドキュメントには以下のような直角三角形の斜辺 sidec を格納するスキーマの例が紹介されています。
CREATE TABLE triangle ( sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) ); INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
AS 以降にカラムの値計算を行う式が定義されています。
mysql> SELECT * FROM triangle; +-------+-------+--------------------+ | sidea | sideb | sidec | +-------+-------+--------------------+ | 1 | 1 | 1.4142135623730951 | | 3 | 4 | 5 | | 6 | 8 | 10 | +-------+-------+--------------------+
ユースケース
よく紹介されている例としては、以下のようなものがあります。
- 複雑な条件結果を先にカラムに定義しておき、クエリ条件を簡略化させる
STOREDを指定して記憶領域に書き込みを行っておき、クエリ参照時には計算コストをかけないJSON型のようなインデックスキーをつけれないカラムに対して、インデックスキー用のカラムを生成する
JSON との組み合わせの例としては、以下のようなものです。
CREATE TABLE `tests` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `json` json DEFAULT NULL, `version` int GENERATED ALWAYS AS (json_extract(`json`,'$.version')) VIRTUAL, PRIMARY KEY (`id`), KEY `version` (`version`) ); INSERT INTO tests(`json`) VALUES ('{"version": "1", "definitions": "foo"}');
json のキーとして version を持つデータに対して、インデックスキーカラムを設定できました。
mysql> SELECT * FROM tests; +----+--------------------------------------+---------+ | id | json | version | +----+--------------------------------------+---------+ | 1 | {"version": 1, "definitions": "foo"} | 1 | +----+--------------------------------------+---------+
アプリケーションでのデータ整合性
アプリケーションが読み書きするデータの整合性は、DB側でも外部キー、ユニークキーやCHECK制約などを用いて担保したいものです。
今回紹介したいのは、ユニークキーとして Generated Columns を活用する例です。
例1. 状態やステータスで有効なユニークキーを表現する
レコードの物理削除を行いたくない場合に、deleted_at カラムを用いることがあるかと思います。
名称 name カラムに対してユニークキーを設定したいとします。
まずは、nameとdeleted_at（nullableなカラム）の複合ユニークキーを設定した良くない例は下記です。
CREATE TABLE `bad_examples` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_1` (`name`,`deleted_at`) );
NULLとなっているカラム値に対しユニークキーは有効でないため、重複名が登録できてしまいます。
mysql> INSERT INTO `bad_examples`(name) VALUES("foo"), ("foo"); mysql> SELECT * FROM `bad_examples`; +----+------+------------+ | id | name | deleted_at | +----+------+------------+ | 1 | foo | NULL | | 2 | foo | NULL | +----+------+------------+ 2 rows in set (0.00 sec)
このようなケースでは、deleted_at が NULL であるかをフラグとして持つ Generated Columns を定義し、ユニークキーとして使うと有効です。
CREATE TABLE `good_examples` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `deleted_at` timestamp NULL DEFAULT NULL, `is_active` tinyint(1) AS ((case when isnull(`deleted_at`) then 1 else NULL end)) VIRTUAL, PRIMARY KEY (`id`), UNIQUE KEY `uq_1` (`name`,`is_active`) );
mysql> INSERT INTO `good_examples`(`name`,`deleted_at`) \ -> VALUES("foo", NULL), ("foo", NOW() - INTERVAL 1 SECOND), ("foo", NOW()); mysql> SELECT * FROM `good_examples`; +----+------+---------------------+-----------+ | id | name | deleted_at | is_active | +----+------+---------------------+-----------+ | 1 | foo | NULL | 1 | | 2 | foo | 2021-06-20 23:49:03 | NULL | | 3 | foo | 2021-06-20 23:49:04 | NULL | +----+------+---------------------+-----------+
例2. ユニークキーの条件がレコードにより異なる
更にこみいったビジネスロジックにおけるデータ整合性を担保する場合を考えてみます。
条件
- 各店舗の商品を持つテーブル
- 全店舗で商品名
nameは必ず持つが、商品コードcodeは店舗により未設定の場合がある - 商品コード
codeをもつ場合、codeがユニークキーとなる - 商品コード
codeをもたない場合、商品名nameがユニークキーとなる
これをスキーマとして持つテーブルは以下のように定義できます。
CREATE TABLE `shop_goods` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `shop_id` int unsigned NOT NULL, `name` varchar(255) NOT NULL, `code` varchar(255) NULL DEFAULT NULL, `is_null_code` tinyint(1) GENERATED ALWAYS AS ((case when isnull(`code`) then 1 else NULL end)) VIRTUAL, PRIMARY KEY (`id`), UNIQUE KEY `uq_1` (`shop_id`, `name`, `is_null_code`), UNIQUE KEY `uq_2` (`shop_id`, `code`) );
商品コードを設定していない店舗１で、重複した商品名は以下のようにユニークキー制約でエラーになります。
mysql> INSERT INTO `shop_goods`(`shop_id`, `name`, `code`) \ -> VALUES(1, "商品A", NULL), (1, "商品A", NULL); ERROR 1062 (23000): Duplicate entry '1-商品A-1' for key 'uq_1'
商品コードを設定している店舗２では、重複した商品名も登録可能になります！
mysql> INSERT INTO `shop_goods`(`shop_id`,`name`, `code`) \ -> VALUES(1, "商品A", "コードA-1"), (1, "商品A", "コードA-2"); mysql> SELECT * from `shop_goods`; +----+---------+---------+--------------+--------------+ | id | shop_id | name | code | is_null_code | +----+---------+---------+--------------+--------------+ | 1 | 1 | 商品A | コードA-1 | NULL | | 2 | 1 | 商品A | コードA-2 | NULL | +----+---------+---------+--------------+--------------+
おわりに
Generated Columns の活用例をいくつか紹介してみました。 INSERT/UPDATE 時にはカラムにセットする値をアプリケーション側から無視する必要があったり（もしくは DEFAULT を使う）、考えることはあります。 しかし、有効なケースも多々あると思いますので参考になりましたら幸いです！
