MySQL的函数和运算符 - JSON 函数 - JSON模式验证函数

林员外聊编程 2021-09-12

144

JSON模式验证函数

从 MySQL 8.0.17 开始，MySQL 支持基于符合 JSON 模式规范草案 4 的 JSON 模式验证 JSON 文档。这可以使用本文中详细介绍的2个函数来完成，它们都有两个参数，一个 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 错误。

以下列表详细描述了这些函数：

● JSON_SCHEMA_VALID(schema,document)

根据 JSON schema 验证 JSON document。schema 和 document 都是必需的。模式必须是一个有效的 JSON 对象，该文档必须是有效的 JSON 文档。若以上条件满足：如果文档通过了基于模式的验证，函数返回 true (1)，否则，返回 false (0)。

在本例中，我们将用户变量 @schema 设置为地理坐标的 JSON 模式值，将另一个用户变量 @document 设置为包含地理坐标的 JSON 文档值。然后，我们通过使用 @schema 作为 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_SCHEMA_VALID() 的 JSON 模式参数支配，此参数作为表达式传入，用于此表的 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_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.
复制

在 MySQL 8.0.19 及更高版本中，可以通过执行 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)
复制

JSON Schema 支持为字符串指定正则表达式模式，但 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() 一样，模式必须是有效的 JSON 对象，文档必须是有效的 JSON 文档。如果满足了这些条件，该函数将以 JSON 文档形式返回一个关于验证结果的报告。如果 JSON 文档根据 JSON Schema 被认为是有效的，则该函数返回一个 JSON 对象，该对象具有一个 valid 属性，值为 “true”。如果 JSON 文档验证失败，该函数将返回一个 JSON 对象，其中包含以下属性：

■ valid: 对于失败的模式验证，总是返回 “false”

■ reason: 包含失败原因的人类可读的字符串

■ schema-location: 一个 JSON 指针 URI 片段标识符，表示 JSON 模式中验证失败的位置

■ document-location: 一个 JSON 指针 URI 片段标识符，表示在 JSON 文档中验证失败的位置

■ schema-failed-keyword: 一个字符串，包含 JSON 模式中被违反的关键字或属性的名称

在本例中，我们将用户变量 @schema 设置为地理坐标的 JSON 模式值，将另一个用户变量 @document 设置为包含地理坐标的 JSON 文档值。然后，我们通过使用 @schema 作为 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 时，验证会失败。函数调用的输出包含关于失败的详细信息，如下所示：

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)
复制

如果现在将 @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_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true}                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)
复制

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - JSON 函数 - JSON模式验证函数

评论

相关阅读