网站建设基本流程视频/深圳seo

作者：杨涛涛

资深数据库专家，专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

引言

MySQL 从 8.0.17 开始，支持对 json 模式定义做严格匹配校验。首先要了解什么是 json 模式？ json 模式类似于关系表结构，诸如字段数据类型、默认值、字符集等等的约束规则。更加深入了解请参考：https://json-schema.org/understanding-json-schema/

正文

比如下面一个 json 模式定义：此模式所包含内容是一个数组，数据元素分别是整型和字符串，整型必须大于等于5；字符串长度必须大于等于5；元素个数限定在2和5之间；并且每个元素要唯一，不能重复。

'{"$schema": "http://json-schema.org/draft-04/schema#","title":"ytt array","description": "ytt array","type": "array","items": [{"type": "number","minimum": 5},{"type": "string","minLength": 5}],"minItems": 2,"maxItems": 5,"uniqueItems": true
}'

MySQL 8.0 内置的 json 模式校验函数可以在 json 串入库前根据预定义模式来提前严格校验，减少后期工作量。

目前有两个相关函数：

1. json_schema_valid

   来校验指定 json 串是否符合预定义模式，符合则返回1，不符合返回0.

2. json_schema_validation_report

   来校验指定 json 串是否符合预定义模式，并且给出详细报告，并不只是简单的返回1和0.

   校验成功则结果为：{“valid”: true}

   校验失败则结果为一个json对象：

   {“valid”:false，

   “reason”: 失败详细原因表述,

   “schema-location”: 失败的模式具体path，

   “document-location”: 失败的检测json对象或者数组具体path，

   “schema-failed-keyword”：失败对应的模式种的key

   }

接下来看看这两个函数的具体用法：分别定义一个数组和一个对象。

先是数组：我把开头那个模式定义稍微改了下，加了一个枚举元素，其他没变，为了好看，用 json_pretty 函数打出来。

mysql:ytt>select json_pretty(@schema)\G
*************************** 1. row ***************************
json_pretty(@schema): {"type": "array","items": [{"type": "number","minimum": 5},{"type": "string","minLength": 5},{"enum": ["postgresql","oracle","uguard","tidb"]}],"title": "ytt array","$schema": "http://json-schema.org/draft-04/schema#","maxItems": 5,"minItems": 3,"description": "ytt array","uniqueItems": true
}
1 row in set (0.01 sec)

接下来分别看看以下 json 数组是否符合预定义模式：

mysql:ytt>set @a='[]';
Query OK, 0 rows affected (0.00 sec)
mysql:ytt>set @b='["mysql",100,"tidb"]';
Query OK, 0 rows affected (0.00 sec)mysql:ytt>set @c='[100,"mysql","uguard"]';
Query OK, 0 rows affected (0.00 sec)mysql:ytt>set @d='[200,"database","postgresql","others"]';
Query OK, 0 rows affected (0.00 sec)mysql:ytt>select json_schema_valid(@schema,@a) "@a",json_schema_valid(@schema,@b) "@b", json_schema_valid(@schema,@c) "@c", json_schema_valid(@schema,@d) "@d";
+------+------+------+------+
| @a   | @b   | @c   | @d   |
+------+------+------+------+
|    0 |    0 |    1 |    1 |
+------+------+------+------+
1 row in set (0.00 sec)

从结果中看@a和@b不符合模式定义，@c和@d都符合模式定义；来看下校验失败原因，用函数 json_schema_validation_report 来验证下：

mysql:ytt>select json_pretty(json_schema_validation_report(@schema,@a)) "@a result"\G
*************************** 1. row ***************************
@a result: {"valid": false,"reason": "The json document location '#' failed requirement 'minItems' at json Schema location '#'","schema-location": "#","document-location": "#","schema-failed-keyword": "minItems"
}
1 row in set (0.00 sec)

由函数 json_schema_validation_report 报告结果可以得出，@a不满足最小元素数目这一项定义。那再来看看@b为什么不符合：

