10月19日にMySQL5.7がGAになったので、新規追加されたJSONデータ型の確認を行いました。
JSONはXMLと同じように、スマートフォンアプリ用のAPIやB2BやB2C連携でJSON APIが多く使われているようです。

基本的な動作検証
1. 基本的な構文バリデーション機能
2. JSONデータ型とTEXT型のパフォーマンス差
3. JSONとGenerated Columnの連携によるインデックス利用とパフォーマンス
4. JSONドキュメントの部分アップデート

ちなみに、ザックリとGoogleで検索してみると以下のように多くのサイトがJSON用のAPIを提供していました。
Twitter
https://dev.twitter.com/rest/reference/get/statuses/user_timeline
原子力規制委員会
http://radioactivity.nsr.go.jp/data/ja/real/area_24000/2401_trend.json
Google API
https://storage.googleapis.com/maps-devrel/google.json
ぐるナビ
http://api.gnavi.co.jp/api/tools/?apitype=ver1_RestSearchAPI
シカゴ市
https://data.cityofchicago.org/resource/alternative-fuel-locations.json?$limit=100&$offset=50

1. 基本的な構文バリデーション機能

検証テーブル (TEXT型)

CREATE TABLE `employees_txt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

検証テーブル (JSON型)

CREATE TABLE `employees_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

それぞれのテーブルに、構文ミスしたJSONデータを入れてみると以下のようになる。
JSONデータ型はINSERT時のバリデーションが正常に効いている。


root@localhost [NEW57]> INSERT INTO NEW57.employees_txt(data) VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0.00 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_txt(data) VALUES ('{"id": 2, "name": "Joe"');
Query OK, 1 row affected (0.01 sec)

root@localhost [NEW57]> SELECT * FROM NEW57.employees_txt;
+----+---------------------------+
| id | data                      |
+----+---------------------------+
|  1 | {"id": 1, "name": "Jane"} |
|  2 | {"id": 2, "name": "Joe"   |
+----+---------------------------+
2 rows in set (0.00 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_json(data) VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0.01 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_json(data) VALUES ('{"id": 2, "name": "Joe"');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 23 in value (or column) '{"id": 2, "name": "Joe"'.
root@localhost [NEW57]> SELECT * FROM NEW57.employees_json;
+----+---------------------------+
| id | data                      |
+----+---------------------------+
|  1 | {"id": 1, "name": "Jane"} |
+----+---------------------------+
1 row in set (0.00 sec)

root@localhost [NEW57]> 

2. JSONデータ型とTEXT型のパフォーマンス差
セミナーでも実施しましたが、以下のようなシンプルなテーブルに対して、
ダウンロードしてきた、20万6千件のJSONデータをインサートして参照レスポンス比較。

それぞれの参照クエリにて同じコマンドと同じデータでレスポンス比較
SELECT distinct json_extract(feature,’$.type’) as feature FROM NEW57.features_txt;
SELECT distinct json_extract(feature,’$.type’) as feature FROM NEW57.features_json;

結果としては、JSON型の方がTEXT型に比べて圧倒的に早い事が確認出来る。

[root@misc01 SOD2015]# ./json_and_text_without_index.sh 
INDEXの無いテーブルに対するSELECTを、TEXT型とJSONデータ型で比較します。
SELECT distinct json_extract(feature,'$.type') as feature FROM 各テーブル

【TEXT型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m9.724s
user    0m0.005s
sys     0m0.002s

【JSON型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m1.506s
user    0m0.004s
sys     0m0.004s
[root@misc01 SOD2015]# 

3. JSONとGenerated Columnの連携によるインデックス利用とパフォーマンス
JSONドキュメントのオブジェクトの中からSTREET名の部分からデータを抽出して列を作成しIndexを付与。

列:      json_extract(feature,’$.properties.STREET’))
インデックス: KEY `feature_street` (`feature_street`)

 

JSONデータ型のサンプルを入れたテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
            id: 12250
       feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}}
  feature_type: "Feature"
feature_street: "MARKET"
Press [Enter] key to resume.

JSONデータ型とGenerated Columnを利用したテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: features
Create Table: CREATE TABLE `features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.type')) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.properties.STREET')) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `feature_type` (`feature_type`),
  KEY `feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4
