nazoです。
今回はMySQL 5.7の新機能であるGenerated Columnsについて紹介したいと思います。
Generated Columnsとは？
簡単に言うと、トリガーで特定のカラムにデータを入れるのを簡単に定義する方法みたいなもので、カラム定義時にAS 計算式と書くことで、そのカラムの値が該当の計算結果になります。
この機能はMySQL 5.7.6から追加されました。日本語では「生成列」と呼ぶこともあるようです。
基本的な使い方
リファレンスに書いてあるクエリを実行してみましょう。
mysql> CREATE TABLE triangle ( -> sidea DOUBLE, -> sideb DOUBLE, -> sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) -> ); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
普通に追加されました。ここでsidecがGenerated Columnsに該当します。INSERT文ではsideaとsidebにしかデータを入れていません。
ではsidecの中身はどうなっているでしょうか。
mysql> select * from triangle; +-------+-------+--------------------+ | sidea | sideb | sidec | +-------+-------+--------------------+ | 1 | 1 | 1.4142135623730951 | | 3 | 4 | 5 | | 6 | 8 | 10 | +-------+-------+--------------------+ 3 rows in set (0.00 sec)
このように、(SQRT(sidea * sidea + sideb * sideb))の計算結果がsidecカラムに自動的に入っているのがわかります。
書式とオプション
Generated Columnsは以下の書式で指定ができます。
col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [[NOT] NULL] [[PRIMARY] KEY]
GENERATED ALWAYSオプションは、そのカラムがGenerated Columnsであることを明示するために追加します。少なくともMySQL 5.7.15の時点ではオプションの有無による挙動の変化はないようです。DB2などでは他のオプションで「任意の値も入れることができるGenerated Columns」を指定できることもあるようなので、今後MySQLに同様の機能が入るのかもしれません。
Generated Columnsなカラムに対して更新をかけようとするとエラーになります。
mysql> update triangle set sidec = 1; ERROR 3105 (HY000): The value specified for generated column 'sidec' in table 'triangle' is not allowed.
VIRTUAL | STORED指定は、生成した値をストレージに格納するかどうかを決めます。デフォルトはVIRTUALで、ストレージに格納せず、データ読み取りに評価されます。STOREDの場合はINSERTやUPDATE時にデータがストレージに格納されます。STOREDの場合はストレージの容量が増えますが、恐らくSTOREDのほうが読み取り時のパフォーマンスは高いのではないかと思います。特にVIRTUAL指定したGenerated Columnsのことを「仮想列」と訳している例もあります。
MySQL 5.7.8から、VIRTUALな計算後のデータに対してインデックスが貼れるようになっています。
mysql> INSERT INTO triangle (sidea, sideb) SELECT t1.sidea, t1.sideb from triangle t1, triangle t2, triangle t3, triangle t4, triangle t5, triangle t6, triangle t7, triangle t8; Query OK, 6561 rows affected (0.16 sec) Records: 6561 Duplicates: 0 Warnings: 0
わかりやすいようにデータを増殖させてみました。この状態で普通にEXPLAINしてみます。
mysql> explain select * from triangle where sidec = 5; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | triangle | NULL | ALL | NULL | NULL | NULL | NULL | 6564 | 10.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
ALLで検索されていることがわかりました。
インデックスを張ってみます。
mysql> alter table triangle add index (sidec); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from triangle where sidec = 5; +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | 1 | SIMPLE | triangle | NULL | ref | sidec | sidec | 9 | const | 2188 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
インデックスが効いているのがわかりました。
制限
Generated Columnsは残念ながらサブクエリには対応していないようです。また当然ですが、RANDOM()やNOW()のような実行結果が毎回変わるようなものは指定できません。
mysql> ALTER TABLE triangle ADD t DATETIME AS (NOW()); ERROR 3102 (HY000): Expression of generated column 't' contains a disallowed function.
基本的な考え方としては、「同一カラム内のデータの計算結果を別カラムに格納できる」とするのが良いでしょう。一応例外として、毎回結果が変わらない単純なスカラー値（1とか"a"とか）も入れることができますが、その用途で使うことはないと思います。
JSON型で使う
今までの例だとあまりメリットを感じないように思えますが、例えばこれが、同時期に入ったJSON型と組み合わせると大きく威力を発揮します。
mysql> CREATE TABLE jsontable ( -> id INTEGER, -> json_data JSON, -> user_id INTEGER GENERATED ALWAYS AS (json_unquote(json_extract(`json_data`,'$.user_id'))) VIRTUAL, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO jsontable(id, json_data) VALUES(1, '{"name": "hoge", "user_id": 10}'); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO jsontable(id, json_data) VALUES(2, '{"name": "fuga", "user_id": 20}'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO jsontable(id, json_data) VALUES(3, '{"name": "piyo", "user_id": 30}'); Query OK, 1 row affected (0.00 sec) mysql> ALTER TABLE jsontable ADD INDEX (user_id); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
この時点で大体想像できると思いますが、具体的な挙動は以下になります。
mysql> SELECT * FROM jsontable;
+----+---------------------------------+---------+
| id | json_data | user_id |
+----+---------------------------------+---------+
| 1 | {"name": "hoge", "user_id": 10} | 10 |
| 2 | {"name": "fuga", "user_id": 20} | 20 |
| 3 | {"name": "piyo", "user_id": 30} | 30 |
+----+---------------------------------+---------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM jsontable WHERE user_id = 10;
+----+---------------------------------+---------+
| id | json_data | user_id |
+----+---------------------------------+---------+
| 1 | {"name": "hoge", "user_id": 10} | 10 |
+----+---------------------------------+---------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM jsontable WHERE user_id = 10;
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | jsontable | NULL | ref | user_id | user_id | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
このように、JSON型の特定の項目に対してインデックスを貼れるような感覚（というか実際に貼れる）になります。JSON型の場合は通常は構造が可変になることが多いと思いますし、JSONの値がある場合のみデータを足すというようなことは何かと大変なので、便利かと思います。また、当然ながらJSON以外にもXMLとかでも同様のことを行うことができます。
特定の順序の検索キーとして使う
ちょっと発想を変えると、検索条件を事前に用意することもできます。例えば、文字の長さでソートしたい場合とかを考えます。
CREATE TABLE lengthtable ( id INTEGER, text VARCHAR(255), textlength INTEGER AS (CHAR_LENGTH(text)), PRIMARY KEY(id) ); INSERT INTO lengthtable(id, text) VALUES(1, "tanaka"); INSERT INTO lengthtable(id, text) VALUES(2, "asada"); INSERT INTO lengthtable(id, text) VALUES(3, "mori"); ALTER TABLE lengthtable ADD INDEX(textlength);
文字数で並び替えをしてみます。（データ量が少なすぎてEXPLAINした時にALLになるのでFORCE INDEXを入れています）
mysql> select * from lengthtable force index (textlength) order by textlength; +----+--------+------------+ | id | text | textlength | +----+--------+------------+ | 3 | mori | 4 | | 2 | asada | 5 | | 1 | tanaka | 6 | +----+--------+------------+ 3 rows in set (0.01 sec) mysql> explain select * from lengthtable force index (textlength) order by textlength; +----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------+ | 1 | SIMPLE | lengthtable | NULL | index | NULL | textlength | 5 | NULL | 3 | 100.00 | NULL | +----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
このように、並び替えのためのキーにすることもできそうです。
ユニーク制約
Generated Columnsにもユニーク制約を入れることができ、違反する場合はエラーになります。
mysql> CREATE TABLE lengthtable ( -> id INTEGER, -> text VARCHAR(255), -> textlength INTEGER AS (CHAR_LENGTH(text)) UNIQUE, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO lengthtable(id, text) VALUES(1, "tanaka"); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO lengthtable(id, text) VALUES(2, "asada"); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO lengthtable(id, text) VALUES(3, "morita"); ERROR 1062 (23000): Duplicate entry '6' for key 'textlength'
普段では入れられないような制約を入れることができそうです。
その他
外部キーとして使う場合はSTOREDにして、ON UPADTE〜とかON DELETE〜とかを指定しないようにして設定する必要があるようです。
まとめ
ちょっと特殊なクエリを使わないとデータを取り出せないカラムに対して検索を行いたい場合や、テーブル内のデータだけの計算結果に対して検索を行いたい場合など、トリガーより簡単に指定できるGenerated Columnsの可能性はまだまだありそうです。皆さんも試してみてください。
UUUMでは、YouTube上の大量のデータをMySQLなどで操作したいDBエンジニアも募集しています。詳しくは以下をご覧下さい。
