Mysql Json
筛选时可以使用 de >JSON_SEARCH(de >json_docde>, de >one_or_allde>, de >search_strde>[, de >escape_charde>[, de >pathde>] ...]) 来完成Where条件的限定de> 但是每次执行时都会进行全表扫描
实例： CREATE TABLE json_test1 ( id int(11) NOT NULL AUTO_INCREMENT, person_desc json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB
insert json_test1(person_desc) values ("{"type1": ["value1", "value2"], "type2": ["value11", "value21", "value31"]}"); insert json_test1(person_desc) values ("{"type1": ["value3", "value4"], "type2": ["value14", "value24", "value34"]}");
表中一共两条记录
mysql> select * from json_test1;
+----+-----------------------------------------------------------------------------+
| id | person_desc |
+----+-----------------------------------------------------------------------------+
| 1 | {"type1": ["value1", "value2"], "type2": ["value11", "value21", "value31"]} |
| 2 | {"type1": ["value3", "value4"], "type2": ["value14", "value24", "value34"]} |
+----+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
根据json筛选条件，能够找到value2的只有id=1的记录
mysql> select * from json_test1 where JSON_SEARCH(person_desc, "one","value2") is not null;
+----+-----------------------------------------------------------------------------+
| id | person_desc |
+----+-----------------------------------------------------------------------------+
| 1 | {"type1": ["value1", "value2"], "type2": ["value11", "value21", "value31"]} |
+----+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看执行计划，进行全表扫描
mysql> explain select * from json_test1 where JSON_SEARCH(person_desc, "one","value2") is not null;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | json_test1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
现在想利用mysql虚拟列方式建立虚拟列，增加索引，在应用中利用索引提升效率 一种直接但不通用的方式：对列person_desc 函数映射到$.type1[1] 形成虚拟列
mysql> alter table json_test1 add col1 varchar(100) generated always as (person_desc->"$.type1[1]");
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table json_test1 add index idx_col1(col1);
Query OK, 0 rows affected (0.01 sec)
mysql> desc json_test1;
+-------------+--------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| person_desc | json | YES | | NULL | |
| col1 | varchar(100) | YES | MUL | NULL | VIRTUAL GENERATED |
+-------------+--------------+------+-----+---------+-------------------+
根据虚拟列查询，因为建立的索引，所以不是全表扫描
mysql> explain select * from json_test1 where col1="value2";
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | json_test1 | NULL | ref | idx_col1 | idx_col1 | 103 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
问题： 根据业务场景需要对type1下所有的取值[value1,value2,value3,value4] 映射到同一虚拟列 vcolx中，建立索引index_vcolx，利用index_vcolx进行查询，上边例子只能将type1下的部分取值映射到一个虚拟列。改问题可以化归为数据表json字段为ARRAY类型中的元素在虚拟列的索引问题，利用函数索引的角度，需要找到映射函数将 json字段映射为每一个数组中的元素，按照目前的虚拟列创建方式没有在MYSQL中找到这样一种创建方式（本人认为本质上这中做法违背经典数据库设计原理）。
那么，将type1的整个值数组作为json类型创建索引，测试否会有索引带来的收益：
mysql> alter table json_test1 add col2 json generated always as (person_desc->"$.type1");
Query OK, 0 rows affected (0.02 sec)
mysql> alter table json_test1 add index idx_col2(col2);
ERROR 3152 (42000): JSON column "col2" cannot be used in key specification.
可以看到可以创建json虚拟列col2，但是却不能创建json列的索引，也就是说json字段类型不能作为索引字段。
结论：对于mysql5.7.X 提供的 json类型，无法满足业务场景需要对其中存在的数组部分数据的每一个元素进行索引的要求，因为Json数组是无模式的而无法映射为虚拟列。Mysql json能够映射虚拟列的作用范围是 Json对象的某一个Key对应的所有数据记录，理论上可以在数据库设计是就将其设计成为 schema中的一列。
- 上一篇： Mysql 8.0 新增特性
- 下一篇： MySQL json 类型操作快速入门
