专栏首页SEian.G学习记录MySQL 8.0 JSON增强到底有多强?(一)

MySQL 8.0 JSON增强到底有多强?(一)

我们都知道,从5.7版本开始,MySQL 支持 RFC7159定义的原生JSON数据类型,该类型支持对JSON文档中的数据的有效访问。关于MySQL 8.0 JSON数据类型,后面准备通过一个系列的文章来进行详细的介绍,这样方便大家对MySQL中JSON数据类型的使用有更好的了解;

很多业务人员在用 JSON 数据类型时会遇到各种各样的问题,其中最容易犯的误区就是将类型 JSON 简单理解成字符串类型。但当你看完今天的内容之后,会真正认识到 JSON 数据类型的威力,从而在实际工作中更好地存储非结构化的数据。

与在字符串列中存储JSON格式的字符串相比,JSON数据类型具有以下优势:

* 自动验证存储在JSON列中的JSON文档 。无效的文档会产生错误。 * 优化的存储格式。JSON列中存储的JSON文档将 转换为内部格式,以允许快速读取文档元素。当服务器稍后必须读取以该二进制格式存储的JSON值时,则无需从文本表示形式解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值,而无需读取文档中它们之前或之后的所有值。

MySQL 8.0还支持RFC 7396中定义的JSON合并补丁格式关系型的结构化存储存在一定的弊端,因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中,或许需要扩展单个列的描述功能,这时,如果能用好 JSON 数据类型,那就能打通关系型和非关系型数据的存储之间的界限,为业务提供更好的架构选择。

一、JSON数据类型

存储在JSON列中的任何JSON文档的大小都限于max_allowed_packet系统变量的值。(当服务器内部在内存中操作JSON值时,该值可以大于此值;当服务器存储一个JSON文档时,这个限制就适用了。)可以使用JSON_STORAGE_SIZE()函数获取存储JSON文档所需的空间量 ;

在MySQL 8.0.13之前,JSON列不能具有非NULL默认值。

JSON列与其他二进制类型的列一样,列也不直接建立索引;相反,可以在生成的列上创建索引,以从该JSON列中提取标量值 。

在MySQL 8.0.17及更高版本中,InnoDB 存储引擎支持JSON数组上的多值索引。后面系列的文章会详细进行介绍

二、创建JSON值

JSON数组包含用逗号分隔并包含在[ ] 字符中的值的列表:

["abc", 10, null, true, false]

JSON对象包含一组键值对,以逗号分隔,并包含在{ }字符内:

{"k1": "value", "k2": 10}

如示例所示,JSON数组和对象可以包含字符串或数字的标量值,JSON空文字或JSON布尔值true或false文字。JSON对象中的键必须是字符串。还允许使用时间(日期,时间或日期时间)标量值:

["12:18:29.000000", "2021-05-10", "2021-05-10 12:18:29.000000"]

JSON数组元素和JSON对象键值中允许嵌套:

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

还可以从MySQL提供的许多函数中获取JSON值,也可以使用CAST(value as JSON)将其他类型的值强制转换为JSON类型;后面会专门介绍JSON的相关函数

JSON 如果该值是有效的JSON值,则 尝试将值插入到列中会成功,但如果不是,则尝试失败:

mysql>CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.01 sec)
 
mysql>INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.00 sec)
 
mysq>INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 6 in value for column 't1.jdoc'.

三、JSON合并

MySQL 8.0.3(及更高版本)支持两种合并算法,分别由函数 JSON_MERGE_PRESERVE()和实现 JSON_MERGE_PATCH()。它们在处理重复键的方式上有所不同:JSON_MERGE_PRESERVE()保留重复键的 值,而 JSON_MERGE_PATCH()丢弃除最后一个值以外的所有值。

1、合并数组

在组合多个数组的上下文中,这些数组合并到单个数组中。JSON_MERGE_PRESERVE()通过将稍后命名的数组连接到第一个数组的末尾来实现这一点。JSON_MERGE_PATCH()将每个参数视为一个由单个元素组成的数组(因此索引为0),然后应用“last duplicate key wins”逻辑仅选择最后一个参数。可以比较此查询显示的结果:

