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

Next, let's learn about the newly added JSON data type characteristics of mysql5.7. I believe you will benefit a lot after reading it. The text is not much in essence. I hope that the newly added JSON data type features of mysql5.7 is what you want.

I. json structure

Create a test table

CREATE TABLE `roomle` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `tags` json NOT NULL, `tags` json NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Analysis: the fields category and tags in the article table are of json type

Fill in the test data

INSERT INTO `Secretle` VALUES (1dint'{\ "id\": 1,\ "name\":\ "php\"}','[\ "php\",\ "mysql\",\ "linux\",\ "nginx\",\ "redis\",\ "memcache\",\ "mongodb\"]'), (2) {\ "id\": 2,\ "name\":\ "java\"}','[\ "java\",\ "mysql\" \ "oracel\",\ "linux\",\ "nginx\",\ "redis\",\ "memcache\",\ "mongodb\"]'), (3)'{\ "id\":\ "3\",\ "name\":\ "c #\"}','[\ "c\",\ "C++\",\ "OS\",\ "linux\",\ "unix\",\ "IBM\"]')

Overall preview

2. Json query

Select id,json_extract (category,'$.name') as name from test.article;# extracts the information in the json field

# column- > path to access the element category- >'$.name'in json

Select id,category- >'$.name'as name from test.article;# extracts the information in the json field (access the element category- >'$.name'in json)

Select id,json_unquote (json_extract (category,'$.name')) as name from test.article;# extracts the information in the json field, and json_unqoute removes the double quotation marks

Select id,json_unquote (category- >'$.name') as name from test.article;# extracts the information in the json field, and json_unqoute removes the double quotation marks

Select id,category- > >'$.name'as name from test.article

Select * from test.article where category=' {"id": 1, "name": "php"}'; # json is different from a string and cannot be compared as a string

Select * from test.article where category=cast ('{"id": 1, "name": "php"}'as JSON); # convert a string to JSON form through CAST

Select * from test.article where category- >'$.name'= 'java'

Select * from test.article where category- > >'$.name'= 'java'

Element search in # JSON strictly distinguishes variable types, such as integers and strings.

Select * from test.article where category- >'$.id'= '2character string

Select * from test.article where category- >'$.id'= 2 × × ×

Select * from test.article where category- >'$.id'='3 'character string

Select * from test.article where json_extract (category,'$.id') ='3 characters cross # string

Select * from test.article where json_contains (category,'2','$.id'); # Integer

Select * from test.article where json_contains (category,' "3",'$.id'); # character string

Select * from test.article where json_contains (tags,' "linux"'); # character string

2. Query fields in json format

Mysql > select jsn_extract (data,'. Name'), jsnextract (data,'.address') from user

+-+

| | jsn_extract (data,' .name') | jsnextract (data,'.address') | |

+-+

| | "David" | "Shangahai" |

| | "Amy" | NULL |

+-+

2 rows in set (0.00 sec)

3. Create an index for a key field in json format. First create a virtual column, and then create an index on the changed virtual column.

Mysql > ALTER TABLE user ADD user_name varchar

-> GENERATED ALWAYS AS (jsn_extract (data,'$.name')) VIRTUAL

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > select user_name from user

+-+

| | user_name |

+-+

| | "Amy" |

| | "David" |

+-+

2 rows in set (0.00 sec)

Mysql > alter table user add index idx_username (user_name)

Query OK, 2 rows affected (0.01sec)

Records: 2 Duplicates: 0 Warnings: 0

4. Then index the json-specific column through the virtual column name:

Mysql > explain select * from user where user_name=' "Amy"\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: user

Partitions: NULL

Type: ref

Possible_keys: idx_username

Key: idx_username

Key_len: 131

Ref: const

Rows: 1

Filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

III. Json updates

Update JSON

If it is an entire json update, it is similar to that at insert time.

Mysql > UPDATE lnmp SET tags ='[1,3,4] 'WHERE id = 1

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > SELECT * FROM lnmp +-+-+ | id | category | tags | +-+-- +-+ | 1 | {"id": 1 "name": "lnmp.cn"} | [1,3,4] | | 2 | {"id": 2, "name": "php.net"} | [1,3,5] | +-+-- + 2 rows in set (0.00 sec)

However, if you want to update the elements under JSON, MySQL does not support the form column- > path

Mysql > UPDATE lnmp SET category- >'$.name'= 'lnmp', tags- >' $[0]'= 2 WHERE id = 1

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'- >'$.name'= 'lnmp', tags- >' $[0]'= 2 WHERE id = 1'at line 1

The following functions may be used

JSON_INSERT () inserts a new value, but does not overwrite an existing value

Mysql > UPDATE lnmp SET category = JSON_INSERT (category,'$.name', 'lnmp',' $.url', 'www.lnmp.cn') WHERE id = 1

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > SELECT * FROM lnmp +-id | category | tags | +-+ -- +-+ | 1 | {"id": 1 "url": "www.lnmp.cn", "name": "lnmp.cn"} | [1,3,4] | | 2 | {"id": 2, "name": "php.net"} | [1,3 " 5] | +-+-- +-+ 2 rows in set (0.00 sec)

You can see that name has not been modified, but the new element url has been added

JSON_SET () inserts a new value and overwrites the existing value

Mysql > UPDATE lnmp SET category = JSON_SET (category,'$.host', 'www.lnmp.cn',' $.url', 'http://www.lnmp.cn') WHERE id = 1

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > SELECT * FROM lnmp +-+-+ | id | category | | tags | + -+ | 1 | {"id": 1 "url": "http://www.lnmp.cn"," host ":" www.lnmp.cn "," name ":" lnmp.cn "} | [1,3,4] | | 2 | {" id ": 2," name ":" php.net "} | [1,3" 5] | +-+ 2 rows in set (0.00 sec)

You can see that host has been inserted and url has been modified

JSON_REPLACE () replaces only existing values

Mysql > UPDATE lnmp SET category = JSON_REPLACE (category,'$.name', 'php',' $.url', 'http://www.php.net') WHERE id = 2

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > SELECT * FROM lnmp +-+-+ | id | category | | tags | + -+ | 1 | {"id": 1 "url": "http://www.lnmp.cn"," host ":" www.lnmp.cn "," name ":" lnmp.cn "} | [1,3,4] | | 2 | {" id ": 2," name ":" php "} | [1,3" 5] | +-+ 2 rows in set (0.00 sec)

You can see that name has been replaced and that url does not exist to be ignored.

JSON_REMOVE () deletes the JSON element

Mysql > UPDATE lnmp SET category = JSON_REMOVE (category,'$.url','$.host') WHERE id = 1

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > SELECT * FROM lnmp +-+-+ | id | category | tags | +-+-- +-+ | 1 | {"id": 1 "name": "lnmp.cn"} | [1,3,4] | | 2 | {"id": 2, "name": "php"} | [1,3,5] | +-+-- + 2 rows in set (0.00 sec)

For more functions, please see: http://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html

The performance of MySQL JSON in PHP

Although it is a JSON type in MySQL, what is actually returned in the PHP application is a string in JSON format

Array (2) {[0] = > array (3) {["id"] = > string (1) "1" ["category"] = > string (28) "{" id ": 1," name ":" lnmp.cn "}" ["tags"] = > string (9) "[1,3" 4] "} [1] = > array (3) {[" id "] = > string (1)" 2 "[" category "] = > string (24)" {"id": 2, "name": "php"} "[" tags "] = > string (9)" [1,3,5] "}

After reading this article on mysql5.7 's newly added JSON data type features, many readers will certainly want to know more about it. For more industry information, you can follow our industry information section.

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.