概述

MySQL 自5.7起开始支持JSON格式的非结构化数据，并且在8.x版本进行性能优化

关于 JSON

JSON（JavaScript Object Notation, JS对象简谱）是一种轻量级的数据交换格式。它基于 ECMAScript（European Computer Manufacturers Association, 欧洲计算机协会制定的 js 规范）的一个子集，采用完全独立于编程语言的文本格式来存储和表示数据。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。

时下 JSON 使用访问非常广泛，大多 Web 接口都采用该格式。

MySQL 相关函数

详见：json-functions

说明

1. 文档中的 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) }
}