以下内容首发于我的个人博客网站:
http://riun.xyz
使用
在mysql5.7之后增加了对json数据的操作
具体json函数使用方法参考: https://blog.csdn.net/qq_21187515/article/details/90760337
现若有一表:test_common_tb
表中有一字段: callbackdata
text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘回调数据’,
和另一字段: statusid
int(11) DEFAULT ‘1’ COMMENT ‘状态: 0-处理成功 1-处理中 2-处理失败’,
他们的逻辑是这样的:statusid
为0的时候,callbackdata
字段必定有值,其他情况不确定是否有值。callbackdata
字段储存json数据。
callbackdata
字段有值时的样例:
{
"productId": "P002",
"bizId": "2001",
"listingId": "1107_03",
"flowId": "srgtwery5ergsdrfg",
"auditResult": 0,
"auditReason": "F01;",
"statusId": 0,
"statusDesc": "success"
}
现在若要查询此字段中 auditReason 包含 F01 的数据,sql应该这样写:
正确示例:
SELECT * FROM `test_common_tb`
where statusid = 0 and callbackdata->'$.auditReason' like '%F01%'
在实际查询json数据时,如果不能保证所有记录的此字段都储存合规json数据,那么就有可能查询出错,例如上述字段callbackdata
可能存在值也可能不存在,所以不能直接这样查询:
错误示例:
SELECT * FROM `test_common_tb`
where callbackdata->'$.auditReason' like '%F01%'
这样会出现错误:
The JSON binary value contains invalid data.
因为当字段为空时mysql会认为此字段不是合规的json数据,若某记录中的此字段值不是合规的json数据,使用callbackdata->'$.auditReason' like '%F01%'
去从这些记录中条件查询就会报错。因此要先筛选出所有callbackdata
字段为合规的json数据的记录,再去执行like
的条件查询。 我们上述的sql是依靠statusid
去确定callbackdata
字段存有合规的json数据
也可以使用如下sql语句查出所有字段为合规的json数据的记录:
select id , callbackdata, statusid from test_common_tb
where json_valid(callbackdata) = 1
json_valid(callbackdata) = 0
表示不是合规的json数据 ,json_valid(callbackdata) = 1
表示是合规的json数据
另外当json数据中存在"userName": "\xe7\x8e\x8b\xe5\xb0\x8f\xe4\xba\x8c",
这种乱码时也会被认为不是合规的json数据。
很多时候我们并没有这样一个statusid去确保另一个字段的值,因此要想根据储存json字段的某Key的值去查询符合规则的记录,就要像下面这样查:
SELECT * FROM test_common_tb
WHERE json_valid ( callbackdata ) = 1 and callbackdata -> "$.productId" = "P001"
上面查询语句中where后面的条件顺序不能改变。
若我们储存的json数据存在多层关系时,想要查询更深层的key,就需要将父级元素的Key全部写上:
{
"productId": "P002",
"bizId": "2001",
"listingId": "1107_03",
"flowId": "sdfgsdfggds",
"auditResult": 0,
"auditReason": "F01;",
"statusId": 0,
"statusDesc": "success",
"object": {
"productId": "34545",
"bizId": "1002",
"listingId": "1234234ersf",
"idNumber": "346235tw34563453",
"idType": "100",
"mobile": "345346",
"userId": "zfdgsd",
"cifNo": "3453453",
"cifType": "234",
"cstLevelCd": "432",
"flowId": "sdfgsdfggds",
"flowCount": 1
}
}
若callbackdata
字段存在这样的数据,而我现在要查询idType
为100的记录,那么我的查询语句应该是:
SELECT * FROM `test_common_tb`
where json_valid ( callbackdata ) = 1 and callbackdata->'$.object.idType' = '100'
只写callbackdata->'$.idType'='100'
它只会在第一级key去寻找。
总结
查询出column符合json规范的记录:
SELECT * FROM `table_name`
WHERE json_valid(column_name) = 1
1为合规,0为不合规。
通用的查询储存json数据的字段中key=value的记录:
SELECT * FROM table_name
WHERE json_valid ( column ) = 1 and column -> "$.key" = "value"
查询多级key时,需要将父级key全部写上
SELECT * FROM `talbe_name`
WHERE json_valid ( column ) = 1 and column_name->'$.key.key' = 'value'