Mysql存储json格式数据需要掌握的

这篇具有很好参考价值的文章主要介绍了Mysql存储json格式数据需要掌握的。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、前言

最近做的一个 填报项目,由于填报的字段比较多于是便在数据库当中使用了longtext类型的字段直接存储json数据。

为什么选择直接存储json数据?

  1. 业务比较复杂,涉及到很多 一对多 的多表关联,假如拆分存到各个业务表,那么每次查询修改是非常繁琐的,存储json一张表即可解决。
  2. 字段全部由前端来定,减少了前后端联调时间。
  3. 避免填报内容需求频繁调整而不断修改数据库结构,比如加字段,减字段这些都是经常有的事,完全可以让前端全权负责,你存什么我就给你返回什么。

但是这里我也是有一点好奇,既然存储为json,为什么不直接将字段类型设置为json?由于最近经常会用到数据库存json串,也是下定决心彻底把这块相关的知识给梳理一遍。

从5.7开始,MySQL开始支持json类型,用于存储JSON数据。关于json类型,mysql官网介绍以及使用,本篇文章也是重点整理的官网相关知识:https://dev.mysql.com/doc/refman/8.0/en/json.html#json-values

Mysql存储json格式数据需要掌握的

json数据类型提供了以下优势:

  1. 插入数据的时候自动验证数据是否是标准的json数据格式,如果不是会报异常!假如使用longtext存储json并不会有这种校验。

Mysql存储json格式数据需要掌握的

  1. 优化存储格式。存储在json列中的JSON文档被转换为内部格式,允许对文档元素进行快速读取访问。可以直接通过键或数组索引查找子对象或嵌套值,而无需读取json中所有值。

Mysql存储json格式数据需要掌握的

注意:

  1. 存储在JSON列中的任何JSON文档的大小都受限于最大允许的数据包系统变量的值,可以使用JSON_STORAGE_SIZE()获取到json长度
  2. 在MySQL 8.0.13之前,JSON列不能有非null的默认值。
  3. 存储JSON文档所需的空间与LONGBLOB或LONGTEXT大致相同
  4. Json类型不支持索引,但是可以在Json当中的列上使用索引!在MySQL 8.0.17及以后的版本中,InnoDB存储引擎支持JSON数组上的多值索引。
  5. MySQL优化器还会在匹配JSON表达式的虚拟列上寻找兼容的索引。
  6. 在MySQL 8.0中,MySQL 优化器可以对JSON列执行局部就地更新,而不是删除旧文档并将整个新文档写入该列。(在后面会重点讲解这一部分)

二、什么是 JSON

JSON 是 JavaScript Object Notation(JavaScript 对象表示法)的缩写,是一个轻量级的,基于文本的,跨语言的数据交换格式。易于阅读和编写。

JSON 的基本数据类型如下:

  1. 数值:十进制数,可以为负数或小数。
  2. 字符串:字符串是由双引号""包围的任意数量Unicode字符的集合,特殊符号使用反斜线转义。
  3. 布尔值:true,false。
  4. 数组:一个由零或多个值组成的有序序列。每个值可以为任意类型。数组使用方括号[] 括起来,元素之间用逗号,分隔。譬如:
 [1, "abc", null, true, "10:27:06.000000", {"id": 1}]
  1. 对象:一个由零或者多个键值对组成的无序集合。其中键必须是字符串,值可以是对象、数组、数字、字符串或者三个字面值(false、null、true)中的一个。值中的字面值中的英文必须使用小写。
    对象使用花括号{}括起来,键值对之间使用逗号 , 分隔,键与值之间用冒号 : 分隔。譬如:
 {"name": "John Doe", "age": 18, "address": {"country" : "china", "zip-code": "10000"}}
  1. 空值:null。

一些合法的JSON的实例:

{"a": 1, "b": [1, 2, 3]}
[1, 2, "3", {"a": 4}]
3.14
"plain_text"

