MySQL---JSON的用法讲解

最新推荐文章于 2024-06-14 16:48:38 发布

烟雨平生9527

最新推荐文章于 2024-06-14 16:48:38 发布

阅读量1.5k

点赞数 23

分类专栏： # 数据库文章标签： mysql json 数据库

本文链接：https://blog.csdn.net/qq_62377885/article/details/138818007

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、概述

1.1MySQL的JSON概述

JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 或 TEXT ）来保存 JSON 文档。

MySQL 8.0版本中增加了对JSON类型的索引支持。可以使用CREATE INDEX语句创建JSON类型的索引，提高JSON类型数据的查询效率。

存储JSON文档所需的空间与存储LONGBLOB或LONGTEXT所需的空间大致相同。

在MySQL 8.0.13之前，JSON列不能有非空的默认值。

JSON 类型比较适合存储一些列不固定、修改较少、相对静态的数据。MySQL支持JSON格式的数据之后，可以减少对非关系型数据库的依赖。

1.2varchar、text、json类型字段的区别

存储结构：
- VARCHAR是一种可变长度的字符串类型，适合存储较短的字符串。它的最大长度限制为65535个字符。VARCHAR类型使用动态方式存储数据，仅占用实际内容所需的空间，加上额外的1-2个字节来记录字符串的长度。
- TEXT类型是长文本数据类型，专门用于存储大量非结构化文本数据，最大可以存储65535个字符（在不同的字符集下，可能有所不同）。与VARCHAR不同，TEXT类型的数据存储在外存中，而不是记录的其余部分。这意味着读取TEXT字段可能会比VARCHAR稍慢一些，因为它可能需要额外的磁盘I/O操作。
- JSON类型是MySQL 5.7.8版本开始支持的原生数据类型，用于存储JSON格式的数据。与VARCHAR或TEXT相比，JSON类型提供了对JSON数据的原生支持，包括自动校验文档的正确性。此外，JSON类型允许对JSON数据执行特定的查询和操作，这在使用VARCHAR或TEXT保存JSON数据时是无法实现的。
性能考虑：
- VARCHAR由于其存储效率和直接包含在表中的特性，对于较小的字符串数据访问速度较快。
- TEXT类型由于数据存储在外存，当频繁访问时可能会有性能影响，尤其是当涉及到大量的磁盘I/O操作时。
- JSON类型虽然增加了数据校验和特定查询操作的优势，但这些操作可能会对性能产生一定的影响，特别是在处理大型JSON文档时。
功能特性：
- VARCHAR和TEXT主要针对通用的字符串存储需求，没有特定于JSON的功能。
- JSON类型提供了对JSON数据的结构检查、索引、优化的搜索等功能。

综上所述，在选择数据类型时，如果需要存储较短的字符串且不需要JSON特有的功能，VARCHAR是一个不错的选择；如果需要存储大量的非结构化文本数据，可以考虑使用TEXT类型；而如果数据是JSON格式，并且需要利用JSON的查询和操作优势，则建议使用JSON类型。

总体而言，JSON 类型比较适合存储一些列不固定、修改较少、相对静态的数据，比如用户画像、商品标签、接口数据等，原先大家可能会用Mongo来存储此类数据，现在MySQL也支持使用了。

二、语法

2.1JSON类型的创建

1、建表指定

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(50) DEFAULT NULL COMMENT '名字',
  `json_data` json DEFAULT NULL COMMENT 'json数据',
  `info` varchar(2000) DEFAULT NULL COMMENT '普通数据',
  `text` text COMMENT 'text数据',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

2、修改字段

-- 添加json字段
ALTER TABLE users ADD COLUMN `test_json` JSON DEFAULT NULL COMMENT '测试';
-- 修改字段类型为json
ALTER TABLE users modify test_json JSON DEFAULT NULL COMMENT '测试';
-- 删除json字段
ALTER TABLE users DROP COLUMN test_json;

2.2JSON类型的插入

1、字符串直接插入

varchar、text、json格式都支持，也可以插入更复杂的嵌套json：

-- 插入数组
insert into users(json_data) values('[1, "abc", null, true, "08:45:06.000000"]');
insert into users(info) values('[1, "abc", null, true, "08:45:06.000000"]');
insert into users(text) values('[1, "abc", null, true, "08:45:06.000000"]');
-- 插入对象
insert into users(json_data) values('{"id": 87, "name": "carrot"}');
insert into users(info) values('{"id": 87, "name": "carrot"}');
insert into users(text) values('{"id": 87, "name": "carrot"}');
-- 插入嵌套json
insert into users(json_data) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
insert into users(info) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
insert into users(text) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');

