Shulou(Shulou.com)06/01 Report--

Create a table of type 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)

On the data column, create a virtual column for "area"

Mysql > ALTER TABLE test ADD data_idx varchar 128GENERATED ALWAYS AS (json_extract (data,'$.area')) VIRTUAL

Query OK, 0 rows affected (0.93 sec)

Records: 0 Duplicates: 0 Warnings: 0

If you want to retrieve on the JSON column, you need to create a virtual column on the retrieved key, and then create an index on the virtual column.

Mysql > alter table test add index idx_data (data_idx)

Query OK, 0 rows affected (0.67 sec)

Records: 0 Duplicates: 0 Warnings: 0

The where condition needs to be retrieved using virtual columns, and the execution plan is as follows:

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)

It is found that the index is gone.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.