MySQL8.0 JSON相关函数(四) - JSON Schema验证、部分更新、空间占用计算

原创

月饮沙 2023-08-10 11:19:10 博主文章分类：MySQL ©著作权

©著作权归作者所有：来自51CTO博客作者月饮沙的原创作品，请联系作者获取转载授权，否则将追究法律责任

(目录)

本文涉及函数简介

函数	作用
JSON_SCHEMA_VALID	验证JSON文档是否符合模式，返回true/false
JSON_SCHEMA_VALIDATION_REPORT	验证JSON文档是否符合模式，返回json格式的报告
JSON_PRETTY	格式化JSON文档，以标准格式返回
JSON_STORAGE_FREE	返回部分更新后JSON的空闲空间
JSON_STORAGE_SIZE	返回JSON文档占用的总空间

JSON Schema验证

从MySQL8.0.17开始，MySQL支持通过JSON Schema验证。JSON Schema需要符合Draft 4 of the JSON Schema specification MongoDB中的也存在JSON Schema验证模式，参考:MongoDB基础操作：文档操作-数据校验，Specify JSON Schema Validation

MySQL提供了两个函数来进行JSON Schema验证：

JSON_SCHEMA_VALID() 验证JSON文档是否符合模式，返回true/false
JSON_SCHEMA_VALIDATION_REPORT() 提供JSON格式的报告

对于null和无效值的处理：

如果任一参数为NULL，结果为NULL
如果任一参数不是JSON格式，返回错误ER_INVALID_TYPE_FOR_JSON
如果schema不是有效的JSON，返回错误ER_INVALID_TYPE_FOR_JSON

JSON_SCHEMA_VALID

JSON_SCHEMA_VALID(schema,document) 验证document是否符合schema，返回true(1)/false(0)

示例

mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object", -- 文档的类型需要是JSON对象
    '> "properties": { -- 详细信息
    '>   "latitude": { -- latitude键的值，类型需要是数字，最大值90，最小值-90
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": { -- -- longitude键的值，类型需要是数字，最大值180，最小值-180
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"] -- 文档中必须包含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)

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)

在表中进行模式验证

可以在建表时使用CHECK子句来设置对表数据进行模式验证，CHECK不能引用参数，所以必须将schema直接写在JSON_SCHEMA_VALID函数里。在存在模式验证的表中插入数据时，如果要插入的数据不符合模式验证要求，会报错ERROR 3819 (HY000): Check constraint 'xxx' is violated. 在MySQL8.0.19及之后的版本，可以通过SHOW WARNINGS语句查看具体哪部分值不符合模式。

-- 创建一个带模式验证的表
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> insert into geo (coordinate) values ('{"latitude":59, "longitude":18}');
Query OK, 1 row affected (0.01 sec)

-- 插入不符合模式的值
mysql> insert into geo (coordinate) values ('{"latitude":91, "longitude":0}');
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 '#/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)

正则表达式

在schema中可以使用正则表达式，但是如果正则表达式无效，MySQL会忽略该正则表达式，导致JSON_SCHEMA_VALID()返回true

mysql> select JSON_SCHEMA_VALID('{"type":"string","pattern":"[0-9][3]"}','"123"'),JSON_SCHEMA_VALID('{"type":"string","pattern":"[0-9][1]"}','"123"'),JSON_SCHEMA_VALID('{"type":"string","pattern":"("}','"123"')\G
*************************** 1. row ***************************
-- 字符串类型，数字出现3次，匹配，返回1
JSON_SCHEMA_VALID('{"type":"string","pattern":"[0-9][3]"}','"123"'): 1
-- 字符串类型，数字只出现1次，不匹配，返回0
JSON_SCHEMA_VALID('{"type":"string","pattern":"[0-9][1]"}','"123"'): 0
-- (不是有效的正则表达式，结果也返回 1
       JSON_SCHEMA_VALID('{"type":"string","pattern":"("}','"123"'): 1

JSON_SCHEMA_VALIDATION_REPORT

JSON_SCHEMA_VALIDATION_REPORT(schema,document) 验证document是否符合schema。返回一个json格式的验证报告。

报告格式

valid 是否符合模式，true/false。如果结果为true,不会再返回其他属性
reason 不符合模式的原因
schema-location 一个URI，表示不符合模式中的哪部分位置
document-location 一个URI，表示文档中的哪部分位置不符合模式
schema-failed-keyword 不符合模式的关键字或属性名称

示例

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"]
    '>}';

-- 符合模式的值
mysql> SET @document1 = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';

-- 不符合模式的值，longitude超过最大值
mysql> SET @document2 = '{
    '> "latitude": 63.444697,
    '> "longitude": 310.445118
    '> }';

-- 不符合模式的值，不包含需要的属性
mysql> SET @document3 = '{}';

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document1),JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document2)),JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document3))\G
*************************** 1. row ***************************
-- 符合模式，返回报告
             JSON_SCHEMA_VALIDATION_REPORT(@schema, @document1): {"valid": true}

-- 不符合模式，返回报告
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document2)): {
  "valid": false, -- 验证失败
  "reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'", -- 失败原因
  "schema-location": "#/properties/longitude", --schema位置
  "document-location": "#/longitude", -- document位置
  "schema-failed-keyword": "maximum" -- 导致验证失败的属性
}