但是json格式的字段，插入时会自动校验格式，如果格式不是json的，会报错：

insert into users(json_data) values('{"id", "name": "carrot"}');
> 3140 - Invalid JSON text: "Missing a colon after a name of object member." at position 5 in value for column 'users.json_data'.

2、JSON_ARRAY()函数插入数组

-- 格式：
JSON_ARRAY([val[, val] ...])

-- 使用JSON_ARRAY()函数创建数组 ： [1, "abc", null, true, "08:09:38.000000"]
insert into users(json_data) values(JSON_ARRAY(1, "abc", null, true,curtime()));
insert into users(info) values(JSON_ARRAY(1, "abc", null, true,curtime()));
insert into users(text) values(JSON_ARRAY(1, "abc", null, true,curtime()));

3、JSON_OBJECT()函数插入对象

对于 JSON 文档，KEY 名不能重复。

如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。

从 MySQL 8.0.3 开始，遵循的是 last duplicate key wins 原则，只会保留最后一个 KEY。

-- 格式：
JSON_OBJECT([key, val[, key, val] ...])

-- 创建对象，一个key对应一个value ： {"id": 87, "name": "carrot"}
insert into users(json_data) values(json_object('id', 87, 'name', 'carrot'));
insert into users(info) values(json_object('id', 87, 'name', 'carrot'));
insert into users(text) values(json_object('id', 87, 'name', 'carrot'));

4、JSON_ARRAYAGG()和JSON_OBJECTAGG()将查询结果封装成json

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
    -> FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
    -> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

5、CAST()将字符串转成json

如果直接插入的话，插入的是字符串，需要手动转换

假设我们有一个名为`students`的表，其中有一个名为`info`的列，存储了学生的信息，数据类型为`VARCHAR`。现在我们想要将`info`列中的字符串转换为JSON格式。

查询语句如下：

SELECT CAST(info AS JSON) as json_info
FROM students;

这个查询将会返回一个名为`json_info`的列，其中包含了转换后的JSON格式的学生信息。

2.3提取JSON对象

主要是JSON_EXTRACT(根据键提取值)，JSON_UNQUOTE(去除最外侧的双引号)，或者-> 和 ->> 表达式。

1、JSON_EXTRACT()解析json

JSON_EXTRACT() 是一个用于从 JSON 数据中提取值的函数。它接受两个参数：第一个参数是要解析的 JSON 字符串，第二个参数是用于指定要提取的值的路径。

如果指定 path 不存在，会返回 NULL。可指定多个 path，匹配到的多个值会以数组形式返回。

SELECT JSON_EXTRACT('{"name": "张三", "age": 30, "address": {"city": "北京", "street": "朝阳路"}}', '$.name');
#    执行上述 SQL 语句后，将返回结果 "张三"。

-- 解析数组
-- 取下标为1的数组值（数组下标从0开始），结果：20
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
-- 取多个，结果返回是一个数组，结果：[20, 10]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
-- 可以使用*获取全部，结果：[30, 40]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');

-- 还可通过 [M to N] 获取数组的子集
-- 结果：[10, 20]
select json_extract('[10, 20, [30, 40]]', '$[0 to 1]');
-- 这里的 last 代表最后一个元素的下标，结果：[20, [30, 40]]
select json_extract('[10, 20, [30, 40]]', '$[last-1 to last]');

-- 解析对象：对象的路径是通过 KEY 来表示的。
set @j='{"a": 1, "b": [2, 3], "a c": 4}';

-- 如果 KEY 在路径表达式中不合法（譬如存在空格），则在引用这个 KEY 时，需用双引号括起来。
-- 结果： 1 4 3
select json_extract(@j, '$.a'), json_extract(@j, '$."a c"'), json_extract(@j, '$.b[1]');
-- 使用*获取所有元素，结果：[1, [2, 3], 4]
select json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*');
-- 这里的 $**.b 匹配 $.a.b 和 $.c.b，结果：[1, 2]
select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');

json_extract解析出来的数据，可以灵活用于where、order by等等所有地方。

2、JSON_QUOTE()引用与JSON_UNQUOTE()取消引用

JSON_QUOTE() 是一个用于将字符串转换为 JSON 格式的函数。它接受一个参数，即要转换的字符串。

SELECT JSON_QUOTE('Hello, World!');

JSON_QUOTE(string)，生成有效的 JSON 字符串，主要是对一些特殊字符（如双引号）进行转义。

-- 结果："null"	"\"null\""	"[1, 2, 3]"
select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');

