MySQL5.7から追加されたGenerated Column(生成列)を使って、色々と便利な事が出来るようになりました。
先日、Generated Columnを利用してMySQLの照合順を説明したのでその時に利用した生成列をサンプル兼メモです。

Default: utf8_general_ci or utf8mb4_general_ciの場合
英語の大文字、小文字は同じ文字として扱う。


root@localhost [CONFIRM]> show create table T_Character\G
*************************** 1. row ***************************
       Table: T_Character
Create Table: CREATE TABLE `T_Character` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string1` char(1) DEFAULT NULL,
  `string2` char(1) DEFAULT NULL,
  `string1_w_string` char(4) GENERATED ALWAYS AS (hex(weight_string(`string1`))) VIRTUAL,
  `string2_w_string` char(4) GENERATED ALWAYS AS (hex(weight_string(`string2`))) VIRTUAL,
  `compare` char(1) GENERATED ALWAYS AS ((`string1` = `string2`)) VIRTUAL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

root@localhost [CONFIRM]> select * from T_Character;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
|   2 | あ      | ぁ      | 3042             | 3041             | 0       |
|   3 | か      | が      | 304B             | 304C             | 0       |
|   4 | あ      | ア      | 3042             | 30A2             | 0       |
|   5 | ア      | ア       | 30A2             | FF71             | 0       |
|   6 | は      | ぱ      | 306F             | 3071             | 0       |
|   7 | ハ      | パ      | 30CF             | 30D1             | 0       |
+-----+---------+---------+------------------+------------------+---------+
7 rows in set (0.00 sec)

root@localhost [CONFIRM]> 

Default: utf8_bin or utf8mb4_binの場合
英語の大文字、小文字は異なる文字として扱う。


root@localhost [CONFIRM]> show create table T_Character_COLLATE_utf8mb4_bin\G
*************************** 1. row ***************************
       Table: T_Character_COLLATE_utf8mb4_bin
Create Table: CREATE TABLE `T_Character_COLLATE_utf8mb4_bin` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string1` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `string2` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `string1_w_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`string1`))) VIRTUAL,
  `string2_w_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`string2`))) VIRTUAL,
  `compare` char(1) GENERATED ALWAYS AS ((`string1` = `string2`)) VIRTUAL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [CONFIRM]> select * from T_Character_COLLATE_utf8mb4_bin;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 000041           | 000061           | 0       |
|   2 | あ      | ぁ      | 003042           | 003041           | 0       |
|   3 | か      | が      | 00304B           | 00304C           | 0       |
|   4 | あ      | ア      | 003042           | 0030A2           | 0       |
|   5 | ア      | ア       | 0030A2           | 00FF71           | 0       |
|   6 | は      | ぱ      | 00306F           | 003071           | 0       |
|   7 | ハ      | パ      | 0030CF           | 0030D1           | 0       |
+-----+---------+---------+------------------+------------------+---------+
7 rows in set (0.00 sec)

root@localhost [CONFIRM]> 

BIT型やINT型の変換等の確認にも利用可能


root@localhost [CONFIRM]> show create table T_BIT\G
*************************** 1. row ***************************
       Table: T_BIT
