mysql查询json数据的指定内容

### 使用

在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` 字段有值时的样例：

```json
{
	"productId": "P002",
	"bizId": "2001",
	"listingId": "1107_03",
	"flowId": "srgtwery5ergsdrfg",
	"auditResult": 0,
	"auditReason": "F01;",
	"statusId": 0,
	"statusDesc": "success"
}
```

现在若要查询此字段中 auditReason 包含 F01 的数据，sql应该这样写：

正确示例：

```sql
SELECT * FROM `test_common_tb` 
where statusid = 0 and callbackdata->'$.auditReason' like '%F01%'
```

在实际查询json数据时，如果不能保证所有记录的此字段都储存合规json数据，那么就有可能查询出错，例如上述字段`callbackdata`可能存在值也可能不存在，所以不能直接这样查询：

错误示例：

```sql
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数据的记录：

```sql
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的值去查询符合规则的记录，就要像下面这样查：

```sql
SELECT * FROM test_common_tb
WHERE json_valid ( callbackdata ) = 1 and callbackdata -> "$.productId" = "P001"
```

上面查询语句中where后面的条件顺序不能改变。

若我们储存的json数据存在多层关系时，想要查询更深层的key，就需要将父级元素的Key全部写上：

```json
{
	"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的记录，那么我的查询语句应该是：

```sql
SELECT * FROM `test_common_tb` 
where json_valid ( callbackdata ) = 1 and callbackdata->'$.object.idType' = '100'
```

只写`callbackdata->'$.idType'='100'`它只会在第一级key去寻找。

### 总结

查询出column符合json规范的记录：

```sql
SELECT * FROM `table_name` 
WHERE json_valid(column_name) = 1
```

1为合规，0为不合规。

通用的查询储存json数据的字段中key=value的记录：

```sql
SELECT * FROM table_name
WHERE json_valid ( column_name ) = 1 and column_name -> "$.key" = "value"
```

查询多级key时，需要将父级key全部写上

```sql
SELECT * FROM `talbe_name` 
WHERE json_valid ( column_name ) = 1 and column_name->'$.key.key' = 'value'
```

复杂情况：json中存在数组和有小数点的key
```json
{
    "productId": "P002",
    "bizId": "2001",
    "listingId": "1107_03",
    "flowId": "sdfgsdfggds",
    "auditResult": 0,
    "auditReason": "F01;",
    "statusId": 0,
    "statusDesc": "success",
	"values":[
		"xxx.xxx.xxx":{
			"productId": "34545",
			"bizId": "1002",
			"listingId": "1234234ersf",
			"idNumber": "346235tw34563453",
			"idType": "100",
			"mobile": "345346",
			"userId": "zfdgsd",
			"cifNo": "3453453",
			"cifType": "234",
			"cstLevelCd": "432",
			"flowId": "sdfgsdfggds",
			"flowCount": 1
		}
	]
}
```

查询'flowCount'为1的记录：
```sql
select 
	* 
from 
	table 
where 
	json_valid(content) = 1 
	and content->'$.values[0]."xxx.xxx.xxx".flowCount' = 1
```

### 下面是一些出结果没出原因的实验

> 下半部分可以不看，，我留着以后知识点更深入了再研究。。

使用EXISTS关键字，有些表正常查询，有些表查询错误：

```sql
SELECT
	 *
FROM
	`test_common_tb` a
WHERE
	EXISTS ( SELECT id FROM `test_common_tb` b WHERE json_valid ( callbackdata ) = 1 and a.id = b.id
) AND callbackdata -> "$.productId" = "P001"
```

使用JOIN关键字的错误示范：

```sql
SELECT
	* 
FROM test_common_tb  b
	right join ( SELECT * FROM `test_common_tb` WHERE json_valid ( callbackdata ) = 1 ) as temp
	on b.id = temp.id
 where b.callbackdata -> "$.productId" = "P001"
```

使用JOIN关键字的正确示范：

```sql
SELECT
	* 
FROM test_common_tb  b
	right join ( SELECT * FROM `test_common_tb` WHERE json_valid ( callbackdata ) = 1 and callbackdata -> "$.productId" = "P001") as temp
	on b.id = temp.id
```

FROM子表的错误示范：

```sql
SELECT
	 *
FROM
	 ( SELECT * FROM `test_common_tb` WHERE json_valid ( callbackdata ) = 1 ) as temp
	WHERE callbackdata -> "$.productId" = "P001"
```

FROM子表的正确示范：

```sql
先执行子查询：
SELECT * FROM `test_common_tb` WHERE json_valid ( callbackdata ) = 1

查出结果为16条。
再执行总查询，**加上limit**：

SELECT
	 *
FROM
	 ( SELECT * FROM `test_common_tb` WHERE json_valid ( callbackdata ) = 1 limit 16 ) as temp
	WHERE callbackdata -> "$.productId" = "P001"
```

WHERE错误示范：

> 这个错误是由于where后面跟着的查询条件如果不包含子查询的话，就会按照顺序执行，所以必须先将json_valid放在前面筛选掉一些不合规的数据

```sql
SELECT
	 *
FROM test_common_tb
WHERE callbackdata -> "$.productId" = "P001" and json_valid ( callbackdata ) = 1 
```

WHERE正确示范：

```sql
SELECT
	 *
FROM test_common_tb
WHERE json_valid ( callbackdata ) = 1 and callbackdata -> "$.productId" = "P001"
```

WHERE条件含有子查询的错误示范：

```sql
SELECT
	* 
FROM
	`test_common_tb` 
WHERE
	id IN ( SELECT id FROM `test_common_tb` WHERE json_valid ( callbackdata ) = 1 ) 
	AND callbackdata -> "$.productId" = "P001"
```

WHERE条件含有子查询的正确示范：

```sql
SELECT
	 *
FROM
	`test_common_tb`
WHERE
	id IN ( SELECT id FROM `test_common_tb` WHERE json_valid ( callbackdata ) = 1 AND callbackdata -> "$.productId" = "P001") 
```

#### in的实质

>  前言：

我看到网上的一些资料，说

SELECT columns FROM tables WHERE column1 in (value1, value2, .... value_n) AND column2 ='column2';

和

SELECT columns FROM tables WHERE column2 ='column2' AND column1 in (value1, value2, .... value_n);

执行结果是不同的，并不是简单的and前后条件取并集。他们说IN的实质是一堆OR条件组合的简略形式，

column1 in (value1, value2, .... value_n) 实际上是  clumn1 = value1 OR column1= value2 OR…… OR column = value_n

按照这样的说法，两条sql的查询结果可能会发生改变，因为and的执行优先级比or高，

所以第一个sql的条件是： clumn1 = value1 OR column1= value2 OR…… OR **column = value_n and column2 ='column2'**

第二个sql的条件是： **column2 ='column2' and clumn1 = value1** OR column1= value2 OR…… OR column = value_n

> 实验：

所以我做了如下实验：

表中数据：![](https://minio.riun.xyz/riun1/2022-06-25_rvJlZUsAqDZ82IuQS0.jpg)

准备一条sql：

```sql
SELECT * FROM `test_common_tb` where id in (1,2,3) and callbackdata->'$.productId' = 'P002'
```

查出数据：![](https://minio.riun.xyz/riun1/2022-06-25_rw4a5YHpeXgEUNgX4J.jpg)

如果按照上述说法，这条sql的**真实面目**其实应该是下面这样：

```sql
SELECT * FROM `test_common_tb` where id = 1 or id = 2 or id = 3 and callbackdata->'$.productId' = 'P002'
```

那么我们再用这条sql查出数据：![](https://minio.riun.xyz/riun1/2022-06-25_rwg9h35jp0AYufmMj1.jpg)

明显是不同的。

所以我认为MySQL在解析in时还要在所有or外面添加括号，即下面这样：

```sql
SELECT * FROM `test_common_tb` where (id = 1 or id = 2 or id = 3) and callbackdata->'$.productId' = 'P002'
```

查询结果：![](https://minio.riun.xyz/riun1/2022-06-25_rwt8vzamzGmVcMUJ9k.jpg)

结论：IN在查询时会解析为多个OR，并且在外层添加大括号，使其为一体。

mysql查询json数据的指定内容

文章目录

发表评论