MySQL :: MySQL 8.4 参考手册 :: 14.17.7 JSON 模式验证函数

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 14 函数和运算符 / 14.17 JSON 函数 / 14.17.7 JSON 模式验证函数

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 95%

14.17.7 JSON 模式验证函数14.17.7 JSON Schema Validation Functions

MySQL 支持根据 JSON Schema 规范第 4 版草案验证 JSON 文档。可以使用本节中详细介绍的任一函数完成此操作，这两个函数都接受两个参数：一个 JSON schema 和一个要根据该 schema 验证的 JSON 文档。JSON_SCHEMA_VALID() 如果文档符合 schema 则返回 true，否则返回 false；JSON_SCHEMA_VALIDATION_REPORT() 会提供以 JSON 格式的验证报告。MySQL supports validation of JSON documents against JSON schemas conforming to Draft 4 of the JSON Schema specification. This can be done using either of the functions detailed in this section, both of which take two arguments, a JSON schema, and a JSON document which is validated against the schema. JSON_SCHEMA_VALID() returns true if the document validates against the schema, and false if it does not; JSON_SCHEMA_VALIDATION_REPORT() provides a report in JSON format on the validation.

两个函数处理空值或无效输入的方式如下：Both functions handle null or invalid input as follows:

如果至少有一个参数为 NULL，函数将返回 NULL。If at least one of the arguments is NULL, the function returns NULL.
如果至少有一个参数不是有效的 JSON，函数将引发错误 (ER_INVALID_TYPE_FOR_JSON)If at least one of the arguments is not valid JSON, the function raises an error (ER_INVALID_TYPE_FOR_JSON)
此外，如果 schema 不是有效的 JSON 对象，函数将返回 ER_INVALID_JSON_TYPE。In addition, if the schema is not a valid JSON object, the function returns ER_INVALID_JSON_TYPE.

MySQL 支持 JSON schema 中的 required 属性，以强制要求包含必要的属性（参见函数描述中的示例）。MySQL supports the required attribute in JSON schemas to enforce the inclusion of required properties (see the examples in the function descriptions).

MySQL 在 JSON 模式中支持 id、$schema、description 和 type 属性，但不要求使用这些属性。MySQL supports the id, $schema, description, and type attributes in JSON schemas but does not require any of these.

MySQL 不支持 JSON 模式中的外部资源；使用 $ref 关键字会导致 JSON_SCHEMA_VALID() 以 ER_NOT_SUPPORTED_YET 错误失败。MySQL does not support external resources in JSON schemas; using the $ref keyword causes JSON_SCHEMA_VALID() to fail with ER_NOT_SUPPORTED_YET.

Note

MySQL 支持 JSON 模式中的正则表达式模式，它支持但会静默忽略无效的模式（有关示例，请参见 JSON_SCHEMA_VALID() 的描述）。MySQL supports regular expression patterns in JSON schema, which supports but silently ignores invalid patterns (see the description of JSON_SCHEMA_VALID() for an example).

这些功能在下面的列表中有详细描述：These functions are described in detail in the following list:

