Beginning with MySQL 8.0.17, MySQL supports validation of JSON documents against JSON schemas conforming to Draft 4 of the JSON Schema specification. 从MySQL 8.0.17开始，MySQL支持根据符合JSON模式规范草案4的JSON模式验证JSON文档。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模式，另一个是根据模式验证的JSON文档。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.JSON_SCHEMA_VALID()如果文档根据模式进行验证，则返回true，否则返回false；JSON_SCHEMA_VALIDATION_REPORT()提供一个JSON格式的验证报告。
Both functions handle null or invalid input as follows:这两个函数都处理空或无效输入，如下所示：
If at least one of the arguments is 如果至少有一个参数为NULL, the function returns NULL.NULL，则函数返回NULL。
If at least one of the arguments is not valid JSON, the function raises an error (如果至少有一个参数不是有效的JSON，则函数将引发ER_INVALID_TYPE_FOR_JSON)ER_INVALID_TYPE_FOR_JSON错误
In addition, if the schema is not a valid JSON object, the function returns 此外，如果模式不是有效的JSON对象，则函数返回ER_INVALID_JSON_TYPE.ER_INVALID_JSON_TYPE。
MySQL supports the MySQL支持JSON模式中的required属性来强制包含所需的属性（参见函数描述中的示例）。required attribute in JSON schemas to enforce the inclusion of required properties (see the examples in the function descriptions).
MySQL supports the MySQL支持JSON模式中的id, $schema, description, and type attributes in JSON schemas but does not require any of these.id、$schema、description和type属性，但不需要这些属性。
MySQL does not support external resources in JSON schemas; using the MySQL不支持JSON模式中的外部资源；使用$ref keyword causes JSON_SCHEMA_VALID() to fail with ER_NOT_SUPPORTED_YET.$ref关键字会导致JSON_SCHEMA_VALID()产生ER_NOT_SUPPORTED_YET失败。
MySQL supports regular expression patterns in JSON schema, which supports but silently ignores invalid patterns (see the description of MySQL支持JSON schema中的正则表达式模式，它支持但默默地忽略无效模式（参见JSON_SCHEMA_VALID() for an example).JSON_SCHEMA_VALID()的描述）。
These functions are described in detail in the following list:下面的列表详细描述了这些功能：
JSON_SCHEMA_VALID(schema,document)
Validates a JSON 根据JSONdocument against a JSON schema. schema验证JSONdocument。Both schema and document are required. schema和document都是必需的。The schema must be a valid JSON object; the document must be a valid JSON document. 架构必须是有效的JSON对象；文档必须是有效的JSON文档。Provided that these conditions are met: If the document validates against the schema, the function returns true (1); otherwise, it returns false (0).如果满足这些条件：如果文档根据模式进行验证，则函数返回true（1）；否则，返回false（0）。
In this example, we set a user variable 在本例中，我们将一个用户变量@schema to the value of a a JSON schema for geographical coordinates, and another one @document to the value of a JSON document containing one such coordinate. @schema设置为地理坐标的JSON模式的值，并将另一个@document设置为包含此类坐标的JSON文档的值。We then verify that 然后，我们使用@document validates according to @schema by using them as the arguments to JSON_SCHEMA_VALID():@document作为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)
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:@schema包含必需的属性，我们可以将@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)
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:@schema的值设置为相同的JSON 架构，但没有必需的属性，@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() and CHECK constraints.JSON_SCHEMA_VALID()和检查约束。 JSON_SCHEMA_VALID() can also be used to enforce CHECK constraints.JSON_SCHEMA_VALID()还可以用于强制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:geo，其JSON列坐标表示地图上的纬度和经度点，由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)
Because a MySQL 因为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.CHECK约束不能包含对变量的引用，所以在使用JSON架构指定此类约束时，必须将其内联传递给JSON_SCHEMA_VALID()。
We assign JSON values representing coordinates to three variables, as shown here:我们将代表坐标的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)
The first of these values is valid, as can be seen in the following 这些值中的第一个是有效的，如下面的INSERT statement:INSERT语句所示：
mysql> INSERT INTO geo VALUES(@point1); Query OK, 1 row affected (0.05 sec)
The second JSON value is invalid and so fails the constraint, as shown here:第二个JSON值无效，因此未通过约束，如下所示：
mysql> INSERT INTO geo VALUES(@point2); ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
In MySQL 8.0.19 and later, you can obtain precise information about the nature of the failure—in this case, that the 在MySQL 8.0.19及更高版本中，您可以通过发出latitude value exceeds the maximum defined in the schema — by issuing a SHOW WARNINGS statement: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)
The third coordinate value defined above is also invalid, since it is missing the required 上面定义的第三个坐标值也是无效的，因为它缺少所需的latitude property. latitude属性。As before, you can see this by attempting to insert the value into the 与前面一样，您可以通过尝试将值插入到geo table, then issuing SHOW WARNINGS afterwards: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)
See Section 13.1.20.6, “CHECK Constraints”, for more information.有关更多信息，请参阅第13.1.20.6节，“检查约束”。
JSON Schema has support for specifying regular expression patterns for strings, but the implementation used by MySQL silently ignores invalid patterns. JSON模式支持为字符串指定正则表达式模式，但是MySQL使用的实现会自动忽略无效的模式。This means that 这意味着即使正则表达式模式无效，JSON_SCHEMA_VALID() can return true even when a regular expression pattern is invalid, as shown here: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)
Validates a JSON 根据JSONdocument against a JSON schema. schema验证JSONdocument。Both schema and document are required. schema和document都是必需的。As with JSON_VALID_SCHEMA(), the schema must be a valid JSON object, and the document must be a valid JSON document. 与JSON_VALID_SCHEMA()一样，schema必须是有效的JSON对象，document必须是有效的JSON文档。Provided that these conditions are met, the function returns a report, as a JSON document, on the outcome of the validation. 如果满足这些条件，该函数将返回一个关于验证结果的报告（作为JSON文档）。If the JSON document is considered valid according to the JSON Schema, the function returns a JSON object with one property 如果根据JSON模式，JSON文档被认为是有效的，那么函数将返回一个JSON对象，其中一个属性valid having the value "true". valid的值为"true"。If the JSON document fails validation, the function returns a JSON object which includes the properties listed here:如果JSON文档验证失败，函数将返回一个JSON对象，其中包含以下属性：
valid: Always "false" for a failed schema validation：对于失败的架构验证，始终为“false”
reason: A human-readable string containing the reason for the failure：包含失败原因的可读字符串
schema-location: A JSON pointer URI fragment identifier indicating where in the JSON schema the validation failed (see Note following this list)：一个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)：一个JSON指针URI片段标识符，指示JSON文档中验证失败的位置（请参阅此列表后面的注释）
schema-failed-keyword: A string containing the name of the keyword or property in the JSON schema that was violated：一个字符串，其中包含JSON架构中违反的关键字或属性的名称
JSON pointer URI fragment identifiers are defined in RFC 6901 - JavaScript Object Notation (JSON) Pointer. JSON指针URI片段标识符在RFC 6901-JavaScript对象表示法（JSON）指针中定义。(These are not the same as the JSON path notation used by （这些与JSON_EXTRACT() and other MySQL JSON functions.) JSON_EXTRACT()和其他MySQL JSON函数使用的JSON路径表示法不同。）In this notation, 在这个表示法中，# represents the entire document, and #/myprop represents the portion of the document included in the top-level property named myprop. #表示整个文档，#/myprop表示顶级属性myprop中包含的文档部分。See the specification just cited and the examples shown later in this section for more information.有关更多信息，请参阅刚刚引用的规范和本节后面显示的示例。
In this example, we set a user variable 在本例中，我们将一个用户变量@schema to the value of a a JSON schema for geographical coordinates, and another one @document to the value of a JSON document containing one such coordinate. @schema设置为地理坐标的JSON模式的值，并将另一个@document设置为包含此类坐标的JSON文档的值。We then verify that 然后，我们使用@document validates according to @schema by using them as the arguments to JSON_SCHEMA_VALIDATION_REORT():@document作为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)
Now we set 现在我们设置@document such that it specifies an illegal value for one of its properties, like this:@document，以便它为其属性之一指定非法值，如下所示：
mysql>SET @document = '{'>"latitude": 63.444697,'>"longitude": 310.445118'>}';
Validation of 当用@document now fails when tested with JSON_SCHEMA_VALIDATION_REPORT(). JSON_SCHEMA_Validation_REPORT()测试时，@document的验证现在失败。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: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)
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. @schema包含必需的属性，所以我们可以将@document设置为一个其他方面有效但不包含必需属性的值，然后对@schema进行测试。The output of JSON_SCHEMA_VALIDATION_REPORT() shows that validation fails due to lack of a required element, like this: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)
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:@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)