概述
MySQL 自
5.7
起开始支持JSON格式
的非结构化数据,并且在8.x
版本进行性能优化
关于 JSON
JSON(JavaScript Object Notation, JS对象简谱)是一种轻量级的数据交换格式。它基于 ECMAScript(European Computer Manufacturers Association, 欧洲计算机协会制定的 js 规范)的一个子集,采用完全独立于编程语言的文本格式来存储和表示数据。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。易于人阅读和编写,同时也易于机器解析和生成,并有效地提升网络传输效率。
时下 JSON 使用访问非常广泛,大多 Web 接口都采用该格式。
MySQL 相关函数
说明
- 文档中的
json_doc
是值以字符串形式传递的 JSON 对象(通常用单引号包裹)
实践操作
建表
这里创建一个简单表,包含格式为
json
的字段value
CREATE TABLE `demo` (
`id` bigint NOT NULL AUTO_INCREMENT,
`t` varchar(100) DEFAULT NULL,
`v` json DEFAULT NULL,
`addOn` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
数据增删改查
新增数据
INSERT INTO demo (`t`, `v`) VALUES
('0', '{"day":"2022-12-16", "value":"阴", "uid":"001","region":{"code":4500}}'),
('0', '{"day":"2022-12-16", "value":"阴", "uid":"002","region":{"code":4501}}'),
('0', '{"day":"2022-12-16", "value":"阳", "uid":"003","region":{"code":4500}}');
可以看到,JSON
类型的数据其实还是以字符串形式进行插入,此处需要注意 JSON 格式的字符串是强格式的(属性名必须是双引号,属性值如果是字符串也必须是双引号)。
再看看新增后的数据,发现对 Key 进行了排序(如下图)。
数据查询
/*平台字段查询*/
select * from demo where t='0';
/*
JSON 对象查询
-> 等价于 JSON_EXTRACT 函数
*/
select * from demo where t='0' and v->'$.value'='阴';
select * from demo where t='0' and JSON_EXTRACT(v, '$.value')='阴'
/*JSON 子对象查询*/
select * from demo where t='0' and v->'$.value'='阴' and v->'$.region.code'=4500;
select * from demo where t='0' and v->'$.value'='阴' and v->'$.region.code'>4500;
/*
如果不确定 JSON 对象查询 path 怎么写,可以用 JSON_SEARCH 函数
SELECT JSON_SEARCH('{"day":"2022-12-16", "value":"阴", "uid":"001","region":{"code":4500}}', 'one', '阴');
结果为 $.value
*/
path 语法
未来更好地理解查询 path ,官方有一段示例:
假设 JSON 对象为 [3, {"a": [5, 6], "b": 10}, [99, 100]]
路径 | 值 |
---|---|
$[0] | 3 |
$[1] | {“a”: [5, 6], “b”: 10} |
$[2] | [99, 100] |
$[3] | NULL |
$[1].a | [5, 6] |
$[1].a[1] | 6 |
$[1].b | 10 |
$[2][0] | 99 |
$[3]
指向不存在的第四个元素,故为 NULL
提取 JSON 内容
select t as `type`, v->'$.day' as day, v->'$.region.code' as region, v->'$.uid' as uid from demo;
select t as `type`, v->>'$.day' as day, v->'$.region.code' as region, v->>'$.uid' as uid from demo;
/*
-> 与 ->> 的区别是,前者返回值带双引号,后者不带(与使用习惯一致)
结果如下
type|day |region|uid |
----+------------+------+-----+
0 |"2022-12-16"|4500 |"001"|
0 |"2022-12-16"|4501 |"002"|
0 |"2022-12-16"|4500 |"003"|
*/
排序
select t as `type`, v->>'$.day' as day, v->'$.region.code' as region, v->>'$.uid' as uid from demo order by region desc;
修改
官方提供如下函数来修改 JSON 对象:
函数名 | 说明 | 参数 |
---|---|---|
JSON_SET | replaces values for paths that exist and adds values for paths that do not exist | json_doc, path, val[, path, val] … |
JSON_REPLACE | adds new values but does not replace existing values | json_doc, path, val[, path, val] … |
JSON_INSERT | json_doc, path, val[, path, val] … | |
JSON_REMOVE | takes a JSON document and one or more paths that specify values to be removed from the document | json_doc, path[, path] … |
/*修改 region.code 为 4501*/
update demo set v =JSON_SET(v, '$.region.code', 4501) where id=2;
/*向 region 子对象中插入 name 属性*/
update demo set v =JSON_INSERT(v, '$.region.name', "柳州") where id =2;
/**
最后的对象为
{
"day": "2022-12-16",
"uid": "002",
"value": "阴",
"region": {
"code": 4501,
"name": "柳州"
}
}
*/
其实用官方的方法使用起来不是特别方便(组合修改比较麻烦),一般都是直接在代码中修改好对象,再转换为 JSON 字符串覆盖到相应的字段 😄
删除
与传统的操作一致
集成 MyBatis-Plus
此处使用
kotlin
进行示范
// 定义实体类,此处 autoResultMap 需要设置为 true (否则 TypeHandler 的 parse 无法正常被调用)
@TableName(autoResultMap = true)
class Demo {
var id = 0
var t = ""
@TableField(typeHandler = JacksonTypeHandler::class, jdbcType = JdbcType.BLOB)
var v = mapOf<String, Any>()
var addOn:Timestamp? = null
}
@Mapper
interface DemoMapper:BaseMapper<Demo>
@Test
fun queryDemo(){
// 使用 LambdaQueryChainWrapper
LambdaQueryChainWrapper(demoMapper)
.apply("v->'$.value'='阴'")
.apply("v->'$.region.code'>4500")
.list()
.forEach { d-> println(JSON.toJSONString(d)) }
// 使用 QueryWrapper
demoMapper.selectList(
QueryWrapper<Demo>()
.apply("v->'$.value'='阴'")
.apply("v->'$.region.code'>4500")
).forEach { d->
println(JSON.toJSONString(d))
}
/*
使用 SqlRunner(需要开启该功能)
mybatis-plus:
global-config:
enable-sql-runner: true
*/
SqlRunner.db()
.selectList("select * from demo where t='0' and v->'$.value'='阴' and v->'$.region.code'>4500;")
.forEach { d-> println(d) }
}