MySQL 的 JSON 格式字段用法

更新于: 2023-10-28 10:57:44 查看: 370

发布于: 2023-10-28 10:24 发布者: 大米

1、概述MySQL 5.7.8 新增 JSON 数据类型，用于定义 JSON 格式的数据。在此之前，表中要存储 JSON 数据都是定义一个 varchar 类型字段，客户端序列化和反序列化处理。但是这种方法不具备强约束性，只要是字符串存什么 ...

1、概述

MySQL 5.7.8 新增 JSON 数据类型，用于定义 JSON 格式的数据。

在此之前，表中要存储 JSON 数据都是定义一个 varchar 类型字段，客户端序列化和反序列化处理。但是这种方法不具备强约束性，只要是字符串存什么都行。

而新的 JSON 类型会校验数据格式，只能存储 JSONObject 类型和 JSONArray 类型。

JSONObject:

{
  "name": "aaa"
}

JSONArray:

[
  {"name":"aaa"}, {}
]

键只能为字符串

值类型支持 null, string, boolean, number, object, array

2、定义

创建表时指定字段类型为 JSON，JSON 类型无需指定长度，且默认值只能为 null。

JSON 字段类型不用显示指定是对象还是数组结构，根据实际存储数据自动推断

CREATE TABLE `t_json_tbl` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `json_obj` json DEFAULT NULL COMMENT 'json 对象字段',
  `json_arr` json DEFAULT NULL COMMENT 'json 数组字段',
  `json_str` varchar(255) DEFAULT NULL COMMENT 'json 格式字符串字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3、插入数据

方式 1：

以普通字符串形式插入，需要遵循 json 格式

insert into t_json_tbl(json_obj, json_arr, json_str) 
  values('{"name":"tom", "age":21, "tags":["a", "b"]}', '["aa", "bb", "cc"]', '{"name":"jj"}');
-- id=1

方式 2：

使用 JSON 内置创建函数

创建 JSON 对象：JSON_OBJECT([key, val[, key, val] ...])

创建 JSON 数组：JSON_ARRAY([val[, val] ...])

函数文档：json-creation-functions

函数可以嵌套使用

insert into t_json_tbl(json_obj, json_arr, json_str)
  values(JSON_OBJECT('name', 'jerry', 'tags', JSON_ARRAY('c', 'd')), JSON_ARRAY('xx', 'yy', 'cc'), JSON_OBJECT('name', 'jack'));
-- id=2

（也可以通过 JSON 函数操作普通字符串类型数据）

注意不要这么使用:

-- JSON_OBJECT('name', 'jerry', 'tags', '["c", "d"]') 第二个字段的值以普通字符串插入，不是数组格式
insert into t_json_tbl(json_obj, json_arr, json_str)
  values(JSON_OBJECT('name', 'jerry', 'tags', '["c", "d"]'), JSON_ARRAY('xx', 'yy', 'cc'), JSON_OBJECT('name', 'jack'));
-- id=3

同名的字段只会保留第一个

4、查询操作

按照普通字符串去查询整个字段数据：

select json_obj, json_arr from t_json_tbl;

查询 JSON 中字段的数据

查询 JSON 字段数据，需要 column -> 'path' 形式访问指定字段的具体数据。

注意这个 'path' 外层是有一个单引号的

其中 column 表示要查询的数据字段列名；

path 为 JSON 数据的访问路径，path格式为 $.path 或 $[idx]。

$.path 用于 JSONObject 类型数据；
$[idx] 用于 JSONArray 类型数据；
$ 代表整个 JSON 数据的 root 节点；
path 为访问字段 key，如果字段名包含空格，则需要用双引号包住，如 $."nick name"；
[idx] 是数组的索引。

例：

{
  "name":"tom", 
  "age":21, 
  "tags":["a", "b"]
}

$.name 值为 "tom"
$.tags[0] 值为 "a"

查询示例：

select json_obj->'$.name' `name`, json_obj->'$.tags[0]' `tags0`, json_arr->'$[0]' xx from t_json_tbl;

-> 查询到的字段字符串类型还会有个双引号，还需要做一层处理

可以使用 ->> 去除，且转义符也会去除

select json_obj->>'$.name' `name`, json_obj->>'$.tags[0]' `tags0`, json_arr->>'$[0]' xx from t_json_tbl;

可以使用内置函数进行查询

JSON_EXTRACT(column, path) 等价于 column->path
JSON_UNQUOTE(JSON_EXTRACT(column, path)) 等价于 column->>path

其他内置查询函数：json-search-functions

条件查询

select * from t_json_tbl where json_obj->'$.name' = 'Merry';

模糊查询

JSON 字段的模糊搜索仅支持 %str% 格式，也因此其模糊搜索时索引是失效的

select * from t_json_tbl where json_obj->'$.name' like '%tom%';

5、更新操作

使用内置函数更新 JSON 字段：json-modification-functions

更新字段

JSON_SET(json_doc, path, val[, path, val] ...) 更新或插入
JSON_REPLACE(json_doc, path, val[, path, val] ...) 只更新

update t_json_tbl 
set 
json_obj = JSON_SET(json_obj, '$.name', 'Merry'),
json_arr = JSON_SET(json_arr, '$[0]', 'aa2222', '$[2]', 'gggg', '$[7]', 'fdsfd')
where id = 1;

新增字段

JSON_INSERT(json_doc, path, val[, path, val] ...) 插入新字段，不会改变已经存在的

update t_json_tbl 
set 
json_obj = JSON_INSERT(json_obj, '$.name', 'Merry'),
json_arr = JSON_INSERT(json_arr, '$[0]', 'aa2222', '$[2]', 'gggg', '$[7]', 'fdsfd')
where id = 2;

删除字段

JSON_REMOVE(json_doc, path[, path] ...) 删除字段

update t_json_tbl 
set 
json_obj = JSON_REMOVE(json_obj, '$.tags'),
json_arr = JSON_REMOVE(json_arr, '$[0]', '$[7]')
where id = 3;

其他操作函数：json-modification-functions

支持

反对

扫描二维码，手机查看

声明：部分数据/图片来源互联网，不代表Hluse Inc.,真实性请妥善甄别。