Shulou(Shulou.com)06/01 Report--

The following to understand the new MySQL 5.7 support--------How to create a Json type index, I believe we will certainly benefit a lot after reading, the text is not more refined, I hope MySQL 5.7 new support-------How to create a Json type index This short content is what you want.

1. background

* In MySQL 5.7.8, MySQL supports the native JSON data type defined by RFC 7159, which supports efficient access to data in JSON(JavaScript Object Markup) documents.

* MySQL automatically validates DML JSON data. Invalid DML JSON data operations produce errors.

* Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that allows quick read access to Json elements.

* MySQL Json type supports indexing through virtual columns to increase query performance.

2. JSON index

* Create Json index table json_key [ name is virtual column, virtual indicates no disk space]

[ GENERATED ALWAYS AND VIRTUAL CAN NOT BE WRITTEN]

Specify to get name key in json

mysql> CREATE TABLE json_key( -> uid BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT, -> data JSON NOT NULL, -> name VARCHAR(32) GENERATED ALWAYS AS (json_extract(data, '$.name')) VIRTUAL, -> )ENGINE=INNODB CHARSET=utf8mb4;

* Create virtual column name index

mysql> alter table users add key (name);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0

* insert data with name key in data [specify non-virtual column 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 name key in data

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 data for json_key

mysql> select * from json_key;+-----+---------------------------------------------+-------+| uid | data | name |+-----+---------------------------------------------+-------+| 1 | {"age": "26", "sex": "male", "name": "tom"} | "tom" || 2 | {"age": "29", "sex": "female"} | NULL |+-----+---------------------------------------------+-------+2 rows in set (0.01 sec)

3. query test

* Query by json method

mysql> explain select * from json_key where json_extract(data, '$.name') = 'tom'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: json_key partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)

* Query through virtual columns

mysql> explain select * from json_key where name = 'tom'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: users

partitions: NULL

type: ref

possible_keys: name

key: name

key_len: 1023

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.01 sec)

4. summary

Demand-driven technology, technology itself has no advantages, only business points.

After reading MySQL 5.7 new support-------How to create Json type index this article, many readers will definitely want to know more about the relevant content, if you need more industry information, you can pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.