MySQL数据类型 - JSON数据类型（1）

原创

数据杂货铺 2020-12-07 16:55:40 ©著作权

文章标签 Java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者数据杂货铺的原创作品，请联系作者获取转载授权，否则将追究法律责任

JSON 数据类型

MySQL支持由RFC 7159定义的原生JSON数据类型，它支持对JSON（JavaScript Object Notation）文档数据的高效访问。与在字符串列中存储JSON格式字符串相比，JSON数据类型提供了以下优势：

●自动验证存储在JSON列中的JSON文档。若是无效文档会报错。

●优化的存储格式。存储在JSON列中的JSON文档被转换为允许快速读取文档元素的内部格式。当服务器以后必须读取以这种二进制格式存储的JSON值时，不需要从文本表示中解析该值。二进制格式的结构使服务器能够直接按键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

MySQL 8.0还使用JSON_MERGE_PATCH()函数支持RFC 7396中定义的JSON Merge Patch格式。

存储JSON文档所需的空间与LONGBLOB或LONGTEXT大致相同。请记住，存储在JSON列中的任何JSON文档的大小都限制为max_allowed_packet系统变量的值。（当服务器在内存中操作一个JSON值时，它可以大于这个值；当服务器存储它时，这个限制适用。）可以使用JSON_STORAGE_SIZE()函数获得存储JSON文档所需的空间量；注意，对于JSON列，在进行任何列更新之前，存储大小(此函数返回的值)就是列大小（请参阅本节后面对JSON部分更新优化的讨论）。

在MySQL8.0.13之前，JSON列的默认值不能为非空。

除了JSON数据类型之外，还提供了一组SQL函数来支持对JSON值的操作，例如创建、操作和搜索。下面的讨论展示了这些操作的示例。

也提供了一组用于操作GeoJSON值的空间函数。

JSON列与其他二进制类型的列一样，不直接编制索引；相反，可以在从JSON列中提取标量值的生成列上创建索引。

MySQL优化器还会在虚拟列上查找与JSON表达式匹配的兼容索引。

在MySQL8.0.17及更高版本中，InnoDB存储引擎支持JSON数组上的多值索引。

MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函数，包括在JSON列生成的列上创建索引，以解决无法为JSON列创建索引的问题。每个NDB表最多支持3个JSON列。

JSON值局部更新

在MySQL 8.0中，优化器可以对JSON列执行局部更新，而不是删除旧文档并将新文档全部写入该列。对于满足以下条件的更新，可以执行此优化：

●正在更新的列已声明为JSON。

●UPDATE语句使用三个函数JSON_SET()、JSON_REPLACE()或JSON_REMOVE()中的任何一个来更新列。列值的直接赋值（例如UPDATE mytable SET jcol = '{"a": 10, "b": 25}'）不能作为局部更新执行。

在一个UPDATE语句中对多个JSON列的更新可以用这种方式进行优化；MySQL只能对那些使用刚才列出的三个函数更新值的列执行局部更新。

●输入列和目标列必须是同一列；UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100)等语句不能作为局部更新执行。

只要输入列和目标列相同，更新可以使用之前列出的任何函数任意组合的嵌套调用。

●所有更改都将用新的数组或对象值替换现有的数组或对象值，并不会向父对象或数组添加任何新元素。

●被替换的值必须至少与替换值一样大。换句话说，新值不能大于旧值。

当以前的局部更新留下了足够的空间时，会出现例外情况。可以使用函数JSON_STORAGE_FREE()查看JSON列的任何局部更新释放了多少空间。

这种局部更新可以使用节省空间的紧凑格式写入二进制日志；可以通过将binlog_row_value_options系统变量设置为PARTIAL_JSON来实现这一点。有关详细信息，请参阅此变量的说明。

接下来的几节将提供有关JSON值的创建和操作的基本信息。

创建JSON值

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

JSON对象包含一组由逗号分隔并包含在{和}字符中的键值对：

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

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

可以从MySQL提供的许多函数中获取JSON值，也可以使用CAST(value AS JSON)将其他类型的值强制转换为JSON类型。下面几段描述MySQL如何处理输入提供的JSON值。

在MySQL中，JSON值被写成字符串。MySQL解析上下文中使用的任何需要JSON值的字符串，如果它不是有效的JSON值，则会生成错误。这些上下文包括向JSON数据类型的列中插入值，将参数传递给需要JSON值的函数（通常在MySQL JSON函数的文档中显示为json_doc或json_val），如下例所示：

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

此类错误消息中"位置N"的位置是从0开始的，但是应该被视为一个值中哪里实际出现问题的粗略指示。

JSON_TYPE()函数需要一个JSON参数，并尝试将其解析为JSON值。如果有效，则返回值的JSON类型，否则将产生错误：

MySQL使用utf8mb4字符集和utf8mb4_bin排序规则处理JSON上下文中使用的字符串。其他字符集中的字符串根据需要转换为utf8mb4。（对于ascii或utf8字符集中的字符串，不需要转换，因为ascii和utf8是utf8mb4的子集。）

除了用文本字符串编写JSON值，还可以用函数把组件元素合成JSON值。JSON_ARRAY()接受一个值列表（可能为空），并返回一个包含这些值的JSON数组：

JSON_OBJECT()接收键值对列表（可能为空），并返回包含这些键值对的JSON对象：

JSON_MERGE_PRESERVE()接受两个或多个JSON文档并返回组合结果：

JSON值可以分配给用户定义的变量：

但是，用户定义的变量不能是JSON数据类型，因此尽管前面示例中的@j看起来像一个JSON值，并且具有与JSON值相同的字符集和排序规则，但是它没有JSON数据类型。相反，JSON_OBJECT()的结果在分配给变量时转换为字符串。

转换JSON值生成的字符串的字符集为utf8mb4，排序规则为utf8mb4_bin：

因为utf8mb4_bin是一种二进制排序规则，所以JSON值的比较区分大小写。

区分大小写还适用于JSON null、true和false字面量，它们必须始终是小写形式：

JSON文本的区分大小写不同于SQL NULL、TRUE和FALSE文本，后者可以用任何字母大小写形式：

有时可能需要或希望在JSON文档中插入引号字符（" or '）。在本例中，假设您希望将一些JSON对象插入到使用以下SQL语句创建的表中，这些对象包含关于MySQL的一些事实的语句（每个语句都与适当的关键字成对出现）：

在这些关键词句子对中有一对儿如下：

将其作为JSON对象插入facts表的一种方法是使用MySQL JSON_OBJECT()函数。在这种情况下，必须使用反斜杠转义每个引号字符，如下所示：

如果将值作为JSON对象文本插入，则此方法的工作方式不同，在这种情况下，必须使用双反斜杠转义序列，如下所示：

使用双反斜杠可以防止MySQL执行转义序列处理，而是将字符串文本传递给存储引擎进行处理。以上述任一方式插入JSON对象后，通过执行简单的SELECT，可以看到反斜杠出现在JSON列值中，如下所示：

要查找使用 mascot 作为键的特定句子，可以使用列路径运算符->，如下所示：

这将保留反斜杠以及周围的引号。要使用 mascot 作为键显示所需的值，但不包括周围的引号或任何转义符，请使用内联路径运算符->>，如下所示：

注意

如果启用了 NO_BACKSLASH_ESCAPES 服务器SQL模式，则前面的示例处理方式会不同。如果设置了此模式，则可以使用单个反斜杠而不是双反斜杠来插入JSON对象文本，并且反斜杠会保留。如果在执行插入操作时使用JSON_OBJECT()函数，并且设置了此模式，则必须交替使用单引号和双引号，如下所示：