mysql:ytt>select json_pretty(json_schema_validation_report(@schema,@b)) "@b result"\G
*************************** 1. row ***************************
@b result: {"valid": false,"reason": "The json document location '#/0' failed requirement 'type' at json Schema location '#/items/0'","schema-location": "#/items/0","document-location": "#/0","schema-failed-keyword": "type"
}
1 row in set (0.00 sec)

同样由函数 json_schema_validation_report 报告结果可以得出，@b不符合的原因是不满足模式定义里数组第一个元素的数据类型，指定类型为整型，@b的第一个元素是字符串。

@c和@d 完全符合模式定义，数组元素类型、元素最小个数、元素最大个数等都符合。

再来看一个对象：依然用 json_pretty 函数打印出来，这个模式定义内容为对象，有几点需要说明：对象的key分别为x,y,z，key对应的 value 数据类型分别为整型，字符串，枚举；另外还规定最小key为2个，最大为3个，并且x、y这两个key必须同时出现。

mysql:ytt>select json_pretty(@schema)\G
*************************** 1. row ***************************
json_pretty(@schema): {"type": "object","title": "ytt object","$schema": "http://json-schema.org/draft-04/schema#","required": ["x","y"],"properties": {"x": {"type": "number"},"y": {"type": "string"},"z": {"enum": ["mysql","ugurad","postgresql"]}},"description": "ytt object","maxProperties": 3,"minProperties": 2
}
1 row in set (0.00 sec)

接下来，定义两个 json 对象，来看下对于模式定义为对象的校验。

mysql:ytt>set @a='{"x":"mysql","y":"oracle"}';
Query OK, 0 rows affected (0.00 sec)mysql:ytt>set @b='{"x":10,"y":"uguard","z":"mysql"}' ;
Query OK, 0 rows affected (0.00 sec)mysql:ytt>select json_schema_valid(@schema,@a) "@a", json_schema_valid(@schema,@b) "@b";
+------+------+
| @a   | @b   |
+------+------+
|    0 |    1 |
+------+------+
1 row in set (0.00 sec)

从函数 json_schema_valid 的校验结果可以得到@a不符合模式定义， @a第一个 key 对应的 value 为字符串，不符合模式定义中的 key 为x的数据类型。 用函数 json_schema_validation_report 来验证下我们的猜测：

mysql:ytt>select json_pretty(json_schema_validation_report(@schema,@a)) "@a result"\G
*************************** 1. row ***************************
@a result: {"valid": false,"reason": "The json document location '#/x' failed requirement 'type' at json Schema location '#/properties/x'","schema-location": "#/properties/x","document-location": "#/x","schema-failed-keyword": "type"
}
1 row in set (0.00 sec)

函数 json_schema_validation_report 的结果刚好验证我们的猜测，key 为x对应的 value 数据类型不匹配。

json 模式定义的验证也可以用到约束表的 check 定义，我们就用第二个包含对象的模式定义来约束表的 json 字段，定义表t10:

create table t10
(str1 json,
check(json_schema_valid(
'{"$schema": "http://json-schema.org/draft-04/schema#","title":"ytt object","description": "ytt object","type": "object","properties": {"x": { "type": "number" },"y": { "type": "string" },"z": { "enum": ["mysql", "ugurad", "postgresql"] }},"required": ["x", "y"],"minProperties": 2,"maxProperties": 3
}',str1)));

分别把 json 对象@a和@b写入表t10: 插入@a失败，show warnings 报告失败具体原因。

mysql:ytt>insert t10 values (@a);
ERROR 3819 (HY000): Check constraint 't10_chk_1' is violated.
mysql:ytt>show errors\G
*************************** 1. row ***************************Level: ErrorCode: 3934
Message: The json document location '#/x' failed requirement 'type' at json Schema location '#/properties/x'.
*************************** 2. row ***************************Level: ErrorCode: 3819
Message: Check constraint 't10_chk_1' is violated.
2 rows in set (0.00 sec)mysql:ytt>insert t10 values (@b);
Query OK, 1 row affected (0.50 sec)

结语

json_schema_valid 和 json_schema_validation_report 这两个函数可以在使用 json 数据类型前做一个预判，避免实际数据写入后再次整理不规范的数据，在使用 json 数据类型的场景中非常有用。