mysql中json_replace函数的使用?通过json_replace对json对象的值进行替换

需求描述:

在看mysql中关于json的内容,通过json_replace函数可以实现对json值的替换,

在此记录下.

操作过程:

①查看带有json数据类型的表

mysql> select json_replace(data,'$.age',54,'$.tel',15046464563) from tab_json  where id = 1; #使用json_replace进行查询处理,对已经存在的key值进行替换
+-------------------------------------------------------+
| json_replace(data,'$.age',54,'$.tel',15046464563)     |
+-------------------------------------------------------+
| {"age": 54, "tel": 15046464563, "passcode": "654567"} |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_replace(data,'$.age',54,'$.tel',15046464563,'$.sex',"male") from tab_json  where id = 1; #对于不存在key,是没有增加新的key-value值的
+------------------------------------------------------------------+
| json_replace(data,'$.age',54,'$.tel',15046464563,'$.sex',"male") |
+------------------------------------------------------------------+
| {"age": 54, "tel": 15046464563, "passcode": "654567"}            |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> update tab_json set data = json_replace(data,'$.age',54,'$.tel',15046464563) where id = 1; #对id=1的行进行更新操作,更新之后,age和tel的值发生了变化
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tab_json;
+----+---------------------------------------------------------------------------------------+
| id | data                                                                                  |
+----+---------------------------------------------------------------------------------------+
|  1 | {"age": 54, "tel": 15046464563, "passcode": "654567"}                                 |
|  2 | {"age": "33", "tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} |
+----+---------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> update tab_json set data = json_replace(data,'$.age',54,'$.tel',15046464563,'$.sex',"male") where id = 1; 对id=1的行进行更新操作,更新之后,age和tel的值发生了变化,但是并没有增加新的key
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from tab_json;
+----+---------------------------------------------------------------------------------------+
| id | data                                                                                  |
+----+---------------------------------------------------------------------------------------+
|  1 | {"age": 54, "tel": 15046464563, "passcode": "654567"}                                 |
|  2 | {"age": "33", "tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} |
+----+---------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

备注:所以json_replace的主要作用是替换,如果存在key就替换对应的值,如果不存在key也不会增加,与json_insert的使用有区别.

以上就是土嘎嘎小编为大家整理的mysql中json_replace函数的使用?通过json_replace对json对象的值进行替换相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！