Create Table: CREATE TABLE `T_BIT` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string1` bit(8) DEFAULT NULL,
  `string2` char(8) GENERATED ALWAYS AS ((`string1` + 0)) VIRTUAL,
  `string3_BIT` char(8) GENERATED ALWAYS AS (conv((`string1` + 0),10,2)) VIRTUAL,
  `string4_OCT` char(8) GENERATED ALWAYS AS (conv((`string1` + 0),10,8)) VIRTUAL,
  `string5_HEX` char(8) GENERATED ALWAYS AS (hex((`string1` + 0))) VIRTUAL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [CONFIRM]> select * from T_BIT;
+-----+---------+---------+-------------+-------------+-------------+
| pid | string1 | string2 | string3_BIT | string4_OCT | string5_HEX |
+-----+---------+---------+-------------+-------------+-------------+
|   1 |        | 1       | 1           | 1           | 1           |
|   2 |        | 2       | 10          | 2           | 2           |
|   3 |        | 3       | 11          | 3           | 3           |
|   4 |        | 4       | 100         | 4           | 4           |
|   5 |        | 5       | 101         | 5           | 5           |
+-----+---------+---------+-------------+-------------+-------------+
5 rows in set (0.00 sec)

root@localhost [CONFIRM]> show create table T_BIGINT\G
*************************** 1. row ***************************
       Table: T_BIGINT
Create Table: CREATE TABLE `T_BIGINT` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string1` bigint(64) DEFAULT NULL,
  `string2_2` char(8) GENERATED ALWAYS AS (conv(`string1`,10,2)) VIRTUAL,
  `string3_8` char(8) GENERATED ALWAYS AS (conv(`string1`,10,8)) VIRTUAL,
  `string2_16` char(8) GENERATED ALWAYS AS (conv(`string1`,10,16)) VIRTUAL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [CONFIRM]> select * from T_BIGINT;
+-----+---------+-----------+-----------+------------+
| pid | string1 | string2_2 | string3_8 | string2_16 |
+-----+---------+-----------+-----------+------------+
|   1 |       1 | 1         | 1         | 1          |
|   2 |       2 | 10        | 2         | 2          |
|   3 |       3 | 11        | 3         | 3          |
|   4 |       4 | 100       | 4         | 4          |
|   5 |       5 | 101       | 5         | 5          |
|   6 |     255 | 11111111  | 377       | FF         |
|   7 |     100 | 1100100   | 144       | 64         |
+-----+---------+-----------+-----------+------------+
7 rows in set (0.00 sec)

root@localhost [CONFIRM]> 

色々と簡素化出来る事が出来るので、良く使う集計などは上記の様に、
GENERATED COLUMN(生成列)でまとめておくと楽かもしれません。

その他
生成列はJSONデータ型との相性が良いので、是非JSONデータ型と合わせて利用してみて下さい。
MySQLによるJSONデータ型処理
JSONデータとGenerated_Columnを使う場合の考慮事項


MySQL5.7において、JSON(JavaScript Object Notation)がサポートされていて、
様々な言語やアプリ間で、より簡単にデータの受け渡しが行えるようになりました。
ドキュメントをJSONフォーマットでデータベースに保存して、
様々なアプリケーションから呼び出して利用出来るなど、
汎用性と運用面での活用が出来るようになっています。

1) ネイティブのJSONデータ型
効率的なデータ処理と保管にネイティブ内部バイナリ形式をサポート。
2) 組み込みJSONファンクション
効率よくドキュメントを保存,検索,更新,操作する事を可能にします。
3) JSON コンパレーター
文書データを容易にSQLクエリと統合することが可能
4) Generated Columnsを利用し、ドキュメントにインデックスを利用する事が可能。
新しいアナライザーは,自動的に利用可能で最適な機能インデックスを利用。

ファンクションの詳細は、こちらを参照してください。
12.16 JSON Functions

検証バージョン


root@localhost [USER01]> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.8-rc-log |
+--------------+
1 row in set (0.00 sec)

検証用テーブル


root@localhost [USER01]> show create table T_JSON\G
*************************** 1. row ***************************
       Table: T_JSON
