MySQL中的JSON数据类型 2 JSON值的基本信息

最新推荐文章于 2024-05-15 10:46:25 发布

Risehuxyc

最新推荐文章于 2024-05-15 10:46:25 发布

阅读量565

点赞数

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

原文链接：https://dev.mysql.com/doc/refman/8.0/en/json.html#json-values

版权

MySQL 专栏收录该内容

35 篇文章 2 订阅

订阅专栏

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

创建 JSON 值

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

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

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

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

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

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

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

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

您还可以从MySQL为此目的提供的许多函数中获取JSON值（请参见第12.18.2节"创建JSON值的函数"），以及通过使用CAST（值为JSON）将其他类型的值转换为类型（请参阅在JSON和非JSON值之间转换）。接下来的几个段落描述了MySQL如何处理作为输入提供的JSON值。

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

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

mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.

在这种错误消息中，"在位置N"的位置是基于 0 的，但应将其视为值中问题实际发生位置的粗略指示。

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

mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+

mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

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

作为使用文本字符串编写 JSON 值的替代方法，存在用于从组件元素编写 JSON 值的函数。 JSON_ARRAY（）获取一个（可能为空）值列表，并返回包含这些值的 JSON 数组：

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

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

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON_MERGE_PRESERVE（）采用两个或多个 JSON 文档并返回组合结果：

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.00 sec)

有关合并规则的信息，请参阅JSON 值的规范化、合并和自动打包。

（MySQL 8.0.3及更高版本也支持JSON_MERGE_PATCH（），其行为略有不同。有关这两个函数之间差异的信息，请参阅JSON_MERGE_PATCH（）与 JSON_MERGE_PRESERVE（）的比较。

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

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.00 sec)

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

通过转换 JSON 值生成的字符串具有字符集和排序规则：utf8mb4utf8mb4_bin

mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+

由于是二进制排序规则，因此 JSON 值的比较区分大小写。utf8mb4_bin

mysql> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+

区分大小写也适用于 JSON 、和文本，它们必须始终以小写形式编写：nulltruefalse

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                  0 |
+--------------------+--------------------+--------------------+

mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null                 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
"Invalid value." at position 0 in 'NULL'.

JSON 文本区分大小写与 SQL 和文本区分大小写，后者可以用任何字母大小写：NULLTRUEFALSE

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
|            1 |            1 |            1 |
+--------------+--------------+--------------+

有时，在 JSON 文档中插入引号字符（或）可能是必要或可取的。对于此示例，假设您要将一些 JSON 对象（其中包含表示陈述有关 MySQL 的一些事实的句子的字符串）插入到使用此处显示的 SQL 语句创建的表中，每个句子都与适当的关键字配对："'

mysql> CREATE TABLE facts (sentence JSON);

在这些关键字 - 句子对中，有一个：

mascot: The MySQL mascot is a dolphin named "Sakila".

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

mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

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

mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

使用双反斜杠可防止MySQL执行转义序列处理，而是导致它将字符串文本传递到存储引擎进行处理。以刚才显示的任何一种方式插入 JSON 对象后，您可以通过执行简单的SELECT来查看 JSON 列值中存在反斜杠，如下所示：

mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+

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

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

注意

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

mysql> INSERT INTO facts VALUES
     > (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));

有关此模式对 JSON 值中转义字符的影响的更多信息，请参阅JSON_UNQUOTE（）函数的说明。

Risehuxyc

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录