• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
    •
    位置： MySQL 8 中文手册 -> MySQL 函数和运算符 -> JSON函数

    JSON模式验证功能

    从MySQL 8.0.17开始，MySQL支持针对符合JSON Schema规范草案4的 JSON模式验证JSON文档。可以使用本节中详细介绍的功能之一来完成此功能，这两个功能都包含两个参数，一个JSON模式和一个根据该模式验证的JSON文档。JSON_SCHEMA_VALID()如果文档根据架构进行验证，则返回true；否则，则返回false；否则，返回false。JSON_SCHEMA_VALIDATION_REPORT()提供有关验证的JSON格式的报告。

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

    • 如果至少有一个自变量NULL，则函数返回NULL。
    • 如果至少一个参数不是有效的JSON，则该函数将引发错误（ER_INVALID_TYPE_FOR_JSON）
    • 此外，如果架构不是有效的JSON对象，则该函数返回ER_INVALID_JSON_TYPE。

    MySQL required在JSON模式中支持该属性以强制包含必需的属性（请参见函数描述中的示例）。

    MySQL的支持id，$schema，description，和type在JSON模式属性，但不需要任何的这些。

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

    注意

    MySQL在JSON模式中支持正则表达式模式，该模式支持但无声地忽略了无效模式（JSON_SCHEMA_VALID()有关示例，请参见的描述）。

    以下列表中详细描述了这些功能：

    • JSON_SCHEMA_VALID(schema,document)

      document针对JSON 验证JSON schema。这两个schema和document是必需的。模式必须是有效的JSON对象；该文档必须是有效的JSON文档。满足以下条件：如果文档针对架构进行验证，则该函数返回true（1）；否则，该函数将返回true。否则，返回false（0）。

      在此示例中，我们将用户变量设置@schema为用于地理坐标的JSON模式@document的值，将另一个变量设置为包含一个此类坐标的JSON文档的值。然后，我们通过将它们用作以下参数来验证是否@document验证：@schemaJSON_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，如下所示：

      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模式用作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_SCHEMA_VALID()在使用它为表指定此类约束时，必须将JSON模式传递给内联。

      我们将代表坐标的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和更高版本中，您可以latitude通过发出以下SHOW WARNINGS语句来获取有关故障性质的精确信息（在这种情况下，该值超过架构中定义的最大值）：

      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)

      document针对JSON 验证JSON schema。这两个schema和document是必需的。与JSON_VALID_SCHEMA（）一样，模式必须是有效的JSON对象，并且文档必须是有效的JSON文档。如果满足这些条件，则该函数将返回有关验证结果的报告，作为JSON文档。如果根据JSON模式将JSON文档视为有效，则该函数返回一个JSON对象，该对象的一个属性valid值为“ true”。如果JSON文档验证失败，则该函数将返回一个JSON对象，该对象包括此处列出的属性：

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

      JSON指针URI片段标识符在 RFC 6901-JavaScript对象表示法（JSON）指针中定义。（这些是不一样由所使用的JSON路径表示法JSON_EXTRACT()和其他MySQL JSON功能）。在这种表示法中，#表示整个文档，和#/myprop表示包括在命名顶层属性的文档的所述部分myprop。有关更多信息，请参见刚刚引用的规范以及本节稍后显示的示例。

      在此示例中，我们将用户变量设置@schema为用于地理坐标的JSON模式@document的值，将另一个变量设置为包含一个此类坐标的JSON文档的值。然后，我们通过将它们用作以下参数来验证是否@document验证：@schemaJSON_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其设置为为其属性之一指定非法值，如下所示：

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

      @document使用进行测试时，现在验证失败JSON_SCHEMA_VALIDATION_REPORT()。函数调用的输出包含有关失败的详细信息（通过包装函数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)

      如果现在我们将的值设置为@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)

    上篇：JSON表函数

    下篇：JSON实用程序功能