8.0支持,5.7不支持
 
mysql>SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]
1 row in set (0.00 sec)

2、合并对象

合并时,多个对象将产生一个对象。JSON_MERGE_PRESERVE()通过组合数组中该键的所有唯一值来处理具有相同键的多个对象;然后将此数组用作结果中该键的值。JSON_MERGE_PATCH() 丢弃从左到右查找重复键的值,以便结果仅包含该键的最后一个值。以下查询说明重复键的结果不同a:

mysql>SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G
*************************** 1. row ***************************
Preserve: {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3}
   Patch: {"a": 4, "b": 2, "c": 5, "d": 3}
1 row in set (0.01 sec)

在需要数组值的上下文中使用的非数组值是自动包装的:该值被[和]字符包围,以将其转换为数组。在下面的语句中,每个参数都自动包装为一个数组([1],[2])。然后合并这些结果以生成单个结果数组。与前两种情况一样,JSON_MERGE_PRESERVE()组合具有相同键的值;JSON_MERGE_PATCH()丢弃除最后一个键以外的所有重复键的值,如下所示:

mysql>SELECT JSON_MERGE_PRESERVE('1', '2') AS Preserve,JSON_MERGE_PATCH('1', '2') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2]   Patch: 2
1 row in set (0.00 sec)

数组和对象值是通过将对象自动包装为数组并通过组合值或根据合并函数的选择(JSON_MERGE_PRESERVE()JSON_MERGE_PATCH())选择“last duplicate key wins”来合并数组,从而合并数组和对象值 ,如以下示例所示:

mysql>SELECT JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch\G
*************************** 1. row ***************************
Preserve: [10, 20, {"a": "x", "b": "y"}]
   Patch: {"a": "x", "b": "y"}
1 row in set (0.00 sec)

四、JSON值的部分更新

在MySQL 8.0中,优化器可以执行JSON列的局部就地更新,而不是删除旧文档并将新文档全部写入该列。可以对满足以下条件的更新执行此优化:

* 正在更新的列被声明为 JSON。