Press [Enter] key to resume.

JSONドキュメントに対して、INDEX検索が利用出来るか確認 -> where json_extract(feature,'$.properties.STREET') = '"MARKET"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: features
   partitions: NULL
         type: ref
possible_keys: feature_street
          key: feature_street
      key_len: 123
          ref: const
         rows: 808
     filtered: 100.00
        Extra: NULL
[root@misc01 SOD2015]# 

4. JSONドキュメントの部分アップデート
JSONドキュメントの一部をUPDATEする場合の処理方法確認。
TEXT型だと、全ての列データを入れ替える必要がありますが、JSONだと一部のみ変更可能です。

select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+------------------------------------------------------------------------+---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}| 50000   |
+----+------------------------------------------------------------------------+---------+

update T_JSON_DOC set T_JSON_DOC.body = JSON_REPLACE(body,"$.price",15000) 
where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+----------------------------------------------------------------------- +---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "冷蔵庫", "price": 15000, "Conditions": ["NEW", 2015]}| 15000   |
+----+------------------------------------------------------------------------+---------+

文字入れ替えに便利なJSONファンクション(例)
JSON_SET() 既存の値を置き換え、存在しない値を追加
JSON_INSERT() 既存の値を置き換えずに値を挿入
JSON_REPLACE() 既存の値のみを置き換えます

参照:
11.6 The JSON Data Type
https://dev.mysql.com/doc/refman/5.7/en/json.html

MySQL5.7セミナー資料
https://www-jp.mysql.com/news-and-events/seminar/downloads.html


MYSQLでUPDATE文を利用する場合の構文

単一テーブル構文:


UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

単一テーブル構文には、UPDATE ステートメントは新しい値を利用して tbl_name 内に既存行のカラムを
更新します。SET 条項は、どのカラムを変更し、それらにはどの値が与えられるべきかという事を指示します。
もし WHERE 条項が与えられたら、それはどの行を更新するべきかを決定します。WHERE 条項が無ければ、
全ての行が更新されます。もし ORDER BY 条項が指定されると、指定された順に行が更新されます。
LIMIT 条項は、更新できる行数に制限を設定します。

update_single_table

複合テーブル構文:


UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition]

複合テーブル構文には、UPDATE が、条件を満たす table_references
で名づけられたそれぞれのテーブルの行を更新します。この場合、ORDER BY と LIMIT を利用する事はできません
※  MySQL バージョン 4.0.4 以降では、複数のテーブルに対する UPDATE 操作も実行可能。


UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

上の例では、カンマ演算子を使用した内部結合を示していますが、複数テーブルの
UPDATE ステートメントでは、LEFT JOIN など、SELECT ステートメントで使用可能な任意
の結合型を使用することができます。


mysql> update MYSQLIMP,MYSQLIMP2 set MYSQLIMP.n = MYSQLIMP2.n
-> where MYSQLIMP.id = MYSQLIMP2.id
-> and MYSQLIMP.id = 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

update_multi_tables

UPDATE ステートメントは次の修飾因子をサポートします。

もし LOW_PRIORITY キーワードを利用すると、別のクライアントがテーブルからの
読み込みをしなくなるまで、UPDATE の実行が遅れます。

もし IGNORE キーワードを利用すると、更新中にエラーが発生しても更新ステートメントは異常終了しません。
複製キーの矛盾が起きた行は更新されません。データ変換エラーを起こす値にカラムが更新された行は、
代わりに一番近い有効値に更新されます。

単一テーブル UPDATE 割り当ては通常左から右に評価されます。複合テーブルの更新に関しては、
割り当てが特定の順番で行われるという保証はありません。

NULL に設定する事で NOT NULL を宣言されたカラムを更新すると、カラムはそのデータ タイプに
適切なデフォルト値に設定され、警告カウントはインクリメントされます。
数値タイプ、文字列タイプの空の文字列(”)、そして日付と時刻タイプの「ゼロ」 値のデフォルト値は 0 です。