JSON 与 JS 对象的关系
很多人搞不清楚 JSON 和 JS 对象的关系,甚至连谁是谁都不清楚。其实,可以这么理解:
JSON 是 JS 对象的字符串表示法,它使用文本表示一个 JS 对象的信息,本质是一个字符串。如

var obj = {a: 'Hello', b: 'World'}; //这是一个对象,注意键名也是可以使用引号包裹的

var json = '{"a": "Hello", "b": "World"}'; //这是一个 JSON 字符串,本质是一个字符串

JSON 和 JS 对象互转

要实现从JSON字符串转换为JS对象,使用 JSON.parse() 方法:

var obj = JSON.parse('{"a": "Hello", "b": "World"}'); //结果是 {a: 'Hello', b: 'World'}

要实现从JS对象转换为JSON字符串,使用 JSON.stringify() 方法:

var json = JSON.stringify({a: 'Hello', b: 'World'}); //结果是 '{"a": "Hello", "b": "World"}'

简单地说,JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式,例如在 Java中,可以将 JSON 还原为数组或者一个基本对象。

XML本质上也可以作为跨语言的数据交换格式,JSON和XML的可读性可谓不相上下,一边是简易的语法,一边是规范的标签形式,很难分出胜负。

三、Mysql当中json函数

https://blog.csdn.net/weixin_43888891/article/details/130431272

四、JSON值部分更新

4.1.使用 Partial Updates 的条件

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

  • 要更新的列被声明为JSON。
  • UPDATE语句使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()这三个函数中的任意一个来更新列。直接赋值列值(例如,UPDATE mytable SET jcol = '{"a": 10, "b": 25}')不能作为部分更新执行。MySQL只能对使用上面列出的三个函数更新值的列执行部分更新。
  • 输入列和目标列必须是同一列,像UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100)这样的语句不能作为部分更新执行。
  • 所有的更改都用新的值替换现有的数组或对象值,并且不向父对象或数组添加任何新元素。
  • 被替换的值必须至少与替换值一样大。换句话说,新值不能大于旧值。当先前的部分更新为较大的值留下了足够的空间时,可能会出现此需求的异常。您可以使用JSON_STORAGE FREE()函数查看JSON列的任何部分更新释放了多少空间。
  • JSON文档的部分更新只能在列值上执行。对于存储JSON值的用户变量,该值总是被完全替换,即使使用JSON_SET()执行更新:

JSON_STORAGE_FREE(更新后释放的空间)

  • 描述:主要是记录JSON_SET()、JSON_REPLACE()或JSON_REMOVE()使用这三个函数进行就地更新后,其二进制表示形式释放了多少存储空间。
  • 语法:JSON_STORAGE_FREE(json_val)

返回值:

  • 如果参数是一个JSON列值,并按照前面的描述进行了更新,则返回一个正的非零值,这样它的二进制表示比更新之前占用的空间更少。
  • 假如其二进制表示与以前相同或更大,或者如果更新无法利用部分更新,则返回0
  • 假如没有使用那三个函数,也会返回0

创建测试表

mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.38 sec)

mysql> INSERT INTO jtable VALUES
    ->     ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM jtable;
+----------------------------------------------+
| jcol                                         |
+----------------------------------------------+
| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
+----------------------------------------------+
1 row in set (0.00 sec)

现在我们使用JSON_SET()更新列值,这样就可以执行部分更新;在本例中,我们将c键所指向的值(数组[true, false])替换为占用更少空间的值(整数1):

mysql> UPDATE jtable
    ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                      16 |
+-------------------------+
1 row in set (0.00 sec)

4.2.如何在 binlog 中开启 Partial Updates

这种部分更新可以使用压缩格式写入二进制日志,以节省空间;这可以通过将 binlog_row_value_options选项系统变量设置为PARTIAL_JSON来启用。

binlog_row_value_options 参数是MySQL 8.0.3 版本引入的新参数,该参数主要用于JSON类型的字段更新时,只记录更新的那部分数据到binlog,而不是记录完整的JSON数据,这样能够显著减少JSON字段更新产生的binlog文件大小。

