MySQLのGenerated Columnsまとめ with Rails #Rails

Geneterated Columnとは

実カラムの値を計算した結果を格納する専⽤のカラムを作成できる
関数indexをシミュレートできる(生成列にindexを貼れるし、生成列で分割もできる)
MySQL5.7から使える。
Oracleの仮想列に近い。
CREATE TABLEまたはALTER TABLE文の中で使用できる。
[GENERATED ALWAYS] as (expression) の構文で作成する。

作成

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);

確認

mysql> select * from triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+
3 rows in set (0.01 sec)

何故 Geneterated Column を使うのか

データの保存用途ではなく、仮想生成した列は、
照会を単純化し統一するための方法として使用できる。
複雑な条件は、生成された列として定義することで、
確実に同じ条件を使用するようにできる。
格納された生成列は、その場で計算するのにコストがかかる
複雑な条件のためのキャッシュとして使用できる。
Geneterated Columnは関数インデックスをシミュレートできる。
Geneterated Columnを使用して関数式を定義し、
それにインデックスを付けることもできる。
- 生成された列にNOT NULL制約・UNIQUE制約をかけられる。
- 生成された列で分割(partitioning)ができる。
- 生成された列にインデックスが付けられている場合、クエリがその列を直接名前で参照していなくても、
  オプティマイザは列の定義と一致するクエリ式を認識し、
  クエリ実行中にその列のインデックスを適切に使用する
  https://dev.mysql.com/doc/refman/5.7/en/generated-column-index-optimizations.html

データの持ち方

Geneterated Columnは VIRTUAL または STOREDを指定することができる。
デフォルトは VIRTUALである。
テーブル内に VIRTUAL と STORED は混在できる
どちらのタイプもセカンダリーインデックスを作成できる。
セカンダリーインデックスはどちらのタイプでもDiskに固定できる。

STORED

列の値は、行が挿入または更新されたときに評価されて保管される。
ストレージを必要とする。
indexを貼れる。

VIRTUAL

SELECT時に都度計算される。
ストレージを必要としない。
MySQL 5.7.8から、計算後のデータに対してインデックスが貼れる

注意点

生成された列式は、以下の規則に従う必要があり、
式に許可されていない構成が含まれていると、エラーが発生する。
- ストアドファンクション・ユーザー定義関数は不許可
- ストアドプロシージャと関数パラメータは不許可
- 変数（システム変数、ユーザー定義変数、およびストアドプログラムローカル変数）は不許可
- サブクエリは許可されない
- AUTO_INCREMENT属性は列の定義で使用できない
カラムとしては定義されているので、 SELECT * とかはできるが、
Insert のように直接値を投入することはできない。
式が宣言された列型とは異なるデータ型に評価された場合、
宣言された型への暗黙的な強制は通常のMySQLの型変換規則に従って発生する。
外部キー制約は仮想生成した列を参照できない。

Railsで実装する場合

テーブル作成時に追加する方法

class CreateTriangle < ActiveRecord::Migration[5.1]
  def change
    create_table :triangle do |t|
      t.integer :sidea
      t.integer :sideb
      t.virtual :sidec, type: :float,  as: "SQRT(sidea * sidea + sideb * sideb)"
      t.virtual :sided, type: :float,  as: "SQRT(sidea * sidea + sideb * sideb)", stored: true
    end
  end
end

テーブルの確認

mysql> show create table triangle;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                    |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| triangle | CREATE TABLE `triangle` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sidea` int(11) DEFAULT NULL,
  `sideb` int(11) DEFAULT NULL,
  `sidec` float GENERATED ALWAYS AS (sqrt(((`sidea` * `sidea`) + (`sideb` * `sideb`)))) VIRTUAL,
  `sided` float GENERATED ALWAYS AS (sqrt(((`sidea` * `sidea`) + (`sideb` * `sideb`)))) STORED,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

データの挿入

INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

確認

mysql> select * from triangle;
+----+-------+-------+---------+---------+
| id | sidea | sideb | sidec   | sided   |
+----+-------+-------+---------+---------+
|  1 |     1 |     1 | 1.41421 | 1.41421 |
|  2 |     3 |     4 |       5 |       5 |
|  3 |     6 |     8 |      10 |      10 |
+----+-------+-------+---------+---------+
3 rows in set (0.00 sec)

カラムとして追加

class AddColumnToTriangle < ActiveRecord::Migration[5.1]
  def up
    execute "ALTER TABLE triangle ADD COLUMN sidee float AS (
      CASE WHEN sidea > 2 THEN SQRT(sidea * sidea + sideb * sideb)
      ELSE NULL END
    )"

    add_index :triangle, :sidee, unique: true
  end

  def down
    remove_column :triangle, :sidee
  end
end

テーブルの確認

mysql> show create table triangle;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| triangle | CREATE TABLE `triangle` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sidea` int(11) DEFAULT NULL,
  `sideb` int(11) DEFAULT NULL,
  `sidec` float GENERATED ALWAYS AS (sqrt(((`sidea` * `sidea`) + (`sideb` * `sideb`)))) VIRTUAL,
  `sided` float GENERATED ALWAYS AS (sqrt(((`sidea` * `sidea`) + (`sideb` * `sideb`)))) STORED,
  `sidee` float GENERATED ALWAYS AS ((case when (`sidea` > 2) then sqrt(((`sidea` * `sidea`) + (`sideb` * `sideb`))) else NULL end)) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_triangle_on_sidee` (`sidee`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

確認

mysql> select * from triangle;
+----+-------+-------+---------+---------+-------+
| id | sidea | sideb | sidec   | sided   | sidee |
+----+-------+-------+---------+---------+-------+
|  1 |     1 |     1 | 1.41421 | 1.41421 |  NULL |
|  2 |     3 |     4 |       5 |       5 |     5 |
|  3 |     6 |     8 |      10 |      10 |    10 |
+----+-------+-------+---------+---------+-------+
3 rows in set (0.00 sec)

参考

MySQL 5.7にやられないためにおぼえておいてほしいこと
https://www.slideshare.net/yoku0825/mysql-57-53449734

13.1.18.8 CREATE TABLE and Generated Columns
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html