UPDATE の領域を制限する為に LIMIT row_count を利用する事ができます。
LIMIT 条項は行に一致した制限です。ステートメントは、実際に変更されたかどうかに関わらず、
WHERE 条項の条件を満たす row_count 行を見付けるとすぐに止まります。

もし UPDATE ステートメントが ORDER BY 条項を含むなら、行は条項に指示された順番で更新されます。
これは、エラーが起こるかもしれない特定の場合に有効です。
複合テーブルをカバーする UPDATE 演算を行う事もできます。
.しかし、複合テーブル UPDATE と共に ORDER BY や LIMIT を利用する事はできません。



CREATE VIEW V_CITYN_COUNTRYN (CityName, CountryName)
AS SELECT City.Name, Country.Name FROM City, Country
WHERE City.CountryCode = Country.Code
AND City.CountryCode = 'JPN';

view_update_test


update V_CITYN_COUNTRYN SET CityName = 'Yokohama'
where CityName = 'Jokohama [Yokohama]';

update_view


update V_CITYN_COUNTRYN SET CityName = 'TOKYO',
CountryName = 'JAPAN'
where CityName = 'Tokyo'
AND CountryName = 'Japan';

上記UPDATE文は二つのテーブルからなるVIEWにて同時にそれぞれのテーブルを
更新しようとしている為エラーになっている。

ERROR 1393 (HY000): Can not modify more than one base table through a join view
tables_two

update_view_multiple

insert into V_CITYN_COUNTRYN values('Toukyou','Japan');
insert into V_CITYN_COUNTRYN(CityName,CountryName) values('Toukyou','Japan');

上記INSERTも2つのテーブルを同時にINSERTしようとしてエラーになっている。
(the view is insertable only if a single table is affected)

ERROR 1393 (HY000): Can not modify more than one base table through a join view ‘STUDY.V_CITYN_COUNTRYN’

view_insert_table

VIEW経由で一つのテーブルに対してINSERTしてみるとINSERTは出来たが、
VIEWは2つのテーブルをJOINして成り立っているのでVIEWをSELECTしてもINSERTしたデータは出てこない。

view_insert_single_table

VIEWは便利ですが、色々な制限があるので色々を参考になるサイトを読んでテストしてみた方がいいですね。
GROUP BY,COUNT,列+1などの関数を利用しているVIEWは他のRDBMS同様にINSERT、UPDATE出来ません。

以下のいずれかを含んでいるとビューは更新可能となりません。
——————————————————————————————
* 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
*DISTINCT
*GROUP BY
*HAVING
*UNION もしくはUNION ALL
*選択リスト中のサブ・クエリ
その他………………………..
——————————————————————————————

(例)以下のVIEWは集計ファンクションやGroup byを使用しているので、更新処理は出来ません。
The view is not updatable because it uses aggregate functions and GROUP BY.


Create View V_Country_Area
(Continent,Total_Surface,Average_Surface)
as
select Continent,sum(SurfaceArea),avg(SurfaceArea)
from Country group by Continent;

view_group_by

またMYSQL5.1では以下のような固有の制限もあるようです。
ビューの FROM 句でサブクエリを使用することはできません。

    この制限はいずれ取り除かれる予定です。

参考サイト
D.4. ビューの規制
20.2. CREATE VIEW 構文


大陸の名前、面積が入っているテーブルをベースに大陸毎の面積や各大陸に存在する
国の平均面積を算出するViewを作成。

下記2つの内容は同じViewになります。


Create View V_Surface
(NAME, SUM_Surface, AVG_Suface) AS
select continent, sum(SurfaceArea),avg(SurfaceArea)
from Country Group by Continent;


Create View V_Surface2 AS
select continent as NAME, sum(SurfaceArea) as SUM_Surface,
avg(SurfaceArea) as AVG_Suface from Country Group by Continent;

create_view_surface

上記のViewはSUMなどのAggregate funcation(集合関数)を利用していることGroup byを
利用しているので、更新することは出来ない。

view_update_fail

——————————————————————-
・データベース構造を意識させない。
・正規化によって分解された表を、Viewにて組み合わせて一つのテーブルのように操作可能。
・Tableへ直接の権限を付与しないでViewにて権限を制限する事でセキュリティ強化
・複数の表からなるビューは更新できない。
・集合関数や演算を使用して作成したViewはUpdateできない。


