Go to Qiita Advent Calendar 2024 Top
LoginSignup
42
34

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@hmatsu47

MySQL 5.7で生成カラムを使って関数INDEXを作成する

Last updated at Posted at 2016-11-08

MySQL 5.7では**「生成カラム」という機能が追加されました。
Oracleでいうところの「仮想列」**に相当する機能です。
JSONデータ型の処理に関連して言及されることが多い機能ですが、もちろん、JSONデータ型以外で汎用的に使うこともできます。
ここでは、生成カラムを使って、関数インデックスを作ってみます。

生成カラム（generated column）とは

あるカラムの値を元に、関数・式などで自動的に計算した結果を取り出すことができるカラムのことです。
生成カラムには、**実データを保持しないもの（VIRTUAL）と保持するもの（STORED）**の2種類がありますが、ここでは前者を扱います。

一種の導出属性にあたるため、正規化理論上は問題がありそうな感じがしますが、RDBMS自身が値を計算してくれるため、少なくとも更新時異状を心配する必要はありません。

[参考情報]

生成カラムの例

以下の例では、

  • 都道府県（pref）カラムから
  • 地方／地域（area）カラムを
  • 制御フロー関数であるCASE式を使って

生成しています。

テーブル定義
mysql> CREATE TABLE `virt_test`.`yuruchara` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(100) NOT NULL,
    ->   `pref` varchar(3) NOT NULL,
    ->   `area` varchar(3) AS ((case when (`pref` = '北海道') then '北海道'
    ->                               when (`pref` in ('青森','岩手','秋田','宮城','山形','福島')) then '東北'
    ->                               when (`pref` in ('茨城','栃木','群馬','埼玉','千葉','東京','神奈川')) then '関東'
    ->                               when (`pref` in ('山梨','長野','新潟','富山','石川','福井','静岡','愛知','岐阜')) then '中部'
    ->                               when (`pref` in ('三重','滋賀','京都','大阪','兵庫','奈良','和歌 山')) then '近畿'
    ->                               when (`pref` in ('鳥取','島根','岡山','広島','山口')) then '中国'
    ->                               when (`pref` in ('香川','愛媛','徳島','高知')) then '四国'
    ->                               when (`pref` in ('福岡','佐賀','長崎','熊本','大分','宮崎','鹿児島','沖縄')) then '九州' else 'その他' end)) NOT NULL,
    ->   `point` int(10) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_pref` (`pref`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

データ型の後ろに**「AS」（「GENERATED ALWAYS AS」）およびカッコで括って式を書くことにより、生成カラムを定義することができます。
式の後ろに「STORED」を記述すると実データを持つ生成カラム、「VIRTUAL」**を記述するか省略すると実データを持たない生成カラムになります。

ここに、以下のINSERT文を発行し、データを挿入します。
…はい、某グランプリの結果、です。

挿入データ
INSERT INTO virt_test.yuruchara SET name = 'しんじょう君', pref = '高知', point = 4345960;
INSERT INTO virt_test.yuruchara SET name = 'はにぽん', pref = '埼玉', point = 4341662;
INSERT INTO virt_test.yuruchara SET name = 'チュッピー', pref = '岡山', point = 4236540;
INSERT INTO virt_test.yuruchara SET name = 'とち介', pref = '栃木', point = 1885649;
INSERT INTO virt_test.yuruchara SET name = '滝ノ道ゆずる', pref = '大阪', point = 1115165;
INSERT INTO virt_test.yuruchara SET name = 'ぽすくま', pref = '東京', point = 1080212;
INSERT INTO virt_test.yuruchara SET name = 'こんのすけ', pref = '東京', point = 1066947;
INSERT INTO virt_test.yuruchara SET name = 'ミムリン', pref = '埼玉', point = 1058618;
INSERT INTO virt_test.yuruchara SET name = 'りそにゃ', pref = '東京', point = 1050213;
INSERT INTO virt_test.yuruchara SET name = '島根県観光キャラクター「しまねっこ」', pref = '島根', point = 819750;
INSERT INTO virt_test.yuruchara SET name = 'ゆずがっぱ', pref = '徳島', point = 752294;
INSERT INTO virt_test.yuruchara SET name = 'はにたん', pref = '大阪', point = 492428;
INSERT INTO virt_test.yuruchara SET name = 'つげさん', pref = '大阪', point = 472830;
INSERT INTO virt_test.yuruchara SET name = '菊川市マスコットキャラクター「きくのん」', pref = '静岡', point = 429521;
INSERT INTO virt_test.yuruchara SET name = 'ニーラ', pref = '山梨', point = 413125;
INSERT INTO virt_test.yuruchara SET name = 'トマックン', pref = '神奈川', point = 336990;
INSERT INTO virt_test.yuruchara SET name = 'こにゅうどうくん', pref = '三重', point = 327799;
INSERT INTO virt_test.yuruchara SET name = 'はっぴーカバー君', pref = '愛媛', point = 310620;
INSERT INTO virt_test.yuruchara SET name = 'Ｐマン＆ぴーにゃっつ', pref = '千葉', point = 301040;
INSERT INTO virt_test.yuruchara SET name = 'でんちゅうくん', pref = '岡山', point = 286137;
INSERT INTO virt_test.yuruchara SET name = 'ハッぴぃ ショッぴぃ フジッぴぃ', pref = '愛媛', point = 282707;
INSERT INTO virt_test.yuruchara SET name = 'うなりくん', pref = '千葉', point = 269780;
INSERT INTO virt_test.yuruchara SET name = 'どりんぴん', pref = '東京', point = 268441;
INSERT INTO virt_test.yuruchara SET name = 'きゅぽらん', pref = '埼玉', point = 257026;
INSERT INTO virt_test.yuruchara SET name = 'やいちゃん', pref = '静岡', point = 240169;
INSERT INTO virt_test.yuruchara SET name = 'こぶしまる', pref = '東京', point = 237641;
INSERT INTO virt_test.yuruchara SET name = 'いまぞう君', pref = '愛媛', point = 234685;
INSERT INTO virt_test.yuruchara SET name = 'あわブタ', pref = '埼玉', point = 227502;
INSERT INTO virt_test.yuruchara SET name = 'はぴ太ファミリー', pref = '大阪', point = 224300;
INSERT INTO virt_test.yuruchara SET name = 'ユキマサくん', pref = '東京', point = 211194;
INSERT INTO virt_test.yuruchara SET name = 'いのとん', pref = '愛媛', point = 197042;
INSERT INTO virt_test.yuruchara SET name = 'ちりゅっぴ', pref = '愛知', point = 194182;
INSERT INTO virt_test.yuruchara SET name = 'キューモット', pref = '東京', point = 192806;
INSERT INTO virt_test.yuruchara SET name = 'はすぴぃ', pref = '埼玉', point = 189860;
INSERT INTO virt_test.yuruchara SET name = 'しまばらん', pref = '長崎', point = 180617;
INSERT INTO virt_test.yuruchara SET name = 'なーしくん', pref = '愛媛', point = 180350;
INSERT INTO virt_test.yuruchara SET name = 'メル助', pref = '愛知', point = 164132;
INSERT INTO virt_test.yuruchara SET name = 'いえらぶ君', pref = '東京', point = 157207;
INSERT INTO virt_test.yuruchara SET name = 'ミクリン', pref = '静岡', point = 155330;
INSERT INTO virt_test.yuruchara SET name = 'すそのん（静岡県裾野市マスコットキャラクター）', pref = '静岡', point = 153935;
INSERT INTO virt_test.yuruchara SET name = 'ゆめずきんちゃん', pref = '長崎', point = 151999;
INSERT INTO virt_test.yuruchara SET name = 'パイプル君', pref = '埼玉', point = 141732;
INSERT INTO virt_test.yuruchara SET name = 'ゾウキリン', pref = '埼玉', point = 137998;
INSERT INTO virt_test.yuruchara SET name = 'ぱんちゃん', pref = '大阪', point = 129088;
INSERT INTO virt_test.yuruchara SET name = 'がんばくん　らんばちゃん', pref = ' 長崎', point = 125169;
INSERT INTO virt_test.yuruchara SET name = 'はながたベニちゃん', pref = '山形', point = 119937;
INSERT INTO virt_test.yuruchara SET name = 'ココちゃん', pref = '兵庫', point = 118991;
INSERT INTO virt_test.yuruchara SET name = 'しろいしみのりちゃん', pref = '佐賀', point = 116212;
INSERT INTO virt_test.yuruchara SET name = 'カパル', pref = '埼玉', point = 110772;
INSERT INTO virt_test.yuruchara SET name = 'カミスココくん', pref = '茨城', point = 107242;

（50位までで勘弁してください。）

ここで、次のSELECT文を発行します。
areaカラムの値が自動的に生成されていることが分かります。

SELECT文
mysql> SELECT * FROM virt_test.yuruchara LIMIT 3;
+----+--------------------+--------+--------+---------+
| id | name               | pref   | area   | point   |
+----+--------------------+--------+--------+---------+
|  1 | しんじょう君       | 高知   | 四国   | 4345960 |
|  2 | はにぽん           | 埼玉   | 関東   | 4341662 |
|  3 | チュッピー         | 岡山   | 中国   | 4236540 |
+----+--------------------+--------+--------+---------+
3 rows in set (0.00 sec)

関数インデックス／式インデックスとは

カラム値を関数や式で「加工」した結果を、インデックスとして使用できるようにしたものです。

例えば、先のテーブルについて、都道府県別ではなく、地方／地域別にレコードを抽出したい場合を考えます。
50件そこそこならテーブルをフルスキャンしても大したことにはなりませんが、レコード数が多くなると、ちょっと大変なことになってきます。

MySQL 5.7では、生成カラムについて、実データを持つ／持たないに関わらず、セカンダリINDEXを定義することができます。

※但し、実データを持たない生成カラムはPRIMARY KEYに含められない・外部キー制約には使えない・全文検索INDEXを作成できない、NOW()などの非決定的な関数を含む生成カラムに対してはINDEXを作成できない、サブクエリやストアドファンクションなどが使えない、などの制限はあります。

[参考情報]

生成カラムにINDEXを追加したテーブル定義
mysql> CREATE TABLE `virt_test`.`yuruchara2` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(100) NOT NULL,
    ->   `pref` varchar(3) NOT NULL,
    ->   `area` varchar(3) AS ((case when (`pref` = '北海道') then '北海道'
    ->                               when (`pref` in ('青森','岩手','秋田','宮城','山形','福島')) then '東北'
    ->                               when (`pref` in ('茨城','栃木','群馬','埼玉','千葉','東京','神奈川')) then '関東'
    ->                               when (`pref` in ('山梨','長野','新潟','富山','石川','福井','静岡','愛知','岐阜')) then '中部'
    ->                               when (`pref` in ('三重','滋賀','京都','大阪','兵庫','奈良','和歌 山')) then '近畿'
    ->                               when (`pref` in ('鳥取','島根','岡山','広島','山口')) then '中国'
    ->                               when (`pref` in ('香川','愛媛','徳島','高知')) then '四国'
    ->                               when (`pref` in ('福岡','佐賀','長崎','熊本','大分','宮崎','鹿児島','沖縄')) then '九州' else 'その他' end)) NOT NULL,
    ->   `point` int(10) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_pref` (`pref`),
    ->   KEY `idx_area` (`area`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

「idx_area」が**生成カラムのINDEX（＝関数INDEX相当）**です。

なお、ここで、以下のように、先ほどのテーブルからデータをコピーしようとすると怒られます。VIRTUALな生成カラムに（DEFAULT以外の）値を代入してINSERT/UPDATEすることはできないからです。

INSERTに失敗する例
mysql> INSERT INTO virt_test.yuruchara2 SELECT * FROM virt_test.yuruchara;
ERROR 3105 (HY000): The value specified for generated column 'area' in table 'yuruchara2' is not allowed.

あらためて、先ほどと同じデータをyuruchara2テーブルにもINSERTしたら、areaカラムの値で絞ってSELECT文を実行してみます。

関数INDEXを使うSELECT文
mysql> SELECT * FROM virt_test.yuruchara2 WHERE area = '中部';
+----+-----------------------------------------------------------------------+--------+--------+--------+
| id | name                                                                  | pref   | area   | point  |
+----+-----------------------------------------------------------------------+--------+--------+--------+
| 14 | 菊川市マスコットキャラクター「きくのん」                              | 静岡   | 中部   | 429521 |
| 15 | ニーラ                                                                | 山梨   | 中部   | 413125 |
| 25 | やいちゃん                                                            | 静岡   | 中部   | 240169 |
| 32 | ちりゅっぴ                                                            | 愛知   | 中部   | 194182 |
| 37 | メル助                                                                | 愛知   | 中部   | 164132 |
| 39 | ミクリン                                                              | 静岡   | 中部   | 155330 |
| 40 | すそのん（静岡県裾野市マスコットキャラクター）                        | 静岡   | 中部   | 153935 |
+----+-----------------------------------------------------------------------+--------+--------+--------+
7 rows in set (0.00 sec)

EXPLAINの結果を見ると、INDEXが効いていることがわかります。

EXPLAINの結果
mysql> EXPLAIN SELECT * FROM virt_test.yuruchara2 WHERE area = '中部';
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | yuruchara2 | NULL       | ref  | idx_area      | idx_area | 11      | const |    7 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MySQL 5.7で生成カラムを使ってCHECK制約もどきを実装する に続きます。

【おまけ】
MySQL 5.7関連投稿記事へのリンクを集めました。

42
34
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
42
34

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?