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