ビュー(更新可能なビューを含む) はMySQL Server 5.1から入手可能。
旧バージョンMySQLを5.1にアップグレードした場合、ビューの使用はビュー関連の
権限を含むようにグラントテーブルもアップグレードが必要です。

———————————
メモ
———————————
mysql_upgrade コマンドは、古い方、つまり mysql_fix_privilege_tables より優先です。
MySQL 5.1.7 では、シェル スクリプト として mysql_upgrade が加えられ、Unix システムだけで機能します。
MySQL 5.1.10 以降は、mysql_upgrade は実行可能なバイナリとして、すべてのシステムで使用できます。
mysql_upgrade をサポートしているものより古いシステムでは、手動で mysqlcheck コマンドを実行し、
システム テーブルのアップグレードを行ないます

ビューの現在の実装には欠点があります。もしユーザがビューの作成に必要な基本権限
( CREATE VIEW と SELECT 権限 ) を取得した場合、SHOW VIEW 権限も取得しない限り、
そのユーザはオブジェクトの SHOW CREATE VIEW を呼び出すことはできない。

PREPARE によって準備されたステートメントがビューを参照する場合、後でステートメントが実行される度
に参照されるビューの内容が、ステートメントが準備できた時のビューの内容になります。
これは、ステートメントが準備された後、実行される前にビュー定義が変更されても同じ。


CREATE VIEW v AS SELECT 1;
PREPARE s FROM 'SELECT * FROM v';
ALTER VIEW v AS SELECT 2;
EXECUTE s;

view11

幾つかのビューは更新可能です。すなわち、基礎をなすテーブルの内容を更新するため、
UPDATE、DELETEもしくはINSERTのようなステートメントの中でそれらを使うことができます。
ビューを更新可能にするため、ビュー中の行と基礎テーブル中の行の間に1対1の関係が存在しなければなりません。
ビューを更新不能にするその他の生成子もあります。もっと具体的に言うと、それが以下のいずれかを含んでいると
ビューは更新可能となりません。

* 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
* DISTINCT
* GROUP BY
* HAVING
* UNION もしくはUNION ALL
* 選択リスト中のサブ・クエリ
* 特定結合(このセクション中の後の部分に追加した結合の説明参照)
* FROM節中の更新不能ビュー
* FROM節中のテーブルを参照するWHERE 節中のサブ・クエリ
* 文字値だけを参照(この場合、更新する基礎となるテーブルは存在しません)
* ALGORITHM = TEMPTABLE (テンポラリテーブルの使用は常にビューを更新不能にする)

(INSERTステートメントで更新不能となる)挿入性に関して、それがビューカラムに対する
これらの追加条件も満たすと、更新不能ビューが挿入可能になります。

* ビューカラム名に重複があってはなりません。
* ビューには、デフォルト値を持っていないベーステーブル内にある全てのカラムを含んでいなくてはなりません。
* ビューカラムは派生カラムではなく、単純なカラムリファレンスでなければなりません。
派生カラムは単純なカラムリファレンスでなく、表現から派生したものです。これらは派生カラムの例です。

3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)

UPDATE,INSERTが可能か確認検証


CREATE VIEW V_Region (CityName, CountryName)
AS SELECT City.Name, Country.Name FROM City, Country
WHERE City.CountryCode = Country.Code
AND City.CountryCode = 'DEU';

VIEW
create_view

    UPDATE確認


update V_Region set CityName = 'HAMBURG'
where CityName = 'Hamburg';

1つのテーブルしか更新されないのでエラーにはならない。
view_update

複数テーブルを更新しようとするとUPDATEもエラーになる

update V_Region set
CityName = 'Hamburg',
CountryName = 'Japan'
where CityName = 'HAMBURG';

複数テーブルを更新しようとしているのでエラーになる
view_update_multi

    INSERT確認

insert into V_Region(CityName,CountryName) values('NewCity','Japan');

※ 複数のテーブルを更新することになるのでエラーになる。
view_insert1

参考サイト
20.2. CREATE VIEW 構文


D.4. ビューの規制


4.5.4. mysql_upgrade — MySQL アップグレードのテーブル チェック