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

版本 8.4

9.0
8.0

8.0 日语

MySQL 8.4 参考手册 / ... / JSON 模式验证函数

14.17.7 JSON 模式验证函数

MySQL 支持根据符合 JSON 模式规范草案 4 的 JSON 模式验证 JSON 文档。这可以使用本节中详细介绍的两个函数中的任何一个来完成，这两个函数都接受两个参数：一个 JSON 模式和一个根据该模式进行验证的 JSON 文档。JSON_SCHEMA_VALID() 如果文档根据模式验证，则返回 true，否则返回 false；JSON_SCHEMA_VALIDATION_REPORT() 提供有关验证的 JSON 格式报告。

这两个函数都按如下方式处理空值或无效输入

如果至少有一个参数是 NULL，则函数返回 NULL。
如果至少有一个参数不是有效的 JSON，则函数会引发错误 (ER_INVALID_TYPE_FOR_JSON)
此外，如果模式不是有效的 JSON 对象，则函数返回 ER_INVALID_JSON_TYPE。

MySQL 支持 JSON 模式中的 required 属性来强制包含必需的属性（请参阅函数说明中的示例）。

MySQL 支持 JSON 模式中的 id、$schema、description 和 type 属性，但不需要其中任何一个。

MySQL 不支持 JSON 模式中的外部资源；使用 $ref 关键字会导致 JSON_SCHEMA_VALID() 失败并返回 ER_NOT_SUPPORTED_YET。

注意

MySQL 支持 JSON 模式中的正则表达式模式，它支持但会静默忽略无效模式（请参阅 JSON_SCHEMA_VALID() 的说明以获取示例）。

以下列表详细介绍了这些函数

JSON_SCHEMA_VALID(schema,document)

根据 JSON schema 验证 JSON document。schema 和 document 都是必需的。模式必须是有效的 JSON 对象；文档必须是有效的 JSON 文档。假设满足这些条件：如果文档根据模式验证，则函数返回 true (1)；否则，它返回 false (0)。

在本例中，我们将用户变量 @schema 设置为地理坐标的 JSON 模式的值，并将另一个变量 @document 设置为包含一个此类坐标的 JSON 文档的值。然后，我们通过将它们用作 JSON_SCHEMA_VALID() 的参数来验证 @document 是否根据 @schema 进行验证

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 对其进行测试，如下所示

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 对象，即使它不包含任何属性，如下所示

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 约束。

考虑如下所示创建的表 geo，其中 JSON 列 coordinate 表示地图上的纬度和经度点，由 JSON 模式管理，该模式在 JSON_SCHEMA_VALID() 调用中用作参数，该调用作为此表上 CHECK 约束的表达式传递

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)

注意

因为 MySQL CHECK 约束不能包含对变量的引用，所以当使用 JSON 模式为表指定此类约束时，必须将 JSON 模式内联传递给 JSON_SCHEMA_VALID()。

我们将表示坐标的 JSON 值分配给三个变量，如下所示

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 语句中所示

mysql> INSERT INTO geo VALUES(@point1);
Query OK, 1 row affected (0.05 sec)

第二个 JSON 值无效，因此无法满足约束，如下所示

mysql> INSERT INTO geo VALUES(@point2);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.

您可以通过发出 SHOW WARNINGS 语句来获取有关故障性质的精确信息，在本例中，即 latitude 值超出了模式中定义的最大值

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 来查看这一点

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 约束”。

JSON 模式支持为字符串指定正则表达式模式，但 MySQL 使用的实现会静默忽略无效模式。这意味着即使正则表达式模式无效，JSON_SCHEMA_VALID() 也可以返回 true，如下所示

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 文档有效，则该函数将返回一个 JSON 对象，该对象具有一个属性 valid，其值为 “true”。如果 JSON 文档验证失败，则该函数将返回一个 JSON 对象，其中包含此处列出的属性。

valid：对于失败的 schema 验证，始终为 “false”。
reason：一个包含失败原因的可读字符串。
schema-location：一个 JSON 指针 URI 片段标识符，指示 JSON schema 中验证失败的位置（请参阅此列表后的注释）。
document-location：一个 JSON 指针 URI 片段标识符，指示 JSON 文档中验证失败的位置（请参阅此列表后的注释）。
schema-failed-keyword：一个包含 JSON schema 中违反的关键字或属性名称的字符串。

注意

JSON 指针 URI 片段标识符在 RFC 6901 - JavaScript 对象表示法（JSON）指针中定义。（这些与 JSON_EXTRACT() 和其他 MySQL JSON 函数使用的 JSON 路径表示法不同。）在此表示法中，# 表示整个文档，而 #/myprop 表示顶级属性 myprop 中包含的文档部分。有关更多信息，请参阅刚才引用的规范以及本节后面显示的示例。

在本例中，我们将用户变量 @schema 设置为地理坐标的 JSON schema 的值，并将另一个用户变量 @document 设置为包含一个此类坐标的 JSON 文档的值。然后，我们通过将它们用作 JSON_SCHEMA_VALIDATION_REORT() 的参数来验证 @document 是否根据 @schema 进行验证。

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 设置为为其一个属性指定非法值，如下所示：

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 310.445118
    '> }';

现在，使用 JSON_SCHEMA_VALIDATION_REPORT() 测试时，@document 的验证失败。函数调用的输出包含有关失败的详细信息（函数由 JSON_PRETTY() 包装以提供更好的格式），如下所示：

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() 的输出显示，由于缺少必需元素，验证失败，如下所示：

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)

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)