MySQL5.7 JSON类型列创建索引查询一例
发布时间：2022-04-06 11:05:20 所属栏目：MySql教程 来源：互联网
导读：创建json类型的表test: mysql CREATE TABLE test(data JSON); Query OK, 0 rows affected (0.47 sec) mysql insert into test values({name:abc,sex:nan,area:[1,2]}); Query OK, 1 row affected (0.39 sec) mysql insert into test values({name:abc,sex:
|
创建json类型的表test: mysql> CREATE TABLE test(data JSON); Query OK, 0 rows affected (0.47 sec) mysql> insert into test values('{"name":"abc","sex":"nan","area":["1","2"]}'); Query OK, 1 row affected (0.39 sec) mysql> insert into test values('{"name":"abc","sex":"nan","area":["2","3"]}'); Query OK, 1 row affected (0.39 sec) mysql> insert into test values('{"name":"abc","sex":"nan","area":["3","4"]}'); Query OK, 1 row affected (0.39 sec) mysql> select json_type(data) from test; +-----------------+ | json_type(data) | +-----------------+ | OBJECT | +-----------------+ 1 row in set (0.15 sec) mysql> select * from test; +---------------------------------------------------+ | data | +---------------------------------------------------+ | {"sex": "nan", "area": ["1", "2"], "name": "abc"} | +---------------------------------------------------+ 1 row in set (0.10 sec) mysql> select json_extract(data, '$.name' ) from test; +-------------------------------+ | json_extract(data, '$.name' ) | +-------------------------------+ | "abc" | +-------------------------------+ 1 row in set (0.00 sec) mysql> select json_extract(data, '$.sex' ) from test; +------------------------------+ | json_extract(data, '$.sex' ) | +------------------------------+ | "nan" | +------------------------------+ 1 row in set (0.00 sec) mysql> select json_extract(data, '$.area' ) from test; +-------------------------------+ | json_extract(data, '$.area' ) | +-------------------------------+ | ["1", "2"] | +-------------------------------+ 1 row in set (0.00 sec) 在data列上，对"area"建立虚拟列 mysql> ALTER TABLE test ADD data_idx varchar(128) GENERATED ALWAYS AS (json_extract(data,'$.area')) VIRTUAL; Query OK, 0 rows affected (0.93 sec) Records: 0 Duplicates: 0 Warnings: 0 如果要在JSON列上进行检索，需要对检索的key创建虚拟列，然后再虚拟列上创建索引。 mysql> alter table test add index idx_data(data_idx); Query OK, 0 rows affected (0.67 sec) Records: 0 Duplicates: 0 Warnings: 0 where条件需要使用虚拟列来进行检索,执行计划如下: mysql> explain select * from test where data_idx='["3", "4"]'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test | NULL | ref | idx_data | idx_data | 387 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.04 sec) （编辑：临夏站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