* 该UPDATE语句使用任何的三个功能 JSON_SET(), JSON_REPLACE()或 JSON_REMOVE()更新列。列值的直接赋值(例如,UPDATE mytable SET jcol='{“A”:10,“b”:25}’)不能作为部分更新执行。

在一个UPDATE语句中更新多个JSON列可以用这种方式进行优化;MySQL只能对那些使用刚刚列出的三个函数更新其值的列执行部分更新。

* 输入列和目标列必须是同一列;诸如UPDATE mytable SET jcol1 = JSON_SET(jcol2, ‘$.a’, 100)不能作为部分更新执行的语句。

只要输入列和目标列相同,更新可以以任何组合使用对上一项中列出的任何函数的嵌套调用。

* 所有更改都会用新值替换现有的数组或对象值,并且不会将任何新元素添加到父对象或数组。

* 被替换的值必须至少与替换值一样大。换句话说,新值不能大于旧值。

当以前的部分更新为较大的值留下了足够的空间时,可能会出现此要求的例外情况。可以使用函数JSON_STORAGE_FREE()查看JSON列的任何部分更新释放了多少空间。

可以使用节省空间的紧凑格式将此类部分更新写入二进制日志。可以通过将binlog_row_value_options 系统变量设置为启用此功能PARTIAL_JSON。该参数8.0才开始支持;

本文分享自微信公众号 - DBA的辛酸事儿(dbabitter),作者:SEianG

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-05-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 8.0中的JSON增强

    现在很多应用环境中都能看到JSON灵活的影子。各阶段数据层次的递归层次,能很好的分辨。一直对MySQL的JSON很期待的,最近才有时间研究一下。

    数据和云
  • 到底有多强?苹果的增强现实框架:ARKit

    本篇文章重在 workShop 上苹果工程师的解惑和我对 ARKit 的理解, 最后会简单介绍一下相关技术。

    郭艺帆
  • MySQL 8 新特性介绍

    广受欢迎的开源数据库MySQL 8中,包括了众多新特性,其中包括对Unicode更好的支持、对JSON格式和文档的处理,以及一直以来呼吁增加的象window函数...

    钱曙光
  • Python第十二章-多进程和多线程02-多线程

    MySQL被Sun收购后,搞了个过渡的6.0版本,没多久就下线了(有一次居然听说有人在线上用6.0版本,我惊得下巴都掉了)。被Oracle收购后,终于迎来了像样...

    不会飞的小鸟
  • 嗦一嗦 MySQL 8.0的新特性(一)

    导读 MySQL8.0 GA版本发布了,展现了众多新特性,本系列译文将整理为3篇,为大家介绍升级的部分新特性。 本文为第1篇,重点为大家介绍SQL、JSON上...

    wubx
  • MySQL如何给JSON列添加索引(二)

    上一篇文章《MySQL 8.0 JSON增强到底有多强?(一)》,我们简单介绍了MySQL中JSON数据类型,相信大家对JSON数据类型有了一定的了解,那么今天...

    SEian.G
  • MySQL 8支持文档存储,并带来性能和安全方面的改进

    程序你好
  • MySQL 8 第一个正式版发布:更好的性能

    MySQL 8.0 系列的首个正式版 8.0.11 已发布,官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍,还带来了大量的改进和更快的性能!

    Debian中国
  • 资源丨MySQL故障排查思路方法PPT&视频&24问答

    昨晚,墨天轮邀请到MySQL技术顾问崔虎龙做了题为《一小时掌握MySQL故障排查思路方法》的直播分享,引起了大家的广泛关注,直播后很多小伙伴来找小编询问PPT、...

    数据和云
  • MySQL 8.0.11 (2018-04-19, General Availability)

    仅支持通过使用 in-place 方式从 MySQL 5.7 升级到 MySQL 8.0 升级; 不支持从 MySQL 8.0 降级到 MySQL 5....

    MySQL轻松学
  • 一起来看看1stOpt到底有多强大?

    特别说明:本次采用的1stOpt 5.5 英文试用版,实例全部来自于5.0官方帮助文档。

    matlab爱好者
  • MySQL 8.0中的新增功能

    原文:https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available/

    shaonbean
  • MySQL8.0 JSON函数之创建与返回JSON属性(四)

    经过前面三篇的文章的介绍,相信大家已经对MySQL JSON数据类型有了一定的了解,为了在业务中更好的使用JSON类型,今天我们来具体介绍一下JSON函数的使用...

    SEian.G
  • 相比原生,腾讯云数据库MySQL 8.0带来了哪些新的极致体验?

    官方MySQL 8.0 是非常大的版本,以前的版本号是 5.6、5.7,现在一下飞跃到 8.0,对于 Oracle MySQL官方来说也是非常大的版本,有很多的...

    腾小云
  • 2018-09-10 MariaDB和MySQL全面对比:选择数据库需要考虑这几点

    http://www.infoq.com/cn/articles/mariadb-vs-mysql

    Albert陈凯
  • MySQL 8.0 新特性:多值索引 --如何给JSON数组添加索引(三)

    上一篇文章《MySQL如何给JSON列添加索引(二)》中,我们介绍了如何给JSON列添加索引,那么接下来,我们看下如何给JSON数组添加索引?

    SEian.G
  • SQL的巨大飞跃:MySQL 8.0发布

    “你仍在使用SQL-92吗?”是我在“新SQL”演讲中的开篇问题。在我提出这个问题后,竟然有大部分观众坦承仍在使用25年前的技术。而如果我问谁还在使用Windo...

    IT派
  • 体验 MySQL 8.0 JSON聚合函数

    MySQL 最近的动作很快,已经计划推出 8.0 版本,会新增很多新特性 在 5.7 中,JSON 已经被正式支持,但在 SQL 中对 JSON 的处理能力较弱...

    dys
  • MySQL8.0 JSON函数之搜索JSON值(五)

    之前的几篇文章介绍了JSON数据类型,相信大家已经对JSON有了一定的了解,上面一篇文章介绍了《MySQL8.0 JSON函数之创建与返回JSON属性(四)》J...

    SEian.G

扫码关注云+社区

领取腾讯云代金券