MySQL database, how to modify the value of the JSON data type? JSON field values are modified by the Json_set function?

Requirements Description :

Today I look at the problem of storing JSON data types in MySQL, and for modifying the JSON data,

Under this record.

Operation Process :

1. Create a table with JSON data types, insert the underlying data

Mysql> CREATE TABLE Tab_json (ID int not NULL auto_increment primary key,data JSON); Query OK, 0 rows affected (0.03 sec) mysql> insert into Tab_json values (null, ' {' name ': ' Mike ', ' address ': ' Beijing ', ' tel ' : 13249872314} '); Query OK, 1 row affected (0.01 sec) mysql> insert into Tab_json values (null, ' {' name ': ' David ', ' address ': ' Shanghai ', ' tel ": 189776542} '); Query OK, 1 row affected (0.01 sec) mysql> Select * from tab_json;+----+---------------------------------------------- --------------+| ID | Data                                                       |+----+------------------------------------------------------------+|  1 | {"Tel": 13249872314, "name": "Mike", "Address": "Beijing"} | |  2 | {"Tel": 189776542, "name": "David", "Address": "Shanghai"} |+----+-------------------------------------------------- ----------+2 rows in Set (0.00 sec)

2. Use the Json_set function to modify the value of the data field

mysql> Update Tab_json Set data = Json_set (data, "$.address", "Guangzhou") where id = 1;The key value of the address of the line #对id = 1 is modified.
Query OK, 1 row affected (0.26 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select * from Tab_json;
+----+--------------------------------------------------------------+
| ID | Data |
+----+--------------------------------------------------------------+
| 1 | {"Tel": 13249872314, "name": "Mike", "Address": "Guangzhou"} |
| 2 | {"Tel": 189776542, "name": "David", "Address": "Shanghai"} |
+----+--------------------------------------------------------------+
2 rows in Set (0.00 sec)

mysql> Update Tab_json Set data = Json_set (data, "$.address", "Shenzhen");
Query OK, 2 rows affected (0.02 sec)
Rows matched:2 Changed:2 warnings:0

Mysql> select * from Tab_json;
+----+-------------------------------------------------------------+
| ID | Data |
+----+-------------------------------------------------------------+
| 1 | {"Tel": 13249872314, "name": "Mike", "Address": "Shenzhen"} |
| 2 | {"Tel": 189776542, "name": "David", "Address": "Shenzhen"} |
+----+-------------------------------------------------------------+
2 rows in Set (0.00 sec)

mysql> Update Tab_json Set data = Json_set (data, "$.address", "Hangzhou") where id = 2;#对id为2的address键值进行修改
Query OK, 1 row affected (0.03 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select * from Tab_json;
+----+-------------------------------------------------------------+
| ID | Data |
+----+-------------------------------------------------------------+
| 1 | {"Tel": 13249872314, "name": "Mike", "Address": "Shenzhen"} |
| 2 | {"Tel": 189776542, "name": "David", "Address": "Hangzhou"} |
+----+-------------------------------------------------------------+
2 rows in Set (0.00 sec)

mysql> Update Tab_json Set data = Json_set (data, "$.passcode", "654567") where id = 1;#对id为1的passcode字段进行修改, it was found that without this key value, a key-value pair was added.
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select * from Tab_json;
+----+-----------------------------------------------------------------------------------+
| ID | Data |
+----+-----------------------------------------------------------------------------------+
| 1 | {"Tel": 13249872314, "name": "Mike", "Address": "Shenzhen","passcode": "654567"}|
| 2 | {"Tel": 189776542, "name": "David", "Address": "Hangzhou"} |
+----+-----------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)

mysql> Update Tab_json Set data = Json_set (data, "$".Olds"," "n") where id = 2;
Query OK, 1 row affected (0.17 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select * from Tab_json;
+----+-----------------------------------------------------------------------------------+
| ID | Data |
+----+-----------------------------------------------------------------------------------+
| 1 | {"Tel": 13249872314, "name": "Mike", "Address": "Shenzhen", "passcode": "654567"} |
| 2 | {"Tel": 189776542, "name": "David", "Olds":", "Address": "Hangzhou"} |
+----+-----------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)

mysql> Update Tab_jsonSet data = Json_set(Data, "$.age", "33");
Query OK, 2 rows affected (0.02 sec)
Rows matched:2 Changed:2 warnings:0

Mysql> select * from Tab_json;
+----+------------------------------------------------------------------------------------------------+
| ID | Data |
+----+------------------------------------------------------------------------------------------------+
| 1 | {"Age": "$", "tel": 13249872314, "name": "Mike", "Address": "Shenzhen", "passcode": "654567"} |
| 2 | {"Age": "$", "tel": 189776542, "name": "David", "Olds": "A", "Address": "Hangzhou"} |
+----+------------------------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)

Note: The above is through the json_set of the JSON field to modify the key value, if there is a replacement, if there is no key value, increase the key value pair.

Document creation Time: June 5, 2018 21:59:18

MySQL database, how to modify the value of the JSON data type? JSON field values are modified by the Json_set function?