JSON_UNQUOTE(json_val)，将 JSON 转义成字符串输出。常用于使用JSON_EXTRACT()和->函数解析完之后，去除引号。
JSON_UNQUOTE()特殊字符转义表：

转义序列	由序列表示的字符
\"	双引号
\b	退格字符
\f	换页字符
\n	换行符
\r	回车符
\t	制表符
\\	反斜杠(\)字符
\uXXXX	Unicode XXXX 转UTF-8

insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
-- 字符串类型转换后会去掉引号，结果："jack"	jack	1	0
select json_data->'$.ename',json_unquote(json_data->'$.ename'),json_valid(json_data->'$.ename'),json_valid(json_unquote(json_data->'$.ename')) from users;
-- 数字类型转换并没有额外效果，结果：1001	1001	1	1
select json_data->'$.empno',json_unquote(json_data->'$.empno'),json_valid(json_data->'$.empno'),json_valid(json_unquote(json_data->'$.empno')) from users;

直观地看，没加 JSON_UNQUOTE 字符串会用双引号引起来，加了 JSON_UNQUOTE 就没有。但本质上，前者是 JSON 中的 STRING 类型，后者是 MySQL 中的字符类型，这一点可通过 JSON_VALID 来判断。

3、-> 箭头函数解析json

MySQL 还提供了-> 和 ->> 表达式，和上述 SQL 效果一样，其中->能得到提取结果但是不去除外面的双引号，与JSON_EXTRACT对应， ->>能得到提取结果且会去除外面的符号，与 JSON_UNQUOTE(JSON_EXTRACT)对应。

-> = JSON_EXTRACT
->> = JSON_UNQUOTE(JSON_EXTRACT)

column->path，包括后面讲到的 column->>path，都是语法糖，在实际使用的时候都会在底层自动转化为 JSON_EXTRACT。

column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一个path。

-- 同JSON_EXTRACT
insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
-- 结果："jack"
select json_data, json_data -> '$.ename' from users;

4、->>箭头解析json

同 column->path 类似，只不过其返回的是字符串，相当于将字符串的双引号去掉了，是一个语法糖，本质上是执行了JSON_UNQUOTE( JSON_EXTRACT(column, path) )。

以下三者是等价的：
JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path

insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
-- 结果："jack"	jack	jack	jack
select json_data->'$.ename',json_unquote(json_data->'$.ename'),json_data->>'$.ename', JSON_UNQUOTE( JSON_EXTRACT(json_data, '$.ename') ) from users;

2.4JSON数据过滤

提取JSON后不能用新命名的字段做筛选过滤，需要调用把JSON函数或者符号再写一遍。

JSON对象过滤

这种方式与原先的非JSON类型条件过滤类似，写法比较简单明了，但是只能用于过滤JSON对象，无法过滤JSON数组。

mysql> select * from student;
+----+-------------------------------------------------+
| id | info |
+----+-------------------------------------------------+
| 1 | {"age": 13, "sex": "F", "city": "beijing"} |
| 2 | {"age": 14, "sex": "M", "city": "suzhou"} |
| 3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
| 4 | [1, 2, 3, 4] |
| 5 | [{"sex": "M"}, {"sex": "F", "city": "nanjing"}] |
+----+-------------------------------------------------+

/*筛选sex是F，age大于14的*/
mysql> select id,info from student WHERE info->>'$.age' > 14 and info->>'$.sex' = 'F';
+----+---------------------------------------------+
| id | info |
+----+---------------------------------------------+
| 3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
+----+---------------------------------------------+

JSON函数过滤(高级

MySQL8中针对JOSN类型，新增了部分JSON函数用于数据过滤(包括JSON对象和JSON数组)：

MEMBER OF：匹配某个元素是否存在，返回1表示元素存在，返回0表示元素不存在。
JSON_CONTAINS：对JSON数组检查一个元素或者多个元素是否存在，对于JSON对象检查指定KEY是否有某个值。
JSON_OVERLAP：比较两个JSON数组是否至少有一个元素一致，如果是返回1，否则返回0，如果是JSON对象，判断是否是有一对key value一致。

以上函数可以在前面加上NOT关键字就可以取反)

1、JSON_CONTAINS()判断是否包含

格式：JSON_CONTAINS(target, candidate[, path])
判断 target 文档是否包含 candidate 文档，包含的话返回1，不包含的话返回0

如果带了path，就判断path中的数据是否等于candidate，等于的话返回1，不等于的话返回0

JSON_CONTAINS() 函数用于判断 JSON 数据中是否包含指定值。它接受三个参数：

