MySQLのGenerated Columnsまとめ with 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