需要注意的是,binlog 中使用 部分更新(Partial Updates),只需满足存储引擎层使用 Partial Updates 的前几个条件,无需考虑变更前后,JSON 文档的空间使用是否会增加。

4.3.关于 Partial Updates 的性能测试

首先构造测试数据,t 表一共有 16 个文档,每个文档近 10 MB。

create table t(id int auto_increment primary key,
               json_col json,
               name varchar(100) as (json_col->>'$.name'),
               age int as (json_col->'$.age'));

insert into t(json_col) values
(json_object('name', 'Joe', 'age', 24,
             'data', repeat('x', 10 * 1000 * 1000))),
(json_object('name', 'Sue', 'age', 32,
             'data', repeat('y', 10 * 1000 * 1000))),
(json_object('name', 'Pete', 'age', 40,
             'data', repeat('z', 10 * 1000 * 1000))),
(json_object('name', 'Jenny', 'age', 27,
             'data', repeat('w', 10 * 1000 * 1000)));

insert into t(json_col) select json_col from t;
insert into t(json_col) select json_col from t;

接下来,测试下述 SQL:update t set json_col = json_set(json_col, '$.age', age + 1);

在以下四种场景下的执行时间:

  • MySQL 5.7.36
  • MySQL 8.0.27
  • MySQL 8.0.27,binlog_row_value_options=PARTIAL_JSON
  • MySQL 8.0.27,binlog_row_value_options=PARTIAL_JSON + binlog_row_image=MINIMAL

Mysql存储json格式数据需要掌握的
以 MySQL 5.7.36 的查询时间作为基准:

  • MySQL 8.0 只开启存储引擎层的 Partial Updates,查询时间比 MySQL 5.7 快 1.94 倍。
  • MySQL 8.0 同时开启存储引擎层和 binlog 中的 Partial Updates,查询时间比 MySQL 5.7 快 4.87 倍。
  • 如果在 2 的基础上,同时将 binlog_row_image 设置为 MINIMAL,查询时间更是比 MySQL 5.7 快 102.22 倍。

当然,在生产环境,我们一般很少将 binlog_row_image 设置为 MINIMAL。
但即使如此,只开启存储引擎层和 binlog 中的 Partial Updates,查询时间也比 MySQL 5.7 快 4.87 倍,性能提升还是比较明显的。

五、如何对 JSON 字段创建索引

https://blog.csdn.net/weixin_43888891/article/details/130419850

六、mybatis取json类型的数据

https://blog.csdn.net/weixin_43888891/article/details/130438841文章来源地址https://www.toymoban.com/news/detail-474241.html

七、总结

  • 使用longtext存储json也可以使用函数等操作,既然mysql出了json数据类型,那我们尽量就使用json数据类型来存储json,而且该数据类型还有数据校验。
  • Mysql提供了大量的json相关函数,基于此,我们针对于一些需求完全可以在sql层面使用函数解决问题,而不需要将数据拿到业务层,然后通过业务代码来解决问题。
  • 尽量使用8.0以上的mysql来使用json数据类型存储json
    • mysql提供了JSON_SET()JSON_REPLACE()JSON_REMOVE()三个函数可以进行值部分更新。其效率是5.7版本的5倍!虽然5.7版本也有这几个函数,但是并不是部分更新!
  • 涉及到根据json当中某个字段查询,我们可以通过虚拟列进行建立索引。同时在MySQL 8.0.17及以后的版本中,InnoDB存储引擎支持JSON数组上的多值索引。
  • 假如使用的是mybatis框架,如果图省事可以直接使用JSONObject来接受前端的值,也可以作为返回值使用,当然也可以自定义json的Java对象,但是都需要设置typeHandler,好处是中间不会出现转义问题,而使用String不需要设置typeHandler,但是会存在转义的问题。