target：要搜索的 JSON 数据。
candidate：要在 target 中查找的值。
path（可选）：在 target 中搜索的路径。

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SET @j2 = '{"a":1}';
-- 判断@j中是否包含@j2，结果：1
SELECT JSON_CONTAINS(@j, @j2);

SET @j2 = '1';
-- 判断@j字段中的a是否等于1，结果：1
SELECT JSON_CONTAINS(@j, @j2, '$.a');
-- 结果：0
SELECT JSON_CONTAINS(@j, @j2, '$.b');

SET @j2 = '{"d": 4}';
-- 结果：0
SELECT JSON_CONTAINS(@j, @j2, '$.a');
-- 结果：1
SELECT JSON_CONTAINS(@j, @j2, '$.c');

SET @j = '[1, "a", 1.02]';
SET @j2 = '"a"';
-- 判断@j数组中是否包含@j2，结果：1
SELECT JSON_CONTAINS(@j, @j2);

2、JSON_CONTAINS_PATH()判断

格式：JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
判断指定的 path 是否存在，存在，则返回 1，否则是 0。

函数中的 one_or_all 可指定 one 或 all，one 是任意一个路径存在就返回 1，all 是所有路径都存在才返回 1。

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
-- a或者e 存在一个就返回1，结果：1
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
-- a和e都存在返回1，结果：0
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
-- c中的d存在返回1，结果：1
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');

SET @j = '[1, 4, "a", "c"]';
-- @j是一个数组，$[1]判断第二个数据是否存在，结果为1
select JSON_CONTAINS_PATH(@j, 'one', '$[1]');
-- $[11]判断第11个数据不存在，结果为0
select JSON_CONTAINS_PATH(@j, 'one', '$[11]');

3、JSON_KEYS()获取keys

返回 JSON 文档最外层的 key，如果指定了 path，则返回该 path 对应元素最外层的 key。

-- 结果：["a", "b"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
-- 结果：["c"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');

4、JSON_OVERLAPS()比较两个json

MySQL 8.0.17 引入的，用来比较两个 JSON 文档是否有相同的键值对或数组元素，如果有，则返回 1，否则是 0。如果两个参数都是标量，则判断这两个标量是否相等。

5、JSON_SEARCH()返回字符串的位置

格式：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])

JSON_SEARCH() 函数用于在 JSON 数据中搜索指定的字符串，并返回该字符串的位置。它接受以下参数：

json_doc：要搜索的 JSON 数据。
one_or_all：指定搜索模式，可以是 "one" 或 "all"。如果为 "one"，则只返回第一个匹配的位置；如果为 "all"，则返回所有匹配的位置。
search_str：要在 JSON 数据中搜索的字符串。
escape_char（可选）：用于转义特殊字符的字符。如果该参数不填或为 NULL，则取默认转义符\。
path（可选）：指定在 JSON 数据中搜索的路径。

6、JSON_VALUE()提取指定路径的元素

格式：JSON_VALUE(json_doc, path)
8.0.21 引入的，从 JSON 文档提取指定路径（path）的元素。

JSON_VALUE() 函数用于从 JSON 文档中提取指定路径（path）的元素。它接受以下参数：

json_doc：要搜索的 JSON 数据。
path：指定在 JSON 数据中搜索的路径。
RETURNING type（可选）：指定返回值的类型，可以是 "STRING"、"NUMBER" 或 "BOOLEAN"。默认为 "STRING"。
on_empty（可选）：指定当路径为空时的处理方式，可以是 "NULL"、"ERROR" 或 "DEFAULT value"。默认为 "NULL"。
on_error（可选）：指定当路径错误时的处理方式，可以是 "NULL"、"ERROR" 或 "DEFAULT value"。默认为 "NULL"。

7、MEMBER OF()判断是否是json数组中的元素

格式：value MEMBER OF(json_array)
在 MySQL 8.0.17引入了MEMBER OF()函数。判断 value 是否是 JSON 数组的一个元素，如果是，则返回 1，否则是 0。

8、JSON_DEPTH()获取JSON最大深度

JSON_DEPTH() 函数用于获取 JSON 数据的最大深度。它接受一个参数：

json_doc：要搜索的 JSON 数据。

如果参数为NULL，则返回NULL。如果参数不是有效的JSON文档，则会出现错误。
对于空数组，空对象，标量值，其深度为 1。

9、JSON_LENGTH()获取文档长度

语法：JSON_LENGTH(json_doc[, path])

JSON_LENGTH() 函数用于获取 JSON 文档的长度。它接受一个或两个参数：

