MySQL 处理JSON字符串

创建JSON值

JSON数组包含由逗号分隔的值列表，封闭在[和]内字符数：

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

JSON对象包含一组键值对，逗号并括在{和 }人物：

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

如示例所示，JSON数组和对象可以包含标量值是字符串或数字，JSON null literal，或者JSON boolean true或false literal。钥匙 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]}

您还可以从许多函数中获取JSON值由MySQL为此提供（请参见第12.17.2节，将其他类型的值转换为JSON类型，使用 CAST(value AS JSON）（请参见在JSON和非JSON值之间转换。下一有几段描述了MySQL如何处理JSON值作为输入提供。

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

尝试将值插入到JSON 如果值是有效的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,'.

位置“at position 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使用 utf8mb4字符集和 utf8mb4_bin整理。其他字符串字符集将转换为utf8mb4，必要(For字符串在ascii或 utf8mb3字符集，没有转换是因为ascii和 utf8mb3是的子集 utf8mb4.）

作为使用文字字符串编写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)

MySQL 8.3 还使用 JSON_MERGE_PATCH（）函数支持 RFC 7396 中定义的 JSON Merge Patch 格式。有关示例和详细信息，请参阅此函数的说明以及 JSON 值的规范化、合并和自动包装。

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


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

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

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


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

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


mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

区分大小写也适用于 JSON null、true 和 false 文本，这些文本必须始终以小写形式编写：


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 NULL、TRUE和 FALSE字面值，可以用任何字体：


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对象插入到 facts表是使用MySQL JSON_OBJECT()函数。在这在这种情况下，必须使用反斜杠对每个引号字符进行转义，如显示在这里：


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对象的任何一种方式，你可以看到 JSON列值中存在反斜杠，做一个简单的SELECT，像这样：


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

要查找这个以吉祥物为键的特定句子，您可以使用列路径运算符 ->，如下所示：


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_UNQUOTE()函数有关此模式对转义的影响的更多信息 JSON值中的字符。

秒客网

MySQL 处理JSON字符串

创建JSON值

相关文章