-- 不符合模式，返回报告
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document3)): {
  "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)

JSON值的部分更新

在MySQL8.0中，MySQL可以执行JSON值的部分更新，不需要删除旧文档再插入新文档，而是直接更新原始文档中的部分内容。

部分更新在以下情况下可用：

要更新的列数据类型是JSON
使用JSON_SET(), JSON_REPLACE(), JSON_REMOVE()函数来更新列值
- 直接更新列值(UPDATE mytable SET jcol = '{"a": 10, "b": 25}')不会进行部分更新优化
- 在一个UPDATE语句中更新多个JSON列可以进行部分更新
输入列和目标列必须是同一列。
- UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100)不能进行部分更新
- 只要输入列和目标列相同，可以嵌套使用以上三个语句来进行部分更新
对当前数组或对象的更新，都是使用新值替换原有值，不向父数组或对象中添加新元素。
新值不能大于旧值
- 当一次执行多个部分更新时，如果前一次部分更新留下了空闲空间，可以忽略该条件

部分更新可以使用压缩格式写入binlog日志，以节省空间。设置binlog_row_value_options=PARTIAL_JSON来启用

JSON_STORAGE_SIZE

JSON_STORAGE_SIZE(json_val) 计算JSON文档占用的空间大小。部分更新不会更改JSON文档占用的空间大小。 MySQL中，JSON文档的最大限制是不能超过max_allowed_packet的值

JSON_STORAGE_FREE

JSON_STORAGE_FREE(json_val) 计算部分更新释放的空闲空间，如果部分更新的新值占用空间小于旧值，会返回一个非0值，否则返回0。 JSON_STORAGE_FREE只能用于计算列值，如果输入一个JSON文档，总是返回0。实际返回的是JSON列中包含的空闲空间，如果多次进行部分更新，值是累加的，在已经存在空闲空间时，如果新值占用的空间大于旧值，但是差值小于空闲空间，也可以使用已经存在的空闲空间进行部分更新。

示例

-- 建表，插入JSON数据
mysql> CREATE TABLE jtable (id int auto_increment primary key,jcol JSON);
mysql> insert into jtable (jcol) values ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');

-- 查询初始空间情况
mysql> select jcol,JSON_STORAGE_SIZE(jcol),JSON_STORAGE_FREE(jcol) from jtable\G
*************************** 1. row ***************************
                   jcol: {"a": 10, "b": "wxyz", "c": "[true, false]"}
JSON_STORAGE_SIZE(jcol): 48
JSON_STORAGE_FREE(jcol): 0

-- 执行部分更新
mysql>  UPDATE jtable SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);

-- 查看部分更新后的空间情况，总空间不变，释放了部分空闲空间
mysql> select jcol,JSON_STORAGE_SIZE(jcol),JSON_STORAGE_FREE(jcol) from jtable\G
*************************** 1. row ***************************
                   jcol: {"a": 10, "b": "wxyz", "c": 1}
JSON_STORAGE_SIZE(jcol): 48
JSON_STORAGE_FREE(jcol): 14

-- 部分更新释放了一部分空间，可以在此基础上再次进行部分更新
mysql> select jcol,JSON_STORAGE_SIZE(jcol),JSON_STORAGE_FREE(jcol) from jtable\G
*************************** 1. row ***************************
                   jcol: {"a": 10, "b": "wxyzefg", "c": 1}
JSON_STORAGE_SIZE(jcol): 48
JSON_STORAGE_FREE(jcol): 11

-- 部分更新只对列生效，参数不存在部分更新
mysql> SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j,JSON_STORAGE_SIZE(@j),JSON_STORAGE_FREE(@j)\G
*************************** 1. row ***************************
                   @j: {"a": 10, "b": "wxyz", "c": "[true, false]"}
JSON_STORAGE_SIZE(@j): 48
JSON_STORAGE_FREE(@j): 0

mysql> SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');

mysql> SELECT @j,JSON_STORAGE_SIZE(@j),JSON_STORAGE_FREE(@j)\G
*************************** 1. row ***************************
                   @j: {"a": 10, "b": "wxyz", "c": "1"}
JSON_STORAGE_SIZE(@j): 36
JSON_STORAGE_FREE(@j): 0

其他函数

JSON_PRETTY

JSON_PRETTY(json_val) 将JSON文档转换成标准的，更好看的格式进行输出

标准

每个数组元素/字典键值对单独一行，并进行缩进
每个级别的缩进添加两个空格
用于分割元素的,放在每个数组元素/字典键值对的结尾
键值对之间使用:进行分割
空数组/空对象单独占用一行，并去除期间的空格
特殊符号按照JSON_QUOTE()的规则进行处理

示例

mysql> SELECT JSON_PRETTY('123'); # scalar
+--------------------+
| JSON_PRETTY('123') |
+--------------------+
| 123                |
+--------------------+

mysql> SELECT JSON_PRETTY("[1,3,5]"); # array
+------------------------+
| JSON_PRETTY("[1,3,5]") |
+------------------------+
| [
  1,
  3,
  5
]      |
+------------------------+

mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
+---------------------------------------------+
| JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
+---------------------------------------------+
| {
  "a": "10",
  "b": "15",
  "x": "25"
}   |
+---------------------------------------------+

mysql> SELECT JSON_PRETTY('["a",1,{"key1":
    '>    "value1"},"5",     "77" ,
    '>       {"key2":["value3","valueX",
    '> "valueY"]},"j", "2"   ]')\G  # nested arrays and objects
*************************** 1. row ***************************
JSON_PRETTY('["a",1,{"key1":
             "value1"},"5",     "77" ,
                {"key2":["value3","valuex",
          "valuey"]},"j", "2"   ]'): [
  "a",
  1,
  {
    "key1": "value1"
  },
  "5",
  "77",
  {
    "key2": [
      "value3",
      "valuex",
      "valuey"
    ]
  },
  "j",
  "2"
]

下一篇：MySQL8.0 JSON的对比、排序和索引

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