MySQL 8.0 JSON增强到底有多强？（一）

SEian.G

发布于 2021-07-07 15:00:24

6.9K0

发布于 2021-07-07 15:00:24

我们都知道，从5.7版本开始，MySQL 支持 RFC7159定义的原生JSON数据类型，该类型支持对JSON文档中的数据的有效访问。关于MySQL 8.0 JSON数据类型，后面准备通过一个系列的文章来进行详细的介绍，这样方便大家对MySQL中JSON数据类型的使用有更好的了解；

很多业务人员在用 JSON 数据类型时会遇到各种各样的问题，其中最容易犯的误区就是将类型 JSON 简单理解成字符串类型。但当你看完今天的内容之后，会真正认识到 JSON 数据类型的威力，从而在实际工作中更好地存储非结构化的数据。

与在字符串列中存储JSON格式的字符串相比，JSON数据类型具有以下优势：

* 自动验证存储在JSON列中的JSON文档。无效的文档会产生错误。 * 优化的存储格式。JSON列中存储的JSON文档将转换为内部格式，以允许快速读取文档元素。当服务器稍后必须读取以该二进制格式存储的JSON值时，则无需从文本表示形式解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

MySQL 8.0还支持RFC 7396中定义的JSON合并补丁格式关系型的结构化存储存在一定的弊端，因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中，或许需要扩展单个列的描述功能，这时，如果能用好 JSON 数据类型，那就能打通关系型和非关系型数据的存储之间的界限，为业务提供更好的架构选择。

一、JSON数据类型

存储在JSON列中的任何JSON文档的大小都限于max_allowed_packet系统变量的值。（当服务器内部在内存中操作JSON值时，该值可以大于此值；当服务器存储一个JSON文档时，这个限制就适用了。）可以使用JSON_STORAGE_SIZE()函数获取存储JSON文档所需的空间量；
在MySQL 8.0.13之前，JSON列不能具有非NULL默认值。
JSON列与其他二进制类型的列一样，列也不直接建立索引；相反，可以在生成的列上创建索引，以从该JSON列中提取标量值。
在MySQL 8.0.17及更高版本中，InnoDB 存储引擎支持JSON数组上的多值索引。后面系列的文章会详细进行介绍

二、创建JSON值

JSON数组包含用逗号分隔并包含在[ ] 字符中的值的列表：

["abc", 10, null, true, false]

JSON对象包含一组键值对，以逗号分隔，并包含在{ }字符内：

{"k1": "value", "k2": 10}

如示例所示，JSON数组和对象可以包含字符串或数字的标量值，JSON空文字或JSON布尔值true或false文字。JSON对象中的键必须是字符串。还允许使用时间（日期，时间或日期时间）标量值：

["12:18:29.000000", "2021-05-10", "2021-05-10 12:18:29.000000"]

JSON数组元素和JSON对象键值中允许嵌套：

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

还可以从MySQL提供的许多函数中获取JSON值，也可以使用CAST（value as JSON）将其他类型的值强制转换为JSON类型；后面会专门介绍JSON的相关函数

JSON 如果该值是有效的JSON值，则尝试将值插入到列中会成功，但如果不是，则尝试失败：

mysql>CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.01 sec)
 
mysql>INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.00 sec)
 
mysq>INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 6 in value for column 't1.jdoc'.

三、JSON合并

MySQL 8.0.3（及更高版本）支持两种合并算法，分别由函数 JSON_MERGE_PRESERVE()和实现 JSON_MERGE_PATCH()。它们在处理重复键的方式上有所不同：JSON_MERGE_PRESERVE()保留重复键的值，而 JSON_MERGE_PATCH()丢弃除最后一个值以外的所有值。

1、合并数组

在组合多个数组的上下文中，这些数组合并到单个数组中。JSON_MERGE_PRESERVE()通过将稍后命名的数组连接到第一个数组的末尾来实现这一点。JSON_MERGE_PATCH()将每个参数视为一个由单个元素组成的数组（因此索引为0），然后应用“last duplicate key wins”逻辑仅选择最后一个参数。可以比较此查询显示的结果：

8.0支持，5.7不支持
 
mysql>SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]
1 row in set (0.00 sec)

2、合并对象

合并时，多个对象将产生一个对象。JSON_MERGE_PRESERVE()通过组合数组中该键的所有唯一值来处理具有相同键的多个对象；然后将此数组用作结果中该键的值。JSON_MERGE_PATCH() 丢弃从左到右查找重复键的值，以便结果仅包含该键的最后一个值。以下查询说明重复键的结果不同a：

mysql>SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G
*************************** 1. row ***************************
Preserve: {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3}
   Patch: {"a": 4, "b": 2, "c": 5, "d": 3}
1 row in set (0.01 sec)

在需要数组值的上下文中使用的非数组值是自动包装的：该值被[和]字符包围，以将其转换为数组。在下面的语句中，每个参数都自动包装为一个数组（[1]，[2]）。然后合并这些结果以生成单个结果数组。与前两种情况一样，JSON_MERGE_PRESERVE()组合具有相同键的值；JSON_MERGE_PATCH()丢弃除最后一个键以外的所有重复键的值，如下所示：

mysql>SELECT JSON_MERGE_PRESERVE('1', '2') AS Preserve,JSON_MERGE_PATCH('1', '2') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2]   Patch: 2
1 row in set (0.00 sec)

数组和对象值是通过将对象自动包装为数组并通过组合值或根据合并函数的选择（JSON_MERGE_PRESERVE()JSON_MERGE_PATCH()）选择“last duplicate key wins”来合并数组，从而合并数组和对象值，如以下示例所示：

mysql>SELECT JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch\G
*************************** 1. row ***************************
Preserve: [10, 20, {"a": "x", "b": "y"}]
   Patch: {"a": "x", "b": "y"}
1 row in set (0.00 sec)