mysql中json_remove函数的使用?
需求描述:
今天看json记录,可以通过json_remove函数对一个key或多个key从个json记录中去掉.
操作过程:
1.查看一个已经存在的json表
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)
2.通过json_remove函数,将json记录中的key去掉
mysql> select json_remove(data,'$.age') from tab_json; #查询时,去掉某些的key +--------------------------------------------------------------------------+ | json_remove(data,'$.age') | +--------------------------------------------------------------------------+ | {"tel": 15046464563, "passcode": "654567"} | | {"tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} | +--------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select json_remove(data,'$.age','$.name') from tab_json; #查询时,去掉某些的key +---------------------------------------------------------+ | json_remove(data,'$.age','$.name') | +---------------------------------------------------------+ | {"tel": 15046464563, "passcode": "654567"} | | {"tel": 189776542, "olds": "12", "address": "Hangzhou"} | +---------------------------------------------------------+ 2 rows in set (0.00 sec)
通过update将json记录中的值进行替换
mysql> update tab_json set data = json_remove(data,'$.age'); #通过update已经将json记录进行了修改
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from tab_json;
+----+--------------------------------------------------------------------------+
| id | data |
+----+--------------------------------------------------------------------------+
| 1 | {"tel": 15046464563, "passcode": "654567"} |
| 2 | {"tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} |
+----+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)
备注:json_remove函数的作用就是查询时,将其中的某些key不显示,如果要更新源表,需要使用update操作.
文档创建时间:2018年6月6日10:08:09