Create Table: CREATE TABLE `T_JSON` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `body` json DEFAULT NULL,
  `WithTax` decimal(10,2) GENERATED ALWAYS AS (json_extract(body,'$.price')*1.08) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_total_cost_v` (`WithTax`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [USER01]> 

※WithTaxは、MySQL5.7から利用可能なGenerated Columnsで作成して、
 文書内のデータからインデックスを作成出来るようにしています。
 ここでは、文書からpriceを抜き出して、TAXを含むコストをベースにインデックスを作成しています。

テストデータ


root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":1,"name":"自転車","price":10000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.15 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":2,"name":"TV","price":30000,"Conditions":["USED",2013]}');
Query OK, 1 row affected (0.01 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":3,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.00 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":4,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.00 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":5,"name":"自転車","price":25000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.01 sec)

<SNIP>

JSONデータ型で作成した列に、データをINSERTする時にフォーマットが正しいかバリデーションしてくれます。
間違えていると以下のようにエラーになります。


root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":6,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 66 in value (or column) '{"id":6,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]'.
root@localhost [USER01]> 

データの参照


root@localhost [USER01]> select * from T_JSON;
+----+-----------------------------------------------------------------------------+----------+
| id | body                                                                        | WithTax  |
+----+-----------------------------------------------------------------------------+----------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}    | 10800.00 |
|  2 | {"id": 2, "name": "TV", "price": 30000, "Conditions": ["USED", 2013]}       | 32400.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}    | 54000.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}    | 54000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}    | 27000.00 |
+----+-----------------------------------------------------------------------------+----------+
5 rows in set (0.01 sec)

root@localhost [USER01]> SELECT json_extract(body,'$.name') FROM T_JSON;
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "自転車"                    |
| "TV"                        |
| "冷蔵庫"                    |
| "冷蔵庫"                    |
| "自転車"                    |
+-----------------------------+
5 rows in set (0.00 sec)

root@localhost [USER01]> 

select

該当のデータを含むデータの抽出例 (json_search())等。


root@localhost [USER01]> SELECT json_extract(body,'$.name')="自転車" FROM T_JSON;
+-----------------------------------------+
| json_extract(body,'$.name')="自転車"    |
+-----------------------------------------+
|                                       1 |
|                                       0 |
|                                       0 |
|                                       0 |
|                                       1 |
+-----------------------------------------+
5 rows in set (0.00 sec)

root@localhost [USER01]> SELECT id,json_search(body,'one','%自転車%') FROM T_JSON;
+----+---------------------------------------+
| id | json_search(body,'one','%自転車%')    |
+----+---------------------------------------+
|  1 | "$.name"                              |
|  2 | NULL                                  |
|  3 | NULL                                  |
|  4 | NULL                                  |
|  5 | "$.name"                              |
+----+---------------------------------------+
5 rows in set (0.00 sec)

root@localhost [USER01]> SELECT json_extract(body,'$.name') 
    -> FROM T_JSON where json_extract(body,'$.name') = '自転車';
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "自転車"                    |
| "自転車"                    |
+-----------------------------+
2 rows in set (0.00 sec)

root@localhost [USER01]> 

JONS_WHERE

Generated Columnにインデックスを張ってあるので、インデックスを使用したドキュメントの検索の確認。


root@localhost [USER01]> SELECT json_extract(body,'$.name') FROM T_JSON where json_extract(body,'$.price') > 40000;
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "冷蔵庫"                    |
| "冷蔵庫"                    |
+-----------------------------+
2 rows in set (0.00 sec)

root@localhost [USER01]> SELECT json_extract(body,'$.name') FROM T_JSON where WithTax > 40000;
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "冷蔵庫"                    |
| "冷蔵庫"                    |
+-----------------------------+
2 rows in set (0.00 sec)

root@localhost [USER01]> explain SELECT json_extract(body,'$.name') FROM T_JSON where json_extract(body,'$.price') > 40000;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | T_JSON | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [USER01]> explain SELECT json_extract(body,'$.name') FROM T_JSON where WithTax > 40000;
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | T_JSON | NULL       | range | idx_total_cost_v | idx_total_cost_v | 6       | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [USER01]> 

index

【アップデート】

MySQL5.7 JSONデータ型とTEXTデータ型の比較

JSONデータとGenerated_Columnを使う場合の考慮事項

MySQL JSONデータ型とJSON関数の復習

参考:
12.16 JSON Functions
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

JSON Labs Release: Effective Functional Indexes in InnoDB
http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/

MySQL and PostgreSQL JSON functions: do they differ much?
https://blogs.oracle.com/svetasmirnova/entry/mysql_and_postgresql_json_functions

JSON Labs Release: Effective Functional Indexes in InnoDB
http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/


MySQL5.7.7RCでは、列の値を定義によって自動生成可能なGenerated Columunsという機能が追加されています。
MS SQLやDB2でも同じような機能があって、頻度は多く無いですが、以前データベースの運用していた時にTAXの自動計算などで利用していました。
以下のブログには、XMLとの連携を行った例が書かれているので参考にしてみて頂ければと思います。

Generated Columns in MySQL 5.7.5
http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/

例) Generated Columinを利用して合計金額を持つ列を生成


CREATE TABLE `T_Generated_Column` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(1024) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `qty` int(10) DEFAULT NULL,
  `total` decimal(10,2) GENERATED ALWAYS AS (price * qty) STORED,
  PRIMARY KEY (`pid`),
  KEY `IDX_TOTAL` (`total`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

データをINSERTしてINDEX利用の有無の確認

GENERATED COLUMNSの種類
– VIRTUAL(default) : 読み込み時に計算され, データ保存されず,インデックスの作成不可
– STORED: inserted/updated時に計算され,データは保存され,インデックス作成可能

generated

generated_index


root@localhost [USER01]> desc T_Generated_Column;
+-------+------------------+------+-----+---------+------------------+
| Field | Type             | Null | Key | Default | Extra            |
+-------+------------------+------+-----+---------+------------------+
| pid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment   |
| pname | varchar(1024)    | YES  |     | NULL    |                  |
| price | decimal(10,2)    | YES  |     | NULL    |                  |
| qty   | int(10)          | YES  |     | NULL    |                  |
| total | decimal(10,2)    | YES  | MUL | NULL    | STORED GENERATED |
+-------+------------------+------+-----+---------+------------------+
5 rows in set (0.15 sec)


root@localhost [USER01]> select * from T_Generated_Column;
+-----+-----------+----------+------+-----------+
| pid | pname     | price    | qty  | total     |
+-----+-----------+----------+------+-----------+
|   1 | 自転車    | 10000.00 |    3 |  30000.00 |
|   2 | TV        | 30000.00 |    5 | 150000.00 |
|   3 | 冷蔵庫    | 50000.00 |    1 |  50000.00 |
+-----+-----------+----------+------+-----------+
3 rows in set (0.00 sec)

root@localhost [USER01]> insert into T_Generated_Column(pname,price,qty) values('電池',100,5);
Query OK, 1 row affected (0.01 sec)

root@localhost [USER01]> select * from T_Generated_Column;
+-----+-----------+----------+------+-----------+
| pid | pname     | price    | qty  | total     |
+-----+-----------+----------+------+-----------+
|   1 | 自転車    | 10000.00 |    3 |  30000.00 |
|   2 | TV        | 30000.00 |    5 | 150000.00 |
|   3 | 冷蔵庫    | 50000.00 |    1 |  50000.00 |
|   4 | 電池      |   100.00 |    5 |    500.00 |
+-----+-----------+----------+------+-----------+
4 rows in set (0.00 sec)

root@localhost [USER01]> explain select * from T_Generated_Column where total = 500;
+----+-------------+--------------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | T_Generated_Column | NULL       | ref  | IDX_TOTAL     | IDX_TOTAL | 6       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

root@localhost [USER01]> select * from T_Generated_Column where total = 500;
+-----+--------+--------+------+--------+
| pid | pname  | price  | qty  | total  |
+-----+--------+--------+------+--------+
|   4 | 電池   | 100.00 |    5 | 500.00 |
+-----+--------+--------+------+--------+
1 row in set (0.00 sec)

root@localhost [USER01]> 

Index_ok

アプリ側の変更しないでもDB側でまとめて対応出来るという意味で、簡易的にデータの値を変更出来るので、
使い方によっては、データ運用の選択肢として使えるソリューションの一つかと思います。
まだ、RC版ですが検証環境にて試してみて頂ければと思います。

Download
http://dev.mysql.com/downloads/mysql/5.7.html