創(chuàng)建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列上，對(duì)"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列上進(jìn)行檢索，需要對(duì)檢索的key創(chuàng)建虛擬列，然后再虛擬列上創(chuàng)建索引。
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條件需要使用虛擬列來(lái)進(jìn)行檢索,執(zhí)行計(jì)劃如下:
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)


發(fā)現(xiàn)走了索引

本文題目：MySQL5.7JSON類型列創(chuàng)建索引查詢一例
網(wǎng)頁(yè)地址：http://chinadenli.net/article42/ggjphc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、標(biāo)簽優(yōu)化、做網(wǎng)站、企業(yè)建站、網(wǎng)站設(shè)計(jì)公司、網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)