Mysql的JSON格式字段实用操作函数JSON_CONTAINS、JSON_SEARCH、JSON_EXTRACT

文章目录

• 前言
• 一、示例数据
• 二、使用
• • 1.JSON_CONTAINS
  • 2.JSON_SEARCH
  • 3.JSON_EXTRACT
• 总结

前言

在开发中难免会遇见在Mysql字段存储JSON格式数据的业务情况，记录几种常用函数的
用法。

一、示例数据

建一张表，字段memo存储JSON格式数据

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` bigint DEFAULT NULL COMMENT '姓名',
  `age` bigint DEFAULT NULL COMMENT '年龄',
  `sex` bigint DEFAULT NULL COMMENT '性别',
  `memo` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '说明',
  PRIMARY KEY (`id`)
) 

memo的数据为：

{
	"location":"位置1",
	"purchaseTime":"2022-07-04",
	"assetsTypeName":"CPU",
	"useDepartment":"测试部",
	"strId":11
}

二、使用

1.JSON_CONTAINS

JSON_CONTAINS 函数用于检查一个JSON文档是否包含另一个JSON文档或值。它返回1表示包含，返回0表示不包含，返回NULL表示参数有误或不兼容。

示例：查询符合条件的user数据

SELECT * from user
WHERE memo IS NOT NULL 
and JSON_CONTAINS(memo, '"位置1"', '$.location') 
and JSON_CONTAINS(memo, '"测试部"', '$.useDepartment');

SELECT * from user
WHERE memo IS NOT NULL 
and JSON_CONTAINS(memo, '11', '$.strId');

$.location：数据路径。
“位置1”：具体值，注意这里匹配具体值的时候字符值要加双引号（“”），和上面JSON值对上，数字类型正常匹配就行。

2.JSON_SEARCH

JSON_SEARCH 函数用于在JSON文档中搜索指定值，返回该值的路径或位置。有两种模式：“one"和"all”，分别返回第一个匹配的位置或所有匹配的位置。

示例：查询符合条件的user数据

SELECT * FROM user
WHERE memo IS NOT NULL 
and JSON_SEARCH(memo, 'one', '位置1');


SELECT * FROM user
WHERE memo IS NOT NULL 
and JSON_SEARCH(memo, 'all', '位置1');

3.JSON_EXTRACT

JSON_EXTRACT 函数用于从JSON文档中提取一个或多个值。

示例：查询符合条件的useDepartment

# 方式1
SELECT 
    DISTINCT TRIM(BOTH '"' FROM JSON_EXTRACT(memo, '$.useDepartment')) AS useDepartment
FROM user
WHERE memoIS NOT NULL
AND JSON_EXTRACT(memo, '$.purchaseTime') ='2022-07-03'
AND JSON_EXTRACT(memo, '$.assetsTypeName') = 'CPU'
AND JSON_EXTRACT(memo, '$.location') = '位置1'

# 方式2
#  ->  等效于 JSON_EXTRACT()
SELECT 
    DISTINCT TRIM(BOTH '"' FROM JSON_EXTRACT(memo, '$.useDepartment')) AS useDepartment
FROM user
WHERE memoIS NOT NULL
AND memo->'$.purchaseTime' ='2022-07-03'
AND memo->'$.assetsTypeName' = 'CPU'
AND memo->'$.location' = '位置1'

$.location：数据路径。
TRIM(BOTH ‘"’ FROM …)：移除字符串两边的双引号。

补充
TRIM()函数：
从字符串中删除不必要的前导和后缀字符
用法：
TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str)

总结

mysql针对JSON数据处理的函数还有很多，有兴趣的小伙伴可以自行了解学习。
Mysq官网