一、概述
mysql从5.7后引入了json数据类型以及json函数，可以有效的访问json格式的数据。json数据类型相对于字符串，具有以下优点：
1）对于json列数据提供自动校验json格式，错误格式会提示错误；
2）优化存储类型。数据以二进制方式保存，读取效率快；
3）允许通过键值或者数据索引查找对象，无需把整个数据读取出来；
除此之外，json还有以下特点：
1）json存储空间大致于longblob或longtext差不多；
2）mysql8.0.13之后，json允许默认值为null；
3）json列不能设置索引，可通过json中的键值设置索引来提高查询效率；
4）json中null、true、false必须使用小写，
二、JSON函数介绍
1、创建JSON函数
JSON_ARRAY([val[,?val] ...])：
每一个val值作为json数据中的元素
mysql> select JSON_ARRAY(1,'2',now());
+----------------------------------------+
| JSON_ARRAY(1,'2',now()) |
+----------------------------------------+
| [1, "2", "2021-10-26 12:00:03.000000"] |
+----------------------------------------+
1 row in set (0.00 sec)
?JSON_OBJECT([key, val[, key, val] ...])：
格式为：key，value，key，value...
mysql> select JSON_OBJECT('name','main','age',18,'birthday',now());
+-----------------------------------------------------------------------+
| JSON_OBJECT('name','main','age',18,'birthday',now()) |
+-----------------------------------------------------------------------+
| {"age": 18, "name": "main", "birthday": "2021-10-26 12:06:31.000000"} |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
创建含有json字段的表结构：
mysql> create table json_test(id int, content json default null);
Query OK, 0 rows affected (0.63 sec)
mysql> insert into json_test values (10000, '{"name":"test","age":18,"body":{"height":180,"weight":70,"friends":["Tom","Lucy","Lili"]}}');
Query OK, 1 row affected (0.26 sec)
2、查询JSON函数
JSON_CONTAINS(target, candidate[, path])
通过返回0或1，判断目标对象target是否包含candidate元素，如果查询包含path路径，则在指定的路径上面查找。
mysql> select JSON_CONTAINS('[1,2,"3",4]','1');
+----------------------------------+
| JSON_CONTAINS('[1,2,"3",4]','1') |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_CONTAINS('[1,2,"3",4]','3');
+----------------------------------+
| JSON_CONTAINS('[1,2,"3",4]','3') |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_CONTAINS('[1,2,"3",4]','"3"');
+------------------------------------+
| JSON_CONTAINS('[1,2,"3",4]','"3"') |
+------------------------------------+
| 1 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_CONTAINS('{"name":"test","age":0}','0','$.age');
+------------------------------------------------------+
| JSON_CONTAINS('{"name":"test","age":0}','0','$.age') |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
1 row in set (0.00 sec)
注意：JSON格式的数据都是字符串，单个字符也要加引号，比如数字 '0'，如果是表示字符串，'"0"'，方法中的参数必须符合json类型，否则报错。
JSON_CONTAINS_PATH(target, one_or_all, path[, path] ...)
通过返回0或1，判断在目标对象target中，指定路径是否包含数据，one_or_all只能是one或者all，如果是one表示多个path下有一个有数据返回1，如果是all，指定的path下必须都有值才能返回1。
mysql> select JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','one','$.a','$.c');
+----------------------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','one','$.a','$.c') |
+----------------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','ONE','$.a','$.c');
+----------------------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','ONE','$.a','$.c') |
+----------------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','all','$.a','$.c');
+----------------------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','all','$.a','$.c') |
+----------------------------------------------------------------------------+
| 0 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','all','$.a','$.b');
+----------------------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','all','$.a','$.b') |
+----------------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','all','$.a','$.b.b2');
+-------------------------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"a":1,"b":{"b1:":3,"b2":4},"d":0}','all','$.a','$.b.b2') |
+-------------------------------------------------------------------------------+
| 1 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_EXTRACT(target, path[, path] ...)
返回所查询path路径对应的value值，如果查询多个path，返回值自动组装成数组
mysql> select JSON_EXTRACT('[1,2,[4,5],6,7]','$[2]');
+----------------------------------------+
| JSON_EXTRACT('[1,2,[4,5],6,7]','$[2]') |
+----------------------------------------+
| [4, 5] |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_EXTRACT('[1,2,[4,5],6,7]','$[2][1]');
+-------------------------------------------+
| JSON_EXTRACT('[1,2,[4,5],6,7]','$[2][1]') |
+-------------------------------------------+
| 5 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_EXTRACT('[1,2,[4,5],6,7]','$[2][2]');
+-------------------------------------------+
| JSON_EXTRACT('[1,2,[4,5],6,7]','$[2][2]') |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_EXTRACT('[1,2,[4,5],6,7]','$[2]','$[3]');
+-----------------------------------------------+
| JSON_EXTRACT('[1,2,[4,5],6,7]','$[2]','$[3]') |
+-----------------------------------------------+
| [[4, 5], 6] |
+-----------------------------------------------+
1 row in set (0.01 sec)
mysql> select JSON_EXTRACT('{"a":1,"b":{"b1:":3,"b2":4},"d":0}', '$.b', '$.d');
+------------------------------------------------------------------+
| JSON_EXTRACT('{"a":1,"b":{"b1:":3,"b2":4},"d":0}', '$.b', '$.d') |
+------------------------------------------------------------------+
| [{"b2": 4, "b1:": 3}, 0] |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
column->path
->是JSON_EXTRACT(target, path)的缩减版(只有一个path场景)，->可用于日常SQL表达式中，比如update、delete、select等。
+-------+--------------------------------------------------------------------------------------------------------+
| id | content |
+-------+--------------------------------------------------------------------------------------------------------+
| 10000 | {"name": "test"} |
| 10000 | {"age": 18, "body": "{}", "name": "test"} |
| 10000 | {"age": 18, "body": {"age": 19}, "name": "test"} |
| 10000 | {"age": 18, "body": {"height": 180, "weight": 70, "friends": ["Tom", "Lucy", "Lili"]}, "name": "test"} |
| 1 | {"body": "{\"weight\":70}", "name": "test"} |
| 1 | {"body": "{weight:70}", "name": "test"} |
+-------+--------------------------------------------------------------------------------------------------------+
mysql> select content->'$.name' from json_test where content->'$.body.age' = 19;
+-------------------+
| content->'$.name' |
+-------------------+
| "test" |
+-------------------+
1 row in set (0.00 sec)
column->>path
column->path返回的包含引号""，column->>path返回的结果自动去掉""，与下面几种方式返回功能一样：
JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path
mysql> select content->>'$.name' from json_test where content->'$.body.age' = 19;
+--------------------+
| content->>'$.name' |
+--------------------+
| test |
+--------------------+
1 row in set (0.00 sec)
mysql> select JSON_UNQUOTE(content->'$.name') from json_test where content->'$.body.age' = 19;
+---------------------------------+
| JSON_UNQUOTE(content->'$.name') |
+---------------------------------+
| test |
+---------------------------------+
1 row in set (0.01 sec)
mysql> select JSON_UNQUOTE(JSON_EXTRACT(content, '$.name')) from json_test where content->'$.body.age' = 19;
+-----------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(content, '$.name')) |
+-----------------------------------------------+
| test |
+-----------------------------------------------+
1 row in set (0.00 sec)
JSON_KEYS(target[, path])
如果path为空，将target对象中第一层key值组装成数组返回，如果path不为空，在指定path的路径下返回key值组成后的数组
mysql> select JSON_KEYS('{"age": 18, "body": {"height": 180, "weight": 70, "friends": ["Tom", "Lucy", "Lili"]}, "name": "test"}');
+---------------------------------------------------------------------------------------------------------------------+
| JSON_KEYS('{"age": 18, "body": {"height": 180, "weight": 70, "friends": ["Tom", "Lucy", "Lili"]}, "name": "test"}') |
+---------------------------------------------------------------------------------------------------------------------+
| ["age", "body", "name"] |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_KEYS('{"age": 18, "body": {"height": 180, "weight": 70, "friends": ["Tom", "Lucy", "Lili"]}, "name": "test"}', '$.body');
+-------------------------------------------------------------------------------------------------------------------------------+
| JSON_KEYS('{"age": 18, "body": {"height": 180, "weight": 70, "friends": ["Tom", "Lucy", "Lili"]}, "name": "test"}', '$.body') |
+-------------------------------------------------------------------------------------------------------------------------------+
| ["height", "weight", "friends"] |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2、修改JSON函数
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
在指定路径上追加元素
mysql> select JSON_ARRAY_APPEND('{"name":"test"}','$.name','{"age":20}');
+------------------------------------------------------------+
| JSON_ARRAY_APPEND('{"name":"test"}','$.name','{"age":20}') |
+------------------------------------------------------------+
| {"name": ["test", "{\"age\":20}"]} |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_ARRAY_APPEND('[1,[2,3],4,5]','$',0,'$[4]',6);
+---------------------------------------------------+
| JSON_ARRAY_APPEND('[1,[2,3],4,5]','$',0,'$[4]',6) |
+---------------------------------------------------+
| [1, [2, 3], 4, 5, [0, 6]] |
+---------------------------------------------------+
1 row in set (0.00 sec)
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
在指定的path上添加元素，相对于JSON_ARRAY_APPEND，INSERT是新加元素，不会和之前存在的元素组成新的数组
mysql> select JSON_ARRAY_INSERT('{"name":[1,2]}','$.name[2]',1);
+---------------------------------------------------+
| JSON_ARRAY_INSERT('{"name":[1,2]}','$.name[2]',1) |
+---------------------------------------------------+
| {"name": [1, 2, 1]} |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_ARRAY_INSERT('[1,3,[5,7],9,11]','$[1]',2);
+------------------------------------------------+
| JSON_ARRAY_INSERT('[1,3,[5,7],9,11]','$[1]',2) |
+------------------------------------------------+
| [1, 2, 3, [5, 7], 9, 11] |
+------------------------------------------------+
1 row in set (0.00 sec)
选择的元素必须是数组，否则无效：
mysql> select JSON_ARRAY_INSERT('[1,3,[5,7],{"a": [0,10]},11]','$[3][0]',2);
+---------------------------------------------------------------+
| JSON_ARRAY_INSERT('[1,3,[5,7],{"a": [0,10]},11]','$[3][0]',2) |
+---------------------------------------------------------------+
| [1, 3, [5, 7], {"a": [0, 10]}, 11] |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_ARRAY_INSERT('[1,3,[5,7],{"a": [0,10]},11]','$[3].a[0]',2);
+-----------------------------------------------------------------+
| JSON_ARRAY_INSERT('[1,3,[5,7],{"a": [0,10]},11]','$[3].a[0]',2) |
+-----------------------------------------------------------------+
| [1, 3, [5, 7], {"a": [2, 0, 10]}, 11] |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_INSERT(json_doc, path, val[, path, val] ...)
向JSON数据中添加元素
mysql> select JSON_INSERT('{"name":"test","age":18,"body":{"weight":70}}','$.name',"new-test",'$.body.height','180');
+--------------------------------------------------------------------------------------------------------+
| JSON_INSERT('{"name":"test","age":18,"body":{"weight":70}}','$.name',"new-test",'$.body.height','180') |
+--------------------------------------------------------------------------------------------------------+
| {"age": 18, "body": {"height": "180", "weight": 70}, "name": "test"} |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
因为$.name已经存在，所以插入无效。
JSON_REPLACE(json_doc, path, val[, path, val] ...)
替换JSON数据中原有数据，如果没有，则不更新
mysql> select JSON_REPLACE('{"name":"test","age":18,"body":{"weight":70}}','$.name',"new-test",'$.body.height','180');
+---------------------------------------------------------------------------------------------------------+
| JSON_REPLACE('{"name":"test","age":18,"body":{"weight":70}}','$.name',"new-test",'$.body.height','180') |
+---------------------------------------------------------------------------------------------------------+
| {"age": 18, "body": {"weight": 70}, "name": "new-test"} |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_SET(json_doc, path, val[, path, val] ...)
如果JSON数据中存在path，则更新数据，如果不存在，插入数据
mysql> select JSON_SET('{"name":"test","age":18,"body":{"weight":70}}','$.name',"new-test",'$.body.height','180');
+-----------------------------------------------------------------------------------------------------+
| JSON_SET('{"name":"test","age":18,"body":{"weight":70}}','$.name',"new-test",'$.body.height','180') |
+-----------------------------------------------------------------------------------------------------+
| {"age": 18, "body": {"height": "180", "weight": 70}, "name": "new-test"} |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
将多个JSON数据合并：
1）如果第一个JSON不是对象，合并的结果为第二个对象
mysql> select JSON_MERGE_PATCH('1','{"name":"test"}');
+-----------------------------------------+
| JSON_MERGE_PATCH('1','{"name":"test"}') |
+-----------------------------------------+
| {"name": "test"} |
+-----------------------------------------+
1 row in set (0.00 sec)
2）如果第二个JSON不是对象，则合并的结果为第二个元素
mysql> select JSON_MERGE_PATCH('{"name":"test"}','1');
+-----------------------------------------+
| JSON_MERGE_PATCH('{"name":"test"}','1') |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_MERGE_PATCH('1','{"name":"test"}', '1', '{"b":1}');
+---------------------------------------------------------+
| JSON_MERGE_PATCH('1','{"name":"test"}', '1', '{"b":1}') |
+---------------------------------------------------------+
| {"b": 1} |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_MERGE_PATCH('{"name":"test"}', '1', '{"b":1}');
+-----------------------------------------------------+
| JSON_MERGE_PATCH('{"name":"test"}', '1', '{"b":1}') |
+-----------------------------------------------------+
| {"b": 1} |
+-----------------------------------------------------+
1 row in set (0.00 sec)
3）如果都是对象，有重复的key值，后面的value会覆盖前面的值
mysql> select JSON_MERGE_PATCH('{"name":"test"}','{"a":1}');
+-----------------------------------------------+
| JSON_MERGE_PATCH('{"name":"test"}','{"a":1}') |
+-----------------------------------------------+
| {"a": 1, "name": "test"} |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_MERGE_PATCH('{"name":"test","age":20,"body":{"weight":180}}','{"a":0,"age":18,"body":{"height":180,"weight":70}}');
+-------------------------------------------------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{"name":"test","age":20,"body":{"weight":180}}','{"a":0,"age":18,"body":{"height":180,"weight":70}}') |
+-------------------------------------------------------------------------------------------------------------------------+
| {"a": 0, "age": 18, "body": {"height": 180, "weight": 70}, "name": "test"} |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4）合并后，会将value为null的过滤掉
mysql> select JSON_MERGE_PATCH('{"name":"test","age":20,"body":{"weight":180}}', '{"NAME":"new","salary":null}');
+----------------------------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{"name":"test","age":20,"body":{"weight":180}}', '{"NAME":"new","salary":null}') |
+----------------------------------------------------------------------------------------------------+
| {"age": 20, "NAME": "new", "body": {"weight": 180}, "name": "test"} |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
根据第一和第四可以通过使用常量+JSON对象过滤为null的键值
mysql> select JSON_MERGE_PATCH('1','{"name":"test","salary":null}');
+-------------------------------------------------------+
| JSON_MERGE_PATCH('1','{"name":"test","salary":null}') |
+-------------------------------------------------------+
| {"name": "test"} |
+-------------------------------------------------------+
1 row in set (0.00 sec)
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
相对于JSON_MERGE_PATCH，如果有重复的key值，会组成新的数组元素
mysql> select JSON_MERGE_PRESERVE('{"name":"test","age":20}','{"a":0,"age":18}');
+--------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name":"test","age":20}','{"a":0,"age":18}') |
+--------------------------------------------------------------------+
| {"a": 0, "age": [20, 18], "name": "test"} |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
两个数组或常量会生成新的数组
mysql> select JSON_MERGE_PRESERVE('[1,2]','["a","b"]');
+------------------------------------------+
| JSON_MERGE_PRESERVE('[1,2]','["a","b"]') |
+------------------------------------------+
| [1, 2, "a", "b"] |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_MERGE_PRESERVE('1','2','"a"');
+------------------------------------+
| JSON_MERGE_PRESERVE('1','2','"a"') |
+------------------------------------+
| [1, 2, "a"] |
+------------------------------------+
1 row in set (0.00 sec)
两个对象会生成新的对象，有相关key值的不会被覆盖
mysql> select JSON_MERGE_PRESERVE('{"name":"test","age":20}','{"a":0,"age":18}');
+--------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name":"test","age":20}','{"a":0,"age":18}') |
+--------------------------------------------------------------------+
| {"a": 0, "age": [20, 18], "name": "test"} |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
一个对象和一个数组会生成新的数组
mysql> select JSON_MERGE_PRESERVE('{"name":"test","age":20}','[1,2,"a"]');
+-------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name":"test","age":20}','[1,2,"a"]') |
+-------------------------------------------------------------+
| [{"age": 20, "name": "test"}, 1, 2, "a"] |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_REMOVE(json_doc, path[, path] ...)
将指定path的元素删除
mysql> select JSON_REMOVE('{"name":"test"}','$.name');
+-----------------------------------------+
| JSON_REMOVE('{"name":"test"}','$.name') |
+-----------------------------------------+
| {} |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_REMOVE('{"name":"test","age":18}','$.name');
+--------------------------------------------------+
| JSON_REMOVE('{"name":"test","age":18}','$.name') |
+--------------------------------------------------+
| {"age": 18} |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_REMOVE('{"name":"test","age":18}','$.body');
+--------------------------------------------------+
| JSON_REMOVE('{"name":"test","age":18}','$.body') |
+--------------------------------------------------+
| {"age": 18, "name": "test"} |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_REMOVE('[1,2,3]','$[0]');
+-------------------------------+
| JSON_REMOVE('[1,2,3]','$[0]') |
+-------------------------------+
| [2, 3] |
+-------------------------------+
1 row in set (0.00 sec)
其他JSON函数可参考官网：MySQL :: MySQL 8.0 Reference Manual :: 12.18 JSON Functions
三、JSON类型其他特性
json数据比较和排序支持=, <, <=, >, >=, <>, !=, and <=>，不支持BETWEEN，IN()，GREATEST()，LEAST()；
JSON值比较有优先级，下面是优先级从高到低，不同层级的优先级，按照优先级高的确定
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL
mysql> select JSON_ARRAY(1) > JSON_ARRAY('1');
+---------------------------------+
| JSON_ARRAY(1) > JSON_ARRAY('1') |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_ARRAY('a','d') > JSON_ARRAY('a','b');
+-------------------------------------------+
| JSON_ARRAY('a','d') > JSON_ARRAY('a','b') |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_ARRAY('1','d') > JSON_ARRAY('a','b');
+-------------------------------------------+
| JSON_ARRAY('1','d') > JSON_ARRAY('a','b') |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.00 sec)
非JSON格式数据与JSON数据转换
| other type | CAST(other type AS JSON) | CAST(JSON AS other type) | | JSON | 不变? ? ? ?????????????????? | 不变 | utf8字符类型(utf8mb4,utf8,ascii) | 字符串被解析为JSON值 | JSON被序列化成utf8mb4字符串 | | 其他字符类型 | 其他字符编码被隐式转换为utf8mb4，并按utf8字符类型进处理 | JSON值被序列化为utf8mb4字符串，然后再被转换为其他字符编码。结果可能没有意义。 | | NULL | 结果为JSON类型的null值 | 不适用 |
四、JSON索引
json列不支持创建索引，可根据json数据中某个key值创建虚拟列索引，创建方式为：
column_name <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]
GENERATED ALWAYS：通俗的讲这一列是通过其他列计算所得
mysql?5.7之后，支持Virtual Generated Column和Stored Generated Column两种Generated Column；前者只将Generated Column保存在数据字典中（表的元数据），后者会将Generated Column持久化到磁盘上。默认是Virtual Column。
可以在建表的时候直接创建虚拟列索引，也可以先建虚拟列，然后建索引，跟平常创建索引方式一样。
create table json_test(id int, content json default null, name varchar(64) GENERATED ALWAYS AS (`content`-> '$.name') NOT NULL);
alter table json_test add index idx_name(`name`);
创建索引之前查询走的是全表查询
mysql> desc json_test;
+---------+-------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------------------+
| id | int | YES | | NULL | |
| content | json | YES | | NULL | |
| name | varchar(64) | NO | | NULL | VIRTUAL GENERATED |
+---------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)
mysql> explain select * from json_test where content->'$.name' = 'test';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | json_test | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from json_test where name = 'test';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | json_test | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
创建索引之后走的是索引查询
mysql> desc json_test;
+---------+-------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------------------+
| id | int | YES | | NULL | |
| content | json | YES | | NULL | |
| name | varchar(64) | NO | MUL | NULL | VIRTUAL GENERATED |
+---------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)
mysql> explain select * from json_test where content->'$.name' = 'test';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | json_test | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from json_test where name = 'test';
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | json_test | NULL | ref | idx_name | idx_name | 258 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
|