到了这里,关于Mysql存储json格式数据需要掌握的的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • mysql处理json格式的字段,一文搞懂mysql解析json数据

    略。自行百度。 JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前,只能通过字符类型(CHAR,VARCHAR 或 TEXT )来保存 JSON 文档。 MySQL 8.0版本中增加了对JSON类型的索引支持。可以使用CREATE INDEX语句创建JSON类型的索引,提高JSON类型数据的查询效率。 存储JSON文档所需的空间与存储

    2024年02月07日
    浏览(51)
  • MySQL中使用JSON存储数据

    1.概述: MySQL从5.7版本开始引入了对JSON数据类型的原生支持。 这个增强功能使开发人员能够直接在数据库中存储、操作和查询JSON数据。 MySQL的JSON字段为存储半结构化数据提供了更加灵活和高效的方式。 相比传统的关系型数据库,使用JSON字段的优势包括: 灵活性:JSON字段可

    2024年02月09日
    浏览(30)
  • mysql 使用JSON_EXTRACT提取 json数据格式的字段

    task 表中有如下数据结构 id data_ext 1 {“name”: “张三”,“gender”: 1,“age”: 20} 提取data_ext字段 JSON_EXTRACT:提取字段。 JSON_UNQUOTE:去掉双引号。 cast :转换字符显示。 《MySQL JSON_EXTRACT()、JSON_UNQUOTE() 函数获取JSON串的值》:https://blog.csdn.net/TM_enn/article/details/125786948

    2024年02月16日
    浏览(42)
  • spring security + oauth2 使用RedisTokenStore 以json格式存储

    1.项目架构  2.自己对 TokenStore 的 redis实现 3.自定义序列化类 4.配置使用redis类型的 TokenStore 5.完成认证模块的编写后,测试登录    json已经保存在数据库了 但是!!!!!,保存没问题,取出来的时候就有问题了,把这三个文件复制到资源服务器,让资源服务器也用MyRedisTokenStore 的方式读

    2024年02月14日
    浏览(34)
  • springboot 集成log4j日志,需要自定义json格式内容输出方便ES采集

    公司需要将服务迁移到K8S环境上,由于目前服务输出的格式不符合ES进行采集的日志格式,所有需要将日志输出的格式进行调整为JSON格式,方便ES采集 之前是直接配置的输出格式的message为 \\\"message\\\": %msg\\\" ,但是由于打日志需要打印json内容的日志就没有进行转义导致,整体输出

    2024年02月12日
    浏览(38)
  • 【MySQL】JSON 格式字段处理

    MySQL 5.7 版本后已支持 JSON 格式,这虽是 MySQL 的一小步,但可以说是程序开发的一大步,再也不用将 JSON 内容塞到 VARCHAR  类型字段了,程序设计也会变得更加灵活。网上大多只针对 JSONObject 对象类型,本文也将详解  JSONArray 数组类型。 JSON (JavaScript Object Notation)是一种轻量

    2024年02月12日
    浏览(46)
  • mysql存储json类型方法和利弊

    利弊 一、json类型的特性 1、保证了JSON数据类型的强校验,JSON数据列会自动校验存入此列的内容是否符合JSON格式,非正常格式则报错,而varchar类型和text等类型本身是不存在这种机制的。 2、MySQL同时提供了一组操作JSON类型数据的内置函数。 3、更优化的存储格式,存储在JS

    2024年02月05日
    浏览(33)
  • jackson库收发json格式数据和ajax发送json格式的数据

    一、jackson库收发json格式数据   jackson库是maven仓库中用来实现组织json数据功能的库。 json格式  json格式一个组织数据的字符文本格式,它用键值对的方式存贮数据,json数据都是有一对对键值对组成的,键只能是字符串,用双引号包括;值可以是字符串,数字,布尔表达式

    2024年02月13日
    浏览(46)
  • Mysql 查询最近12个月的数据

    示例:  比如现在是2024年 1月, 我们想查询最近12个月的数据。 那么就是 2024-01 的数据,以及 2023-02 到2023-12的数据,一共12个月数据。 ps: 当然起始我们能自然而然拿到最近12个月的起始和结束时间,那么开闭区间怎么用是取决于我们自己。 那么我们通过sql怎么实现呢? 

    2024年02月02日
    浏览(40)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包