创建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)
发现走了索引
