昨天上线后通过系统报警发现了一个bug,于是紧急进行了回滚操作,但是期间有用户下单,数据产生了影响,因此需要排查影响了哪些订单,并对数据进行修复。
由于bug导致了订单表的customer_extra_info字段的hasfreightinsurance误更新成了“是”,因此需要查询回滚前一共有多少被误更新为“是”的订单,如下图:
于是查看订单表中customer_extra_info字段类型发现是json类型的
查询资料发现mysql5.7以后提供了一种新的字段格式-json。
对json类型的数据mysql提供了相关的查询操作。
先给出查询sql,后面在介绍mysql对json类型字段的查询操作
select * from ( select id, customer_extra_info -> '$.hasfreightinsurance' as insurance from oms_order_list where project_id = 1 and update_time > '2022-04-15 16:30:17' ) t where json_contains ( insurance, json_object ( "value", "是" ) )
1、使用 json字段名->’$.json属性’ 进行查询条件
select id, customer_extra_info from oms_order_list where project_id = 1 and update_time > '2022-04-15 16:30:17' and customer_extra_info -> '$.maxclaimamount'=10
查询结果如下:
2、关联表查询
json字段也支持关联表的查询,这里只写出使用方法,不做实例展示。其中deptleaderid和id分别是dept,dept_leader两个表中的关联字段。
select * from dept,dept_leader where dept.json_value->'$.deptleaderid'=dept_leader.json_value->'$.id' ;
写到这里大家都发现了,我们查询的json都是整条json数据,这样看起来不是很方便,那么如果我们只想看json中的某个字段怎么办?
这样就引入了我们的第一个函数:json_extract(字段名,json字段名)
在详细介绍用法之前我们可以看看官网的函数介绍:
咱们可以看到官网介绍json_extract()这个函数很详细:return data from json document
从json中返回字段
1、函数 json_extract():从json中返回想要的字段
用法:json_extract(字段名,$.json字段名)
实例:
select id, json_extract ( customer_extra_info, '$.hasfreightinsurance' ) as hasfreightinsurance from oms_order_list where project_id = 1 and update_time > '2022-04-15 16:30:17'
查询结果如下:
2、函数json_contains():json格式数据是否在字段中包含特定对象
用法: json_contains(target, candidate[, path])
实例:
select id, customer_extra_info from oms_order_list where project_id = 1 and update_time > '2022-04-15 16:30:17' and json_contains ( customer_extra_info, json_object ( "maxclaimamount", 10 ) )
查询结果如下:
3、函数json_object():将一个键值对列表转换成json对象
比如我们想查询某个对象里面的值等于多少
我们可以看到hasfreightinsurance中还有一个对象,里面还有name和value两个属性字段,那么我们应该怎么查询value=否的订单呢。
用法:json_object([key, val[, key, val] …])
实例:
select * from ( select id, customer_extra_info -> '$.hasfreightinsurance' as insurance from oms_order_list where project_id = 1 and update_time > '2022-04-15 16:30:17' ) t where json_contains ( insurance, json_object ( "value", "否" ) )
查询结果如下:
4、函数json_array():创建json数组
用法:json_array([val[, val] …])
实例:我们要查询deptname包含1的数据
select id, customer_extra_info from oms_order_list where project_id = 1 and update_time > '2022-04-15 16:30:17' and json_contains ( customer_extra_info -> '$.deptname', json_array ( "1" ) )
查询结果如下:
5、函数json_type():查询某个json字段属性类型
用法:json_type(json_val)
事例:比如我们想查询deptname的字段属性是什么
select id, customer_extra_info -> '$.deptname', json_type ( customer_extra_info -> '$.deptname' ), customer_extra_info -> '$.hasfreightinsurance', json_type ( customer_extra_info -> '$.hasfreightinsurance' ) from oms_order_list where project_id = 1 and update_time > '2022-04-15 16:30:17'
查询结果如下:
6、函数json_extract() :从json文档返回数据
这也是我们开发中会经常用到的一个函数
select * from oms_order_list where project_id = 1 and update_time > '2022-04-15 16:30:17' and json_contains ( json_extract ( customer_extra_info, '$.hasfreightinsurance' ), json_object ( "value", "否" ) )
查询结果如下:
7、函数json_keys() :json文档中的键数组
用法:json_keys(json_value)
实例:比如我们想查询json格式数据中的所有key
select id, json_keys ( customer_extra_info ) from oms_order_list where project_id = 1 and update_time > '2022-04-15 16:30:17'
查询结果如下:
接下来的3种函数都是新增数据类型的:
json_set(json_doc, path, val[, path, val] …)
json_insert(json_doc, path, val[, path, val] …)
json_replace(json_doc, path, val[, path, val] …)
1、函数json_set() :将数据插入json格式中,有key则替换,无key则新增
这也是我们开发过程中经常会用到的一个函数
用法:json_set(json_doc, path, val[, path, val] …)
实例:比如我们想针对id=2的数据新增一组:newdata:新增的数据,修改deptname为新增的部门1
sql语句如下:
update dept set json_value=json_set('{"deptname": "部门2", "deptid": "2", "deptleaderid": "4"}','$.deptname','新增的部门1','$.newdata','新增的数据') where id=2; select * from dept where id =2
结果:
注意:json_doc如果不带这个单元格之前的值,之前的值是会被新值覆盖的,比如我们如果更新的语句换成:
update dept set json_value=json_set('{"a":"1","b":"2"}','$.deptname','新增的部门1','$.newdata','新增的数据') where id=2
我们可以看到这里json_doc是{“a”:“1”,“b”:“2”},这样的话会把之前的单元格值覆盖后再新增/覆盖这个单元格字段
结果:
2、函数json_insert():插入值(往json中插入新值,但不替换已经存在的旧值)
用法:json_insert(json_doc, path, val[, path, val] …)
实例:
update dept set json_value=json_insert('{"a": "1", "b": "2"}', '$.deptname', '新增的部门2','$.newdata2','新增的数据2') where id=2
结果:
我们可以看到由于json_doc变化将之前的值覆盖了,新增了deptname和newdata2.
如果我们再执行以下刚才的那个sql,只是换了value,我们会看到里面的key值不会发生变化。
因为这个函数只负责往json中插入新值,但不替换已经存在的旧值。
3、函数json_replace()
用法:json_replace(json_doc, path, val[, path, val] …)
用例:
如果我们要更新id=2数据中newdata2的值为:更新的数据2
sql语句如下:
update dept set json_value=json_replace('{"a": "1", "b": "2", "deptname": "新增的部门2", "newdata2": "新增的数据2"}', '$.newdata2', '更新的数据2') where id =2; select * from dept where id =2
结果:
4、函数json_remove() :从json文档中删除数据
用法:json_remove(json_doc, path[, path] …)
举例:删除key为a的字段。
update dept set json_value=json_remove('{"a": "1", "b": "2", "deptname": "新增的部门2", "newdata2": "更新的数据2"}','$.a') where id =2;
结果:
5、函数json_search() :用于在json格式中查询并返回符合条件的节点
这是一个非常强大的函数
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:mmqy2019@163.com进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容。