MySQL 5.7 New Support--------JSON index creation combat

1. Background

* in MySQL 5.7.8, MySQL supports the native JSON data type defined by RFC 7159, which supports effective access to data in JSON (JavaScript object tagging) documents.

* MySQL automatically validates DML JSON data. An invalid DML JSON data operation generates an error.

* optimized storage format. JSON documents stored in JSON columns are converted to an internal format that allows for fast read access to JSON elements.

* MySQL JSON type supports indexing with virtual columns to increase query performance.

2. Json Index

* Create a JSON index table Json_key [name is a virtual column, and virtual indicates that it does not occupy disk space]

[GENERATED always and virtual can not write]

Specify to get the name key in the JSON

Mysql> CREATE TABLE json_key (uid BIGINT PRIMARY key NOT NULL auto_increment, data JSON is not NULL, Name VARCHAR (+) GENERATED always as (json_extract (data, ' $.name ')) Engine=innodb CHARSET=UTF8MB4 ;

* Insert data with the name key [need to display specified non-virtual columns when inserting data]

Mysql> INSERT into Json_key (UID, data) SELECT NULL, Json_object (' name ', ' Tom ', ' sex ', ' male ', ' age ', ' 26 '); Query OK, 1 row affected (0.02 sec) records:1 duplicates:0 warnings:0

* Insert data without the name Key

Mysql> INSERT into Json_key (UID, data) SELECT NULL, json_object (' sex ', ' female ', ' age ', ' 29 '); Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0

* View all json_key data

mysql> select * from json_key;+-----+------------ ---------------------------------+-------+| uid | data                                          | name   |+-----+---------------------------------------------+-------+|   1 | {" Age ": ",  "Sex":  "male",  "name":  "Tom"} |  "Tom"  | |    2 | {"Age":  ", " Sex ": " female "}               | null  |+-----+------------------------- --------------------+-------+2 rows in set  (0.01 sec) 

4. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1943389

MySQL 5.7 New Support--------JSON index creation combat