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

Preface

Because of the needs of the project, the stored fields are stored in JSON format, and the queried values are transferred to the corresponding bean for processing through jackson in the project, which is not simple and convenient.

MySQL has supported data in JSON format since version 5. 7, which is easy to operate.

Build a table

When creating a new table, the field type can be directly set to json type, for example, we create a table:

Mysql > CREATE TABLE `test_ user` (`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR (50) NOT NULL, `info` JSON)

The json type field can be NULL

Insert data:

Mysql > INSERT INTO test_user (`name`, `info`) VALUES ('xiaoming',' {"sex": 1, "age": 18, "nick_name": "Xiaomeng"}')

A field of type json must be a valid json string

You can use the JSON_OBJECT () function to construct a json object:

Mysql > INSERT INTO test_user (`name`, `info`) VALUES ('xiaohua', JSON_OBJECT ("sex", 0, "age", 17))

Use the JSON_ARRAY () function to construct the json array:

Mysql > INSERT INTO test_user (`name`, `info`) VALUES ('xiaozhang', JSON_OBJECT ("sex", 1, "age", 19, "tag", JSON_ARRAY (351))

Now look at the data in the test_ user table:

Mysql > select * from test_user +-+-+ | id | name | info | +-+ -+ | 1 | xiaoming | {"age": 18 "sex": 1, "nick_name": "Xiaomeng"} | | 2 | xiaohua | {"age": 17, "sex": 0} | | 3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3,5 " 90]} | +-+-+ 3 rows in set (0.04 sec)

Query

Expression: the object is a json column->'$. Key', the array is json column->'$. Key [index]'

Mysql > select name, info- >'$.Nick _ name', info- >'$.sex', info- >'$.tag [0] 'from test_user +-+ | name | info- >'$.nick _ name' | info- >'$.sex'| info- >'$.tag [0]'| +-- -+ | xiaoming | "Xiaomeng" | 1 | NULL | | xiaohua | NULL | 0 | NULL | | xiaozhang | NULL | 1 | 3 | +-- +-+-+ 3 rows in set (0.04 sec)

Equivalent to: the object is JSON_EXTRACT (json column,'$. Key'), the array is JSON_EXTRACT (json column,'$. Key [index]')

Mysql > select name, JSON_EXTRACT (info,'$Nick _ name'), JSON_EXTRACT (info,'$.sex'), JSON_EXTRACT (info,'$.tag [0]') from test_user +-+-+ | name | JSON_EXTRACT (info '$.Nick _ name') | JSON_EXTRACT (info,' $.sex') | JSON_EXTRACT (info) '$.tag [0]') | +-+-- -- + | xiaoming | "Xiaomeng" | 1 | NULL | | xiaohua | NULL | 0 | NULL | | xiaozhang | NULL | 1 | 3 | +-+ +-- + 3 rows in set (0.04 sec)

However, we can see that "Xiaomeng" above is in double quotation marks. This is not what we want. We can use the JSON_UNQUOTE function to remove the double quotation marks.

Mysql > select name, JSON_UNQUOTE (info- >'$.Nick _ name') from test_user where name='xiaoming' +-+ | name | JSON_UNQUOTE (info- >'$.Nick _ name') | +-+-+ | xiaoming | Xiaomeng | | +-+-- + 1 row in set (0.05 sec) |

You can also use the operator directly-> >

Mysql > select name, info- > >'$.Nick _ name' from test_user where name='xiaoming' +-+-+ | name | info- > >'$.Nick _ name' | +-+-+ | xiaoming | Xiaomeng | +-+- -+ 1 row in set (0.06 sec)

Of course, attributes can also be used as query conditions.

Mysql > select name, info- > >'$.Nick _ name' from test_user where info- >'$.Nick _ name'=' Xiaomeng' +-+-+ | name | info- > >'$.Nick _ name' | +-+-+ | xiaoming | Xiaomeng | +-+- -+ 1 row in set (0.05 sec)

It is worth mentioning that you can quickly query the specified properties of the JSON type through virtual columns.

Create a virtual column:

Mysql > ALTER TABLE `test_ user`ADD `nick_ name` VARCHAR (50) GENERATED ALWAYS AS (info- > >'$Nick _ name') VIRTUAL

Pay attention to the operator-> >

It is the same as a normal type of column query:

Mysql > select name,nick_name from test_user where nick_name=' Xiaomeng'; +-+-+ | name | nick_name | +-+-+ | xiaoming | Xiaomeng | +-+-+ 1 row in set (0.05 sec)

Update

Use JSON_INSERT () to insert new values, but do not overwrite existing values

Mysql > UPDATE test_user SET info = JSON_INSERT (info,'$.sex', 1,'$. Nick _ name', 'floret') where id=2

Take a look at the results.

Mysql > select * from test_user where id=2 +-+ | id | name | info | nick_name | +-+-- -- +-+ | 2 | xiaohua | {"age": 17 "sex": 0, "nick_name": "floret"} | floret | +-- +-+ 1 row in set (0.06 sec)

Use JSON_SET () to insert new values and overwrite existing values

Mysql > UPDATE test_user SET info = JSON_INSERT (info,'$.sex', 0,'$. Nick _ name', 'Xiao Zhang') where id=3

Take a look at the results.

Mysql > select * from test_user where id=3 +-+-+ | id | name | info | nick_name | +-+-- -+ | 3 | xiaozhang | {"age": 19 "sex": 1, "tag": [3,5,90] "nick_name": "Xiao Zhang"} | Xiao Zhang | +-+-+ 1 row in set (0.06 sec)

Use JSON_REPLACE () to replace only existing values

Mysql > UPDATE test_user SET info = JSON_REPLACE (info,'$.sex', 1,'$.tag','[1meme 2jue 3]') where id=2

Take a look at the results.

Mysql > select * from test_user where id=2 +-+ | id | name | info | nick_name | +-+-- -- +-+ | 2 | xiaohua | {"age": 17 "sex": 1, "nick_name": "floret"} | floret | +-- +-+ 1 row in set (0.06 sec)

You can see that tag is not updated.

Delete

Delete the JSON element using JSON_REMOVE ()

Mysql > UPDATE test_user SET info = JSON_REMOVE (info,'$.sex','$.tag') where id=1

Take a look at the results.

Mysql > select * from test_user where id=1 +-+-+ | id | name | info | nick_name | +-+-- -+-+ | 1 | xiaoming | {"age": 18 "nick_name": "Xiaomeng"} | Xiaomeng | +-+-- +-- + 1 row in set (0.05sec)

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.