JSON_SCHEMA_VALID(schema,document)
验证 JSON document 是否符合 JSON schema。schema 和 document 都是必需的。模式必须是有效的 JSON 对象；文档必须是有效的 JSON 文档。在满足这些条件的前提下：如果文档符合模式，函数返回 true（1）；否则返回 false（0）。Validates a JSON document against a JSON schema. Both schema and document are required. The schema must be a valid JSON object; the document must be a valid JSON document. Provided that these conditions are met: If the document validates against the schema, the function returns true (1); otherwise, it returns false (0).
在这个示例中，我们将用户变量 @schema 设置为地理坐标的 JSON 模式的值，将另一个变量 @document 设置为包含一个该坐标的 JSON 文档的值。然后，我们通过将它们作为 JSON_SCHEMA_VALID() 的参数来验证 @document 是否符合 @schema：In this example, we set a user variable @schema to the value of a JSON schema for geographical coordinates, and another one @document to the value of a JSON document containing one such coordinate. We then verify that @document validates according to @schema by using them as the arguments to JSON_SCHEMA_VALID():
```
mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '>}';
Query OK, 0 rows affected (0.01 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)
```
由于 @schema 包含 required 属性，我们可以将 @document 设置为一个在其他方面有效但不包含必需属性的值，然后将其与 @schema 进行测试，如下所示：Since @schema contains the required attribute, we can set @document to a value that is otherwise valid but does not contain the required properties, then test it against @schema, like this:
```
mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)
```
如果我们现在将@schema的值设置为相同的 JSON 模式，但不带 required 属性，则@document将被验证通过，因为它是一个有效的 JSON 对象，即使它不包含任何属性，如下所示：If we now set the value of @schema to the same JSON schema but without the required attribute, @document validates because it is a valid JSON object, even though it contains no properties, as shown here:
```
mysql> SET @schema = '{
    '> "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> }
    '>}';
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)
```
JSON_SCHEMA_VALID() 与 CHECK 约束。 JSON_SCHEMA_VALID()也可以用来强制执行CHECK约束。JSON_SCHEMA_VALID() and CHECK constraints. JSON_SCHEMA_VALID() can also be used to enforce CHECK constraints.
考虑创建的表geo，如这里所示，其中有一个 JSON 列coordinate，表示地图上的纬度和经度点，其受作为JSON_SCHEMA_VALID()调用参数使用的 JSON 模式约束，此调用被作为该表上CHECK约束的表达式传入：Consider the table geo created as shown here, with a JSON column coordinate representing a point of latitude and longitude on a map, governed by the JSON schema used as an argument in a JSON_SCHEMA_VALID() call which is passed as the expression for a CHECK constraint on this table:
```
mysql> CREATE TABLE geo (
    ->     coordinate JSON,
    ->     CHECK(
    ->         JSON_SCHEMA_VALID(
    ->             '{
    '>                 "type":"object",
    '>                 "properties":{
    '>                       "latitude":{"type":"number", "minimum":-90, "maximum":90},
    '>                       "longitude":{"type":"number", "minimum":-180, "maximum":180}
    '>                 },
    '>                 "required": ["latitude", "longitude"]
    '>             }',
    ->             coordinate
    ->         )
    ->     )
    -> );
Query OK, 0 rows affected (0.45 sec)
```
Note
因为 MySQL 的 CHECK 约束不能包含对变量的引用，所以在使用它为表指定此类约束时，必须将 JSON 模式内联传递给 JSON_SCHEMA_VALID()。Because a MySQL CHECK constraint cannot contain references to variables, you must pass the JSON schema to JSON_SCHEMA_VALID() inline when using it to specify such a constraint for a table.
我们将表示坐标的 JSON 值分配给三个变量，如下所示：We assign JSON values representing coordinates to three variables, as shown here:
```
mysql> SET @point1 = '{"latitude":59, "longitude":18}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @point2 = '{"latitude":91, "longitude":0}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @point3 = '{"longitude":120}';
Query OK, 0 rows affected (0.00 sec)
```
这些值中的第一个是有效的，如以下 INSERT 语句所示：The first of these values is valid, as can be seen in the following INSERT statement:
```
mysql> INSERT INTO geo VALUES(@point1);
Query OK, 1 row affected (0.05 sec)
```
第二个 JSON 值无效，因此不符合约束，如下所示：The second JSON value is invalid and so fails the constraint, as shown here:
```
mysql> INSERT INTO geo VALUES(@point2);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
```
您可以通过执行latitude语句获取有关故障性质的精确信息——在本例中，即SHOW WARNINGS值超过了模式中定义的最大值：You can obtain precise information about the nature of the failure—in this case, that the latitude value exceeds the maximum defined in the schema—by issuing a SHOW WARNINGS statement:
```
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Error
   Code: 3934
Message: The JSON document location '#/latitude' failed requirement 'maximum' at
JSON Schema location '#/properties/latitude'.
*************************** 2. row ***************************
  Level: Error
   Code: 3819
Message: Check constraint 'geo_chk_1' is violated.
2 rows in set (0.00 sec)
```
上面定义的第三个坐标值也是无效的，因为它缺少必需的latitude属性。和之前一样，您可以通过尝试将该值插入geo表中，然后随后执行SHOW WARNINGS来查看这一点：The third coordinate value defined above is also invalid, since it is missing the required latitude property. As before, you can see this by attempting to insert the value into the geo table, then issuing SHOW WARNINGS afterwards:
```
mysql> INSERT INTO geo VALUES(@point3);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Error
   Code: 3934
Message: The JSON document location '#' failed requirement 'required' at JSON
Schema location '#'.
*************************** 2. row ***************************
  Level: Error
   Code: 3819
Message: Check constraint 'geo_chk_1' is violated.
2 rows in set (0.00 sec)
```
有关更多信息，请参见第 15.1.20.6 节，“CHECK 约束”。See Section 15.1.20.6, “CHECK Constraints”, for more information.
JSON Schema 支持为字符串指定正则表达式模式，但 MySQL 使用的实现会默默忽略无效的模式。这意味着即使正则表达式模式无效，JSON_SCHEMA_VALID() 也可能返回 true，如下所示：JSON Schema has support for specifying regular expression patterns for strings, but the implementation used by MySQL silently ignores invalid patterns. This means that JSON_SCHEMA_VALID() can return true even when a regular expression pattern is invalid, as shown here:
```
mysql> SELECT JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"');
+---------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"') |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+
1 row in set (0.04 sec)
```
JSON_SCHEMA_VALIDATION_REPORT(schema,document)
根据 JSON schema 验证 JSON document。schema 和 document 都是必需的。与 JSON_VALID_SCHEMA() 类似，schema 必须是一个有效的 JSON 对象，document 必须是一个有效的 JSON 文档。在满足这些条件的情况下，该函数返回一个 JSON 文档形式的验证结果报告。如果根据 JSON Schema JSON 文档被认为是有效的，函数返回一个具有单个属性 valid 且值为 "true" 的 JSON 对象。如果 JSON 文档验证失败，函数返回一个包含此处列出的属性的 JSON 对象：Validates a JSON document against a JSON schema. Both schema and document are required. As with JSON_VALID_SCHEMA(), the schema must be a valid JSON object, and the document must be a valid JSON document. Provided that these conditions are met, the function returns a report, as a JSON document, on the outcome of the validation. If the JSON document is considered valid according to the JSON Schema, the function returns a JSON object with one property valid having the value "true". If the JSON document fails validation, the function returns a JSON object which includes the properties listed here:
- valid：对于失败的 schema 验证始终为 "false"valid: Always "false" for a failed schema validation
- reason：一个包含失败原因的可读字符串reason: A human-readable string containing the reason for the failure
- schema-location：一个 JSON 指针 URI 片段标识符，指示 JSON 模式中验证失败的位置（参见本列表之后的注释）schema-location: A JSON pointer URI fragment identifier indicating where in the JSON schema the validation failed (see Note following this list)
- document-location：一个 JSON 指针 URI 片段标识符，指示 JSON 文档中验证失败的位置（参见本列表之后的注释）document-location: A JSON pointer URI fragment identifier indicating where in the JSON document the validation failed (see Note following this list)
- schema-failed-keyword：一个包含在 JSON 模式中被违反的关键字或属性名称的字符串schema-failed-keyword: A string containing the name of the keyword or property in the JSON schema that was violated
Note
JSON 指针 URI 片段标识符在 RFC 6901 - JavaScript 对象表示法 (JSON) 指针中定义。（这些不是与 JSON_EXTRACT() 和其他 MySQL JSON 函数使用的 JSON 路径表示法相同。）在这种表示法中，# 表示整个文档，而 #/myprop 表示包含在名为 myprop 的顶级属性中的文档部分。更多信息请参见前面引用的规格说明以及本节后面展示的示例。JSON pointer URI fragment identifiers are defined in RFC 6901 - JavaScript Object Notation (JSON) Pointer. (These are not the same as the JSON path notation used by JSON_EXTRACT() and other MySQL JSON functions.) In this notation, # represents the entire document, and #/myprop represents the portion of the document included in the top-level property named myprop. See the specification just cited and the examples shown later in this section for more information.
在此示例中，我们将用户变量 @schema 设置为地理坐标的 JSON 模式的值，将另一个变量 @document 设置为包含一个此类坐标的 JSON 文档的值。然后我们使用它们作为 JSON_SCHEMA_VALIDATION_REORT() 的参数来验证 @document 是否符合 @schema：In this example, we set a user variable @schema to the value of a JSON schema for geographical coordinates, and another one @document to the value of a JSON document containing one such coordinate. We then verify that @document validates according to @schema by using them as the arguments to JSON_SCHEMA_VALIDATION_REORT():
```
mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '>}';
Query OK, 0 rows affected (0.01 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true}                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)
```
现在我们将 @document 设置为其某个属性指定非法值，如下所示：Now we set @document such that it specifies an illegal value for one of its properties, like this:
```
mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 310.445118
    '> }';
```
当使用 JSON_SCHEMA_VALIDATION_REPORT() 测试时，@document 的验证现在会失败。函数调用的输出包含有关失败的详细信息（函数通过 JSON_PRETTY() 包装以提供更好的格式），如下所示：Validation of @document now fails when tested with JSON_SCHEMA_VALIDATION_REPORT(). The output from the function call contains detailed information about the failure (with the function wrapped by JSON_PRETTY() to provide better formatting), as shown here:
```
mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'",
  "schema-location": "#/properties/longitude",
  "document-location": "#/longitude",
  "schema-failed-keyword": "maximum"
}
1 row in set (0.00 sec)
```
由于 @schema 包含 required 属性，我们可以将 @document 设置为其他方面有效但不包含所需属性的值，然后针对 @schema 进行测试。JSON_SCHEMA_VALIDATION_REPORT() 的输出显示验证因缺少必需元素而失败，如下所示：Since @schema contains the required attribute, we can set @document to a value that is otherwise valid but does not contain the required properties, then test it against @schema. The output of JSON_SCHEMA_VALIDATION_REPORT() shows that validation fails due to lack of a required element, like this:
```
mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
  "schema-location": "#",
  "document-location": "#",
  "schema-failed-keyword": "required"
}
1 row in set (0.00 sec)
```
如果我们现在将@schema的值设置为相同的 JSON 模式，但不带 required 属性，则@document将被验证通过，因为它是一个有效的 JSON 对象，即使它不包含任何属性，如下所示：If we now set the value of @schema to the same JSON schema but without the required attribute, @document validates because it is a valid JSON object, even though it contains no properties, as shown here:
```
mysql> SET @schema = '{
    '> "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> }
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true}                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)
```

首页上一级下一节