MySQL5.7で実装されたJSONデータ型とJSON関数のレビューになります。
ご利用になる場合は、参考にして頂ければと思います。

ネイティブJSONデータ型 (バイナリ形式)
Insert時のJSON構文バリデーション機能
組み込みJSON関数 (保存、検索、更新、操作)
ドキュメントにインデックス設定し高速アクセス
SQLとの統合を容易にする、新しいインライン構文
utf8mb4の文字セットとutf8mb4_binの照合 「🐬」
サイズはmax_allowed_packetの値で制限 (Default:4MB)

MySQL5.7からは、リレーショナル、スキーマレスを同じ技術スタックで利用可能になっています。

13.16.1 JSON Function Reference
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

参考: Modifying JSON Values in MySQL 5.7
https://planet.mysql.com/entry/?id=5994648

JSONで表現する全てのデータ型をサポート

数値, 文字列, bool(true,false)
オブジェクト {“キー”: “値”}, 配列 [123456, “String”, …]
null
日付(date), 時刻, 日付(datetime), タイムスタンプ, その他


[CONFIRM]> show create table T_JSON_SUPPORT\G
*************************** 1. row ***************************
       Table: T_JSON_SUPPORT
Create Table: CREATE TABLE `T_JSON_SUPPORT` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`body` json DEFAULT NULL,
`type` varchar(20) GENERATED ALWAYS AS (json_type(`body`)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4


[CONFIRM]> select * from T_JSON_SUPPORT;
+----+---------------------------------------------+----------+
| id | body                                        | type     |
+----+---------------------------------------------+----------+
|  1 | 123456789                                   | INTEGER  |
|  2 | NULL                                        | NULL     |
|  3 | true                                        | BOOLEAN  |
|  4 | "abcde"                                     | STRING   |
|  5 | {"id": 5, "name": "オブジェクト"}            | OBJECT   |
|  6 | [-122.42200352825247, 37.80848009696725, 0] | ARRAY    |
|  7 | "2016-02-29"                                | DATE     |
|  8 | "2016-02-29 00:00:00.000000"                | DATETIME |
+----+---------------------------------------------+----------+

データ型と照合順
4byteなので絵文字を格納可能、またutf8mb4_binなので大文字と小文字を区別します。


 [NEW57]> SET @j = JSON_OBJECT('key', 'value');
1 row in set (0.00 sec)

 [NEW57]> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+
1 row in set (0.00 sec)

 [NEW57]> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+
1 row in set (0.00 sec)

JSONドキュメントと生成列を利用して列を作成し、対象列にINDEXを付けて高速な検索を行う事が可能です
生成列はファンクションインデックスとして利用可能

こちらは、生成列を利用したテーブルの作成例になります。
string1とstring2にデータをINSERTする事で、string1_w_string、string2_w_string、compareのデータは自動生成されます。


[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=1 DEFAULT CHARSET=utf8mb4

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('A','a');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('あ','ぁ');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('A','A');
Query OK, 1 row affected (0.00 sec)

 [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 | A       | A       | 000041           | 000041           | 1       |
+-----+---------+---------+------------------+------------------+---------+

JSONデータと生成列を利用すると、特定のJSON識別子からデータを抜き出して列を作成し、その列にインデックスを付ける事が可能
例えば、JSONデータをINSERTしてJSONドキュメントにUSER_IDやPRODUCT_IDがあれば、その項目だけを抜き出して列にしてINDEXを付与。
検索する場合は、それらの値を利用して検索するとJSONドキュメントも高速検索が可能になります。


[NEW57]> CREATE TABLE `T_JSON` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,‘$.properties.STREET’)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;


[NEW57]> alter table features add index idx_feature_type(`feature_type`);
Query OK, 0 rows affected (6.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

[NEW57]> explain select distinct(feature_type) from features;
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys    | key              | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | features | NULL       | index | idx_feature_type | idx_feature_type | 123     | NULL | 199013 |   100.00 | Using index |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+

【JSON関数の使い方】

(1)JSONドキュメントの情報取得


[NEW57]> SELECT JSON_VALID(body) from T_JSON_DOC where id = 5;
+------------------+
| JSON_VALID(body) |
+------------------+
|                1 |
+------------------+

[NEW57]> SELECT JSON_TYPE(body) from T_JSON_DOC where id = 5;
+-----------------+
| JSON_TYPE(body) |
+-----------------+
| OBJECT          |
+-----------------+

[NEW57]> SELECT JSON_KEYS(body) from T_JSON_DOC where id = 5;
+---------------------------------------+
| JSON_KEYS(body)                       |
+---------------------------------------+
| ["id", "name", "price", "Conditions"] |
+---------------------------------------+


[NEW57]> SELECT JSON_SEARCH(feature,'one','MARKET') AS extract_path FROM features WHERE id = 121254;
+-----------------------+
| extract_path          |
+-----------------------+
| "$.properties.STREET" |
+-----------------------+

(2)通常のリレーショナルテーブルから,JSONドキュメントを作成する
Object{}とArray[]関数を利用


/*** リレーショナルテーブル ***/
[NEW57]> SELECT NAME,CountryCode from world.City where CountryCode ='JPN' limit 1;
+-------+-------------+
| NAME  | CountryCode |
+-------+-------------+
| Tokyo | JPN         |
+-------+-------------+

/*** リレーショナルテーブルからJSONデータを作成 ({})***/
[NEW57]> SELECT JSON_OBJECT('CITY',NAME,'Country',CountryCode) from world.City where CountryCode ='JPN' limit 1;
+------------------------------------------------+
| JSON_OBJECT('CITY',NAME,'Country',CountryCode) |
+------------------------------------------------+
| {"CITY": "Tokyo", "Country": "JPN"}            |
+------------------------------------------------+

/*** VIEWにしておくと呼び出しがより楽になります ***/
[NEW57]> CREATE VIEW v_City_json AS
    -> SELECT JSON_OBJECT('ID', ID, 'name', Name, 'CountryCode', CountryCode, 'District', District,'Population',Population) as doc FROM City where CountryCode = 'JPN';
Query OK, 0 rows affected (0.01 sec)

[NEW57]> select * from v_City_json limit 1\G
*************************** 1. row ***************************
doc: {"ID": 1532, "name": "Tokyo", "District": "Tokyo-to", "Population": 7980230, "CountryCode": "JPN"}
1 row in set (0.01 sec)


/*** リレーショナルテーブル ***/
[NEW57]>SELECT NAME,CountryCode from world.City where CountryCode ='JPN' limit 1;
+-------+-------------+
| NAME  | CountryCode |
+-------+-------------+
| Tokyo | JPN         |
+-------+-------------+


/*** リレーショナルテーブルからJSONデータを作成 ([])***/
[NEW57]> SELECT JSON_ARRAY(NAME,CountryCode)  from world.City where CountryCode ='JPN' limit 1;
+------------------------------+
| JSON_ARRAY(NAME,CountryCode) |
+------------------------------+
| ["Tokyo", "JPN"]             |
+------------------------------+


/*** リレーショナルテーブル***/
[NEW57]> select * from T_JSON_PLACE;
+----+-------------------------------------+----------+-----------+
| id | place                               | latitude | longitude |
+----+-------------------------------------+----------+-----------+
|  1 | 東京都港区北青山2-5-8                | 35.67125 | 139.71864 |
|  2 | 大阪府大阪市北区堂2-4-27            | 34.69584 | 135.49291 |
+----+-------------------------------------+----------+-----------+


/*** リレーショナルテーブルからJSONデータを作成 ({},[])***/
[NEW57]> select JSON_OBJECT('ID',id,'住所',place,'緯度経度', JSON_ARRAY(latitude,longitude)) from T_JSON_PLACE;
+---------------------------------------------------------------------------------------------------+
| JSON_OBJECT('ID',id,'住所',place,'緯度経度', JSON_ARRAY(latitude,longitude))                      |
+---------------------------------------------------------------------------------------------------+
| {"ID": 1, "住所": "東京都港区北青山2-5-8", "緯度経度": [35.67125, 139.71864]}                     |
| {"ID": 2, "住所": "大阪府大阪市北区堂2-4-27", "緯度経度": [34.69584, 135.49291]}                 |
+---------------------------------------------------------------------------------------------------+

メモ: ST_AsGeoJSONやST_GeomFromGeoJSON等のSpatial GeoJSON Functionsで空間を表すJSONも利用する事が可能

JSONドキュメントの更新処理


/**** JSON_REMOVE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+---------------------------------------------------------------------------------------------------+------------+
| id | body                                                                                              | price_only |
+----+---------------------------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}             |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED", 2013, "故障"]}                 |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 17131, "Conditions": ["NEW", 2015]}                          |   17131.00 |
|  4 | {"id": 4, "name": "オートバイ", "price": 500000, "Conditions": ["NEW", 2015]}                     |  500000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Quantity": "[1,10]", "Conditions": ["NEW", 2015]}    |   25000.00 |
+----+---------------------------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_REMOVE(body,"$.Quantity") from T_JSON_DOC where id = 5;
+-----------------------------------------------------------------------------+
| JSON_REMOVE(body,"$.Quantity")                                              |
+-----------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}    |
+-----------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_REMOVE(body,"$.Quantity") where id = 5;



/**** JSON_SET (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> SELECT JSON_SET(body,'$.name',"オートバイ", '$.price',500000) from T_JSON_DOC where id = 4;
+------------------------------------------------------------------------------------+
| JSON_SET(body,'$.name',"オートバイ", '$.price',500000)                             |
+------------------------------------------------------------------------------------+
| {"id": 4, "name": "オートバイ", "price": 500000, "Conditions": ["NEW", 2015]}      |
+------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_SET(body,'$.name',"オートバイ", '$.price',500000) where id = 4;


/**** JSON_INSERT (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_INSERT(body,'$.Quantity','[1,10]') from NEW57.T_JSON_DOC where id = 5;
+---------------------------------------------------------------------------------------------------+
| JSON_INSERT(body,'$.Quantity','[1,10]')                                                           |
+---------------------------------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Quantity": "[1,10]", "Conditions": ["NEW", 2015]}    |
+---------------------------------------------------------------------------------------------------+

[NEW57]> update NEW57.T_JSON_DOC set body = JSON_INSERT(body,'$.Quantity','[1,10]') where id = 5;


/**** JSON_REPLACE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000))) from T_JSON_DOC where id = 3;
+-----------------------------------------------------------------------------+
| JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000)))                 |
+-----------------------------------------------------------------------------+
| {"id": 3, "name": "冷蔵庫", "price": 18359, "Conditions": ["NEW", 2015]}    |
+-----------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000))) where id = 3;


/**** JSON_ARRAY_INSERT (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]>SELECT JSON_ARRAY_INSERT(body,'$.Conditions[2]','January') from T_JSON_DOC where id = 5;
+----------------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT(body,'$.Conditions[2]','January')                                    |
+----------------------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015, "January"]}    |
+----------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_ARRAY_INSERT(body,'$.Conditions[2]','January') where id = 5;



/**** JSON_ARRAY_APPEND (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_ARRAY_APPEND(body,'$.Conditions','故障') from NEW57.T_JSON_DOC where id = 2;
+----------------------------------------------------------------------------------------+
| JSON_ARRAY_APPEND(body,'$.Conditions','故障')                                          |
+----------------------------------------------------------------------------------------+
| {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED", 2013, "故障"]}      |
+----------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_ARRAY_APPEND(body,'$.Conditions','故障') where id = 2;


/**** JSON_MERGE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> SELECT JSON_MERGE(body,'{"Conditions":"Excellent"}') from T_JSON_DOC where id = 1;
+------------------------------------------------------------------------------------------+
| JSON_MERGE(body,'{"Conditions":"Excellent"}')                                            |
+------------------------------------------------------------------------------------------+
| {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}    |
+------------------------------------------------------------------------------------------+


[NEW57]> update T_JSON_DOC set body = JSON_MERGE(body,'{"Conditions":"Excellent"}') where id = 1;

補足情報
json_extractと->は同等

`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL
`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.type'))) VIRTUAL

メモ:
-> /* JSON_EXTRACT() */
->> /* JSON_UNQUOTE(JSON_EXTRACT()) */

必要に応じてjson_unquoteでQuoteを外して列を作成

`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL
`feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL

[NEW57]> select feature_type,feature_street from T_JSON limit 10,5;
+--------------+----------------+
| feature_type | feature_street |
+--------------+----------------+
| Feature      | "JEFFERSON"    |
| Feature      | "TAYLOR"       |
| Feature      | "BEACH"        |
| Feature      | "BEACH"        |
| Feature      | "JEFFERSON"    |
+--------------+----------------+

生成列のVirtualとStoredの違いはありますが、Virtualであれば作成時も参照、更新もオンライン処理が可能。
但し、参照時にCPUを利用するのでStoredを選択するかは状況により使い分け。
Storedは実データを含む為、高速だが追加でディスク容量が必用。また、作成時は参照のみが可能になります。

ALTER TABLE T_ONLINE ALGORITHM=INPLACE, ADD feature_type varchar(30) AS (feature->"$.type") VIRTUAL;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE T_ONLINE ALGORITHM=INPLACE, ADD feature_type varchar(30) AS (feature->"$.type") STORED;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

ALTER TABLE T_ONLINE ADD feature_type varchar(30) AS (feature->"$.type") STORED;
Query OK, 206560 rows affected (6.17 sec)
Records: 206560  Duplicates: 0  Warnings: 0



先日、リリースされたMySQL5.7.12に含まれるmysqlxプラグインと、
同時にリリースされた、MySQL Shellの初期設定と基本動作確認を確認してみました。
基本的にこれらは、MySQL5.7で追加されたJSONデータ型を含むスキーマレスなドキュメントデータを、
mysqlxプラグインを利用する事でより柔軟に処理出来るようにする為に追加され、
MySQLをドキュメントデータベースとしてより利用し易くする為に追加された機能となっています。

MySQL Shellは、MySQLサーバの開発と管理をサポートする、
JavaScript、Python、SQLの対話型のインタフェースをサポートするMySQLの新しいコンポーネント。
データのクエリと更新処理だけでなく、様々な管理操作を実行する為にMySQLのシェルを使用することができます。
※MySQL Shellを利用する為には、事前にMySQL5.7.12以降に含まれているXプラグインをインストールしておく必要があります。

Xプラグイン
Xプロトコルを使用して通信を可能にするMySQLサーバプラグイン。
X DevAPIを実装するクライアントをサポートし、ドキュメントストアとしてMySQLを使用することができます。

Xプロトコル
Xプロトコルは、Xプラグインを実行しているMySQLサーバーと通信します。
Xプロトコルは、SSL経由でCRUDとSQL操作、認証の両方をサポートしていて、
コマンドのストリーミング処理を可能にし、プロトコルとメッセージレイヤ上に拡張可能。

root@localhost [information_schema]> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_DESCRIPTION from plugins
    -> where PLUGIN_NAME = 'mysqlx';
+-------------+----------------+--------------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_DESCRIPTION |
+-------------+----------------+--------------------+
| mysqlx      | 1.0            | X Plugin for MySQL |
+-------------+----------------+--------------------+
1 row in set (0.00 sec)

root@localhost [information_schema]> 

【X Pluginインストール】
参照:http://dev.mysql.com/doc/refman/5.7/en/x-plugin-installation.html

通常通り、INSTALL PLUGINコマンドでインストール

root@localhost [mysql]> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';
Query OK, 0 rows affected (0.25 sec)

root@localhost [mysql]> show plugins;
+----------------------------+----------+--------------------+-------------------+-------------+
| Name                       | Status   | Type               | Library           | License     |
+----------------------------+----------+--------------------+-------------------+-------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL              | PROPRIETARY |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL              | PROPRIETARY |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
<SNIP>
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL              | PROPRIETARY |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| ngram                      | ACTIVE   | FTPARSER           | NULL              | PROPRIETARY |
| mecab                      | ACTIVE   | FTPARSER           | libpluginmecab.so | PROPRIETARY |
| MYSQL_FIREWALL             | ACTIVE   | AUDIT              | firewall.so       | PROPRIETARY |
| MYSQL_FIREWALL_USERS       | ACTIVE   | INFORMATION SCHEMA | firewall.so       | PROPRIETARY |
| MYSQL_FIREWALL_WHITELIST   | ACTIVE   | INFORMATION SCHEMA | firewall.so       | PROPRIETARY |
| mysqlx                     | ACTIVE   | DAEMON             | mysqlx.so         | PROPRIETARY |
+----------------------------+----------+--------------------+-------------------+-------------+
49 rows in set (0.00 sec)

root@localhost [mysql]> 

【X Plugin オプションと変数について】
設定パラメータに関しては、此方を参照下さい。
http://dev.mysql.com/doc/refman/5.7/en/x-plugin-option-variable-reference.html

【接続方法】
既に、mysqlshをインストール済みなので –sqlオプションを仕様してSQLを流し込んでみる。
mysqlxのPort33060(Default:mysqlx_port)から、ローカルのMySQLにアクセスしてSQLを実行しています。

【サンプルデータベース作成】
参照:
http://downloads.mysql.com/docs/world_x-db.zip
http://dev.mysql.com/doc/refman/5.7/en/mysql-shell-tutorial-javascript-download.html

mysqlsh経由でサンプルデータベースの作成して、mysqlクライアントで接続してスキーマとテーブルの確認

[root@misc01 MID2016]# mysqlsh -u admin -p --sql --recreate-schema world_x < world_x.sql
Enter password: 
Recreating schema world_x...

[root@misc01 MID2016]# mysql -u root -p -e "show databases like 'world%'"
Enter password: 
+-------------------+
| Database (world%) |
+-------------------+
| world             |
| world2            |
| world_x           |
+-------------------+
[root@misc01 MID2016]# mysql -u root -p -e "show tables from world_x"
Enter password: 
+-------------------+
| Tables_in_world_x |
+-------------------+
| City              |
| Country           |
| CountryInfo       |
| CountryLanguage   |
+-------------------+

【接続モード】
参考) 接続方法としては、現状では、以下のモードを選択できるようです。

# mysqlsh --help
MySQL Shell 1.0.3 Development Preview

<SNIP>

  --sql                    Start in SQL mode using a node session.
  --sqlc                   Start in SQL mode using a classic session.
  --js                     Start in JavaScript mode.
  --py                     Start in Python mode.

<SNIP>

こちらでは、mysqlsh経由でjava scriptモードの状態でJSONデータを処理してみます。


[root@misc01 MID2016]# mysqlsh -u admin -p 
Creating an X Session to admin@localhost:33060
Enter password: 
No default schema selected.

Welcome to MySQL Shell 1.0.3 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> news_collection = session.getSchema('NEW57').createCollection("X_JSON");
<Collection:X_JSON>
mysql-js> news_collection.add({"id": 1, "name": "Document Data", "price": 60000, "Conditions": ["NEW", 2016]});
Query OK, 1 item affected (0.01 sec)

mysql-js> news_collection.find('name like :n').bind('n','Document%');
[
    {
        "Conditions": [
            "NEW",
            2016
        ],
        "_id": "26c8af795703e611630c0800279cea3c",
        "id": 1,
        "name": "Document Data",
        "price": 60000
    }
]
1 document in set (0.03 sec)

mysql-js>  

MySQLに接続してデータを確認した状態


root@localhost [NEW57]> desc X_JSON;
+-------+-------------+------+-----+---------+------------------+
| Field | Type        | Null | Key | Default | Extra            |
+-------+-------------+------+-----+---------+------------------+
| doc   | json        | YES  |     | NULL    |                  |
| _id   | varchar(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

root@localhost [NEW57]> select * from X_JSON\G
*************************** 1. row ***************************
doc: {"id": 1, "_id": "26c8af795703e611630c0800279cea3c", "name": "Document Data", "price": 60000, "Conditions": ["NEW", 2016]}
_id: 26c8af795703e611630c0800279cea3c
1 row in set (0.00 sec)

root@localhost [NEW57]> 

【Beta Draft】
X DevAPI User Guide / Overview
http://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-introduction.html
その他、X DevAPI経由でコーディング出来そう。但し、現時点ではベータリリースなのでもう少ししたら確認。

【その他:Status変数】
こちらで、mysqlx経由の処理を確認する事が可能です。


[root@misc01 MID2016]# mysql -u root -p -e "show status like 'mysqlx%'"
Enter password: 
+-------------------------------------+--------------------------+
| Variable_name                       | Value                    |
+-------------------------------------+--------------------------+
| Mysqlx_bytes_received               | 389773                   |
| Mysqlx_bytes_sent                   | 3466                     |
| Mysqlx_connection_accept_errors     | 0                        |
| Mysqlx_connection_errors            | 0                        |
| Mysqlx_connections_accepted         | 2                        |
| Mysqlx_connections_closed           | 2                        |
| Mysqlx_connections_rejected         | 0                        |
| Mysqlx_crud_delete                  | 0                        |
| Mysqlx_crud_find                    | 0                        |
| Mysqlx_crud_insert                  | 0                        |
| Mysqlx_crud_update                  | 0                        |
| Mysqlx_errors_sent                  | 1                        |
| Mysqlx_expect_close                 | 0                        |
| Mysqlx_expect_open                  | 0                        |
| Mysqlx_init_error                   | 1                        |
| Mysqlx_notice_other_sent            | 76                       |
| Mysqlx_notice_warning_sent          | 2                        |
| Mysqlx_rows_sent                    | 18                       |
| Mysqlx_sessions                     | 0                        |
| Mysqlx_sessions_accepted            | 1                        |
| Mysqlx_sessions_closed              | 1                        |
| Mysqlx_sessions_fatal_error         | 0                        |
| Mysqlx_sessions_killed              | 0                        |
| Mysqlx_sessions_rejected            | 1                        |
| Mysqlx_ssl_accepts                  | 0                        |
| Mysqlx_ssl_active                   |                          |
| Mysqlx_ssl_cipher                   |                          |
| Mysqlx_ssl_cipher_list              |                          |
| Mysqlx_ssl_ctx_verify_depth         | 18446744073709551615     |
| Mysqlx_ssl_ctx_verify_mode          | 5                        |
| Mysqlx_ssl_finished_accepts         | 0                        |
| Mysqlx_ssl_server_not_after         | Oct 19 05:28:15 2025 GMT |
| Mysqlx_ssl_server_not_before        | Oct 22 05:28:15 2015 GMT |
| Mysqlx_ssl_verify_depth             |                          |
| Mysqlx_ssl_verify_mode              |                          |
| Mysqlx_ssl_version                  |                          |
| Mysqlx_stmt_create_collection       | 0                        |
| Mysqlx_stmt_create_collection_index | 0                        |
| Mysqlx_stmt_disable_notices         | 0                        |
| Mysqlx_stmt_drop_collection         | 0                        |
| Mysqlx_stmt_drop_collection_index   | 0                        |
| Mysqlx_stmt_enable_notices          | 0                        |
| Mysqlx_stmt_execute_sql             | 69                       |
| Mysqlx_stmt_execute_xplugin         | 2                        |
| Mysqlx_stmt_kill_client             | 0                        |
| Mysqlx_stmt_list_clients            | 0                        |
| Mysqlx_stmt_list_notices            | 0                        |
| Mysqlx_stmt_list_objects            | 2                        |
| Mysqlx_stmt_ping                    | 0                        |
| Mysqlx_worker_threads               | 2                        |
| Mysqlx_worker_threads_active        | 0                        |
+-------------------------------------+--------------------------+
[root@misc01 MID2016]# 

参考までに、MySQL ShellのSQLモードの場合とjavascriptモードの場合では接続が3306か33060からの接続かどうかは以下のステータス変数でも確認する事が可能です。

【SQLモードの場合】


[root@misc01 MID2016]# echo "INSERT INTO T_UC01 VALUES (1,'mysql shell'),(2,'マイエスキュウエルシェル');" | mysqlsh -u demo_user -ppassword --sql --schema=world_x2
mysqlx: [Warning] Using a password on the command line interface can be insecure.
[root@misc01 MID2016]# 


root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> 


【JavaScriptモードの場合】


[root@misc01 MID2016]# mysqlsh --uri demo_user@localhost/NEW57 -ppassword
mysqlx: [Warning] Using a password on the command line interface can be insecure.
Creating an X Session to demo_user@localhost:33060/NEW57
Default schema `NEW57` accessible through db.

Welcome to MySQL Shell 1.0.3 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> db.createCollection("x_posts");
<Collection:x_posts>
mysql-js> db.x_posts.add({"title":"Hello World", "text":"This is the first post via mysqlx"});
Query OK, 1 item affected (0.01 sec)

mysql-js> db.x_posts.find("title = 'Hello World'").sort(["title"]);
[
    {
        "_id": "d2c6eb188b08e6113d110800279cea3c",
        "text": "This is the first post via mysqlx",
        "title": "Hello World"
    }
]
1 document in set (0.00 sec)

mysql-js> 

mysqlx経由のCRUDの実行数が確認出来る。
INSERT x 1回
SELECT x 1回


root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 1     |
| Mysqlx_crud_insert | 1     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.01 sec)

root@localhost [sys]> 


【参考】
http://dev.mysql.com/doc/refman/5.7/en/mysql-shell.html
http://dev.mysql.com/doc/refman/5.7/en/document-store.html
http://dev.mysql.com/doc/dev/connector-nodejs/
http://mysqlserverteam.com/mysql-5-7-12-part-3-more-than-just-sql/
http://mysqlserverteam.com/mysql-5-7-12-part-4-a-new-mysql-command-line-shell/

メモ:Node.jsのサンプルもあるので、後日、別途JSONドキュメント処理を実施してみる。
http://dev.mysql.com/doc/dev/connector-nodejs/