json_doc：要搜索的 JSON 数据。
path（可选）：指定在 JSON 数据中搜索的路径。

返回 JSON 文档的长度，其计算规则如下：
1、如果是标量值，其长度为 1。
2、如果是数组，其长度为数组元素的个数。
3、如果是对象，其长度为对象元素的个数。
4、不包括嵌套数据和嵌套对象的长度。

10、JSON_TYPE()获取JSON类型

语法：JSON_TYPE(json_val)
返回 JSON 值的类型。
如果参数不是有效的JSON值，则会出现错误。

JSON_TYPE() 函数用于获取 JSON 值的类型。它接受一个参数：

json_val：要获取类型的 JSON 值。

JSON类型：OBJECT（对象）、ARRAY（数组）、BOOLEAN（布尔类型）、NULL

数字类型：INTEGER（TINYINT、SMALLINT、MEDIUMINT以及INT和BIGINT标量）、DOUBLE（DOUBLE、FLOAT）、DECIMAL（MySQL、DECIMAL）

时间类型：DATETIME（DATETIME、TIMESTAMP）、DATE、TIME
字符串类型：STRING（CHAR, VARCHAR, TEXT, ENUM, SET）
二进制类型：BLOB（ BINARY, VARBINARY, BLOB, BIT）

其他类型：OPAQUE

11、JSON_VALID()校验JSON格式

语法：JSON_VALID(val)
判断给定值是否是有效的 JSON 文档。
函数前加not可取反

-- 结果：1
SELECT JSON_VALID('{"a": 1}');
-- 结果：0	1
SELECT JSON_VALID('hello'), JSON_VALID('"hello"');

2.5 JSON数据修改

修改数据主要是JSON_SET，JSON_INSERT，JSON_REPLACE三个方法，同时也支持完整的JSON列更新，但是不建议，因为需要把整个JSON拼成一个字符串，相对复杂，而前三种JSON方法可以只针对某个key做更新，相对简单。

JSON_SET：替换现有key的值，插入不存在的key的值。
JSON_INSERT：插入不存在的key的值，已经存在的不修改。
JSON_REPLACE：只替换已存在的key的值，不存在的不做插入。

1、全量修改

直接使用update语句，将json数据字段全部替换。

update users set json_data = '{"a":1}';

2、JSON_ARRAY_APPEND()向数组追加元素

格式：JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)

JSON_ARRAY_APPEND() 函数用于向 JSON 数组中追加元素。它接受一个或多个参数：

json_doc：要追加元素的 JSON 数据。
path：指定在 JSON 数据中追加元素的路径。
val：要追加的元素。
path和val的组合可以重复多次，用于追加多个元素。

SET @j = '["a", ["b", "c"], "d"]';
-- 在数组第二个元素的数组中追加1，结果：["a", ["b", "c", 1], "d"]
SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
-- 结果：[["a", 2], ["b", "c"], "d"]
SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
-- 结果：["a", [["b", 3], "c"], "d"]
SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
-- 多个参数，结果：[["a", 1], [["b", 2], "c"], "d"]
select json_array_append(@j, '$[0]', 1, '$[1][0]', 2, '$[3]', 3);

SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
-- 往b中追加，结果：{"a": 1, "b": [2, 3, "x"], "c": 4}
SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
-- 结果：{"a": 1, "b": [2, 3], "c": [4, "y"]}
SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');

SET @j = '{"a": 1}';
-- 结果：[{"a": 1}, "z"]
SELECT JSON_ARRAY_APPEND(@j, '$', 'z');

3、JSON_ARRAY_INSERT()向数组指定位置插入元素

格式：JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)

JSON_ARRAY_INSERT() 函数用于向 JSON 数组的指定位置插入元素。它接受一个或多个参数：

json_doc：要插入元素的 JSON 数据。
path：指定在 JSON 数据中插入元素的路径。
val：要插入的元素。
path和val的组合可以重复多次，用于插入多个元素。

SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
-- 在下标1处添加元素x，结果：["a", "x", {"b": [1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
-- 没有100个元素，在最后插入，结果： ["a", {"b": [1, 2]}, [3, 4], "x"]
SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
-- 结果：["a", {"b": ["x", 1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
-- 结果：["a", {"b": [1, 2]}, [3, "y", 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');

-- 早期的修改会影响数组中后续元素的位置，因此同一个JSON_ARRAY_INSERT()调用中的后续路径应该考虑这一点。在最后一个示例中，第二个路径没有插入任何内容，因为在第一次插入之后，该路径不再匹配任何内容。
-- 结果：["x", "a", {"b": [1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');

4、JSON_INSERT()插入新值

格式：JSON_INSERT(json_doc, path, val[, path, val] …)
插入不存在的key的值，已经存在的不修改。

JSON_INSERT() 函数用于在 JSON 文档中插入新值。它接受一个或多个参数：

json_doc：要插入值的 JSON 数据。
path：指定在 JSON 数据中插入值的路径。
val：要插入的值。
path和val的组合可以重复多次，用于插入多个值。

仅当指定位置或指定 KEY 的值不存在时，才执行插入操作。另外，如果指定的 path 是数组下标，且 json_doc 不是数组，该函数首先会将 json_doc 转化为数组，然后再插入新值。

SET @j = '{ "a": 1, "b": [2, 3]}';
-- a已经存在则忽略，c不存在则添加，结果：{"a": 1, "b": [2, 3], "c": "[true, false]"}
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
-- 上面插入的c是一个带引号的字符串，想要插入一个数组，必须进行转换，结果：{"a": 1, "b": [2, 3], "c": [true, false]}
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON));

-- 下标0位置已经有值了，不会插入，结果：1
select json_insert('1','$[0]',"10");
-- 结果：[1, "10"]
select json_insert('1','$[1]',"10");
-- 结果：["1", "2", "10"]
select json_insert('["1","2"]','$[2]',"10");

5、JSON_MERGE()合并json

格式：JSON_MERGE(json_doc, json_doc[, json_doc] …)

合并两个或多个JSON文档。JSON_MERGE_PRESERVE()的同义词；在MySQL 8.0.3中已弃用，在未来版本中可能会被删除。
推荐使用JSON_MERGE_PRESERVE()

-- 不是对象，结果：[true, false]
SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
-- 都是对象，结果：{"id": 47, "name": "x"}
SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
-- 都不是对象，取第二个，结果：true
SELECT JSON_MERGE_PATCH('1', 'true');
-- 第一个不是对象，取第二个 ，结果：{"id": 47}
SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
-- 第二个覆盖第一个，结果：{"a": 3, "b": 2, "c": 4}
SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
-- 结果：{"a": 5, "b": 2, "c": 4, "d": 6}
SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }');
-- 第二个有null，会删除该key，结果：{"a": 1}
SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
-- 嵌套json也可以合并，结果：{"a": {"x": 1, "y": 2}}
SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');

6、JSON_MERGE_PATCH()合并json

MySQL 8.0.3 引入的，用来合并多个 JSON 文档。其合并规则如下：
1、如果两个文档不全是 JSON 对象，则合并后的结果是第二个文档。

2、如果两个文档都是 JSON 对象，且不存在着同名 KEY，则合并后的文档包括两个文档的所有元素，如果存在着同名 KEY，则第二个文档的值会覆盖第一个。

-- 不是对象，结果：[true, false]
SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
-- 都是对象，结果：{"id": 47, "name": "x"}
SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
-- 都不是对象，取第二个，结果：true
SELECT JSON_MERGE_PATCH('1', 'true');
-- 第一个不是对象，取第二个 ，结果：{"id": 47}
SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
-- 第二个覆盖第一个，结果：{"a": 3, "b": 2, "c": 4}
SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
-- 结果：{"a": 5, "b": 2, "c": 4, "d": 6}
SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }');
-- 第二个有null，会删除该key，结果：{"a": 1}
SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
-- 嵌套json也可以合并，结果：{"a": {"x": 1, "y": 2}}
SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');

7、JSON_MERGE_PRESERVE()合并json

MySQL 8.0.3 引入的，用来代替 JSON_MERGE。也是用来合并文档，但合并规则与 JSON_MERGE_PATCH 有所不同。
1、两个文档中，只要有一个文档是数组，则另外一个文档会合并到该数组中。
2、两个文档都是 JSON 对象，若存在着同名 KEY ，第二个文档并不会覆盖第一个，而是会将值 append 到第一个文档中。

8、JSON_REMOVE()删除元素

格式：JSON_REMOVE(json_doc, path[, path] …)
删除 JSON 文档指定位置的元素。

SET @j = '["a", ["b", "c"], "d"]';
-- 删除下标为1的元素，结果：["a", "d"]
SELECT JSON_REMOVE(@j, '$[1]');

set @j = '{ "a": 1, "b": [2, 3]}';
-- 删除a元素，结果：{"b": [2, 3]}
select json_remove(@j, '$.a');

set @j = '["a", ["b", "c"], "d", "e"]';
-- 删除多个元素，删除1下标之后，下标移动结果之后再删除下标2位置，结果：["a", "d"]
select json_remove(@j, '$[1]','$[2]');
-- 结果：["a", "e"]
select json_remove(@j, '$[1]','$[1]');

9、JSON_REPLACE()替换元素

语法：JSON_REPLACE(json_doc, path, val[, path, val] …)
替换已经存在的值。不存在的值不做影响。

SET @j = '{ "a": 1, "b": [2, 3]}';
-- 对象替换，结果：{"a": 10, "b": [2, 3]}
SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');

-- 数组替换，结果：[1, "a", 4, "b"]
select json_replace('[1, "a", 3, "b"]', '$[2]', 4, '$[8]', 8);

10、JSON_SET()插入并替换

格式：JSON_SET(json_doc, path, val[, path, val] …)
插入新值，并替换已经存在的值。

换言之，如果指定位置或指定 KEY 的值不存在，会执行插入操作，如果存在，则执行更新操作。

注意JSON_SET、JSON_INSERT、JSON_REPLACE的区别。

SET @j = '{ "a": 1, "b": [2, 3]}';
-- 结果：{"a": 10, "b": [2, 3], "c": "[true, false]"}
SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
-- 结果：{"a": 1, "b": [2, 3], "c": "[true, false]"}
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
-- 结果：{"a": 10, "b": [2, 3]}
SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');

2.6JSON索引使用

同 TEXT，BLOB 字段一样，JSON 字段不允许直接创建索引。
即使支持，实际意义也不大，因为我们一般是基于文档中的元素进行查询，很少会基于整个 JSON 文档。
对文档中的元素进行查询，就需要用到 MySQL 5.7 引入的虚拟列及函数索引。

1、虚拟列索引

当 JSON 数据量非常大，用户希望对 JSON 数据进行有效检索时，可以利用 MySQL 的函数索引功能对 JSON 中的某个字段进行索引，具体方式是先创建一个虚拟列，再对虚拟列创建索引。

/*原本执行计划走的全表*/
 
mysql> explain select * from student where info->>"$.age" = 13 ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
 
/*创建age虚拟列*/
 
mysql> ALTER TABLE student ADD COLUMN age INT as (info->>"$.age");
 
 
/*创建age索引*/
 
mysql> create index idx_age on student(age);
 
 
/*执行计划走新加的索引*/
 
mysql> explain select * from student where info->>"$.age" = 13 ;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_age | idx_age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+

2、 Multi-Valued Index

从MySQL8.0.17开始，InnoDB支持多值索引（Multi-Valued Index）。多值索引是在存储JSON数组的列上定义的辅助索引，对于MEMBER OF，JSON_CONTAINS，JSON_OVERLAPS 等函数可以利用多值索引进行性能优化。

JSON对象的使用(多值索引是官方针对JSON数组的辅助索引，但是根据实践也可以针对JSON对象使用，但是只适用于member of`函数)

mysql> select * from student;
+----+---------------------------------------------+
| id | info |
+----+---------------------------------------------+
| 1 | {"age": 13, "sex": "F", "city": "beijing"} |
| 2 | {"age": 14, "sex": "M", "city": "suzhou"} |
| 3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
+----+---------------------------------------------+
 
 
/*没创建多值索引前走的是全表扫描*/
mysql> explain select * from student where 13 member of(info->'$.age');
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
 
/*创建info->'$.age'的多值索引*/
mysql> alter table student add index idx_age((cast(info->'$.age' as unsigned array)));
 
 
/*memberof函数可以走多值索引*/
mysql> explain select * from student where 13 member of(info->'$.age');
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | student2 | NULL | ref | idx_age | idx_age | 9 | const | 1 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
 
 
/*指定JSON对象过滤无法利用多值索引*/
mysql> explain select * from student where info->'$.age' = 13;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
 
/*JSON_CONTAINS函数无法利用多值索引过滤JSON对象*/
mysql> explain SELECT * FROM student2 where JSON_CONTAINS(info, '13', '$.age');
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
 
/*JSON_OVERLAPS函数也无法利用多值索引过滤JSON对象*/
mysql> explain select * from student where JSON_OVERLAPS(info,'{"age": 13}');
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------

JSON数组的使用：三种JSON函数都可以利用多值索引。

mysql> select * from student;
+----+--------------+
| id | info |
+----+--------------+
| 1 | [1, 2, 5, 9] |
| 2 | [2, 5, 6, 8] |
| 3 | [5, 3, 8, 9] |
| 4 | [1, 2, 7, 8] |
+----+--------------+
 
 
/*没创建多值索引前走的是全表扫描*/
mysql> EXPLAIN SELECT * FROM student WHERE JSON_CONTAINS(info, '[3]');
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
 
/*创建info列的多值索引，注意如果这个地方改为idx_info((cast((info->"$") as unsigned array)))，则后续所有的函数都要是info->"$"，否则走不了索引*/
mysql> ALTER TABLE student ADD INDEX idx_info((cast(info as unsigned array)));
 
 
/*JSON_CONTAINS函数可以走新建的多值索引*/
mysql> EXPLAIN SELECT * FROM student WHERE JSON_CONTAINS(info, '[3]');
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | range | idx_info | idx_info | 9 | NULL | 4 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
 
 
/*JSON_CONTAINS函数可以走新建的多值索引*/
mysql> explain SELECT * FROM student WHERE JSON_CONTAINS(info, '[3]');
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | range | idx_info | idx_info | 9 | NULL | 1 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
 
 
/*MEMBER OF函数可以走新建的多值索引*/
mysql> explain SELECT * FROM student WHERE 3 MEMBER OF(info);
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ref | idx_info | idx_info | 9 | const | 1 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

2.7其他JSON函数

1、JSON_TABLE()列转行

语法：JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
MySQL 8.0支持这样一个函数，JSON_TABLE()，从 JSON 文档中提取数据并以表格的形式返回。

JSON_TABLE() 函数用于将 JSON 数据转换为关系表格。它接受以下参数：

expr：要转换的 JSON 数据。
path：指定在 JSON 数据中提取数据的路径。
COLUMNS：定义要提取的列及其数据类型。
alias：为生成的表格指定别名。

下面是一个使用 JSON_TABLE() 的示例：

假设我们有一个名为 students 的表，其中包含以下 JSON 数据：

[
  {
    "name": "张三",
    "age": 20,
    "hobbies": ["篮球", "足球"],
    "scores": [90, 80]
  },
  {
    "name": "李四",
    "age": 22,
    "hobbies": ["游泳", "跑步"],
    "scores": [85, 78]
  },
  {
    "name": "王五",
    "age": 21,
    "hobbies": ["篮球", "羽毛球"],
    "scores": [92, 88]
  }
]

现在我们想要将这个 JSON 数据转换为关系表格，并提取学生的姓名、年龄和爱好。我们可以使用 JSON_TABLE() 函数来实现这个需求：

SELECT * FROM JSON_TABLE(
  (SELECT json_data FROM students),
  '$[*]' COLUMNS (
    name VARCHAR(255) PATH '$.name',
    age INT PATH '$.age',
    hobbies VARCHAR(255) PATH '$.hobbies[*]'
  )
);

2、JSON_SCHEMA_VALID()验证json

语法：JSON_SCHEMA_VALID(schema,document)
判断 document （ JSON 文档）是否满足 schema （ JSON 对象）定义的规范要求。完整的规范要求可参考 Draft 4 of the JSON Schema specification （https://json-schema.org/specification-links.html#draft-4）。如果不满足，可通过 JSON_SCHEMA_VALIDATION_REPORT() 获取具体的原因。

它的要求如下：
1、document 必须是 JSON 对象。
2、JSON 对象必需的两个属性是 latitude 和 longitude。
3、latitude 和 longitude 必须是数值类型，且两者的大小分别在 -90 ～ 90，-180 ～ 180 之间。

3、JSON_PRETTY()格式化输出

语法：JSON_PRETTY(json_val)
将 JSON 格式化输出。

4、json_arrayagg()函数

三、JSON应用场景

3.1教师信息数据库设计

3.2毕业设计评分数据库设计

3.3建议使用MySQL JSON类型的场景

仅用于存储JSON对象/数组数据，尽量避免在数据库/业务逻辑操作其中的数据
涉及元素数据查询时依然创建索引
全局更新，而非更新对象属性值
数组仅用于存储查询，添加/移除元素语句复杂

MySQL JSON字段映射为Java String类型。因此，属性不建议在后端解析为对象操作，仅作为数据从前端传递到后端，再存储至数据库。

烟雨平生9527

关注

23
点赞
踩
25

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL---JSON的用法讲解

JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 或 TEXT ）来保存 JSON 文档。MySQL 8.0版本中增加了对JSON类型的索引支持。可以使用CREATE INDEX语句创建JSON类型的索引，提高JSON类型数据的查询效率。存储JSON文档所需的空间与存储LONGBLOB或LONGTEXT所需的空间大致相同。在MySQL 8.0.13之前，JSON列不能有非空的默认值。
复制链接

扫一扫