A Brief Introduction to MySQL5.7 JSON format retrieval, mysql5.7json

A Brief Introduction to MySQL5.7 JSON format retrieval, mysql5.7json

MySQL and later versions support JSON format. When creating a table, you can specify the Data Type of the List as JSON. But how to create an index in JSON format ??

I did a simple test.

Step 1: Create a table containing the JSON type:

CREATE TABLE json_test` ( id` int (8) NOT NULL AUTO_INCREMENT, content` json NOT NULL , PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 2: Initialize data

insert into json_test(content) value( '{"name":"zhangsan","age":18}' );insert into json_test(content) value( '{"name":"lisi","age":19}' );insert into json_test(content) value( '{"name":"wangwu","age":20}' );

Step 3: query data in a JSON Column

select json_extract(content,  '$.name' )  from json_test  where json_extract(content,  '$.name' )= "zhangsan" ;

Through expain analysis and modification of the query statement, it is found that the full table scan is performed.

Query Information on the Internet. If you want to search on the JSON column, you need to create a virtual column for The retrieved key, and then create an index on the virtual column.

Step 4: create a virtual column for "name" in the content Column

ALTER TABLE json_test  ADD name_virtual  varchar (32) GENERATED ALWAYS  AS (json_extract(content,  '$.name' )) VIRTUAL;


Step 5: Create an index for a virtual Column

CREATE INDEX name_virtual_index  ON json_test(name_virtual);


Re-query (Note: The where condition requires the use of virtual columns for retrieval. If the JSON column is used for comparison, full table scan is performed)

explain  select json_extract(content,  '$.name' )  from json_test  where name_virtual= "zhangsan" \G

Summary:

In fact, MySQL uses a space-for-time approach, similar to creating a trigger to store redundant data on the JSON column to the virtual column. During comparison, it uses the index of the virtual column, then locate the actual data.