需求描述:
在看mysql中关于json的内容,通过json_replace函数可以实现对json值的替换,
在此记录下.
操作过程:
1.查看带有json数据类型的表
mysql> select * from tab_json; +----+---------------------------------------------------------------------------------------+ | id | data | +----+---------------------------------------------------------------------------------------+ | 1 | {"age": "33", "tel": 13249872314, "passcode": "654567"} | | 2 | {"age": "33", "tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} | +----+---------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
2.使用json_replace函数对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)
3.通过update语句对json中的值进行替换操作
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的使用有区别.
json_insert函数的使用:https://www.cnblogs.com/chuanzhang053/p/9142212.html
文档创建时间:2018年6月6日09:48:10
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:mysql中json_replace函数的使用?通过json_replace对json对象的值进行替换 - Python技术站