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

不念不忘少年蓝@ 2024-03-23 14:12 47阅读 0赞

#### 目录 ####

*   *  一、前言
     *  二、什么是 JSON
     *  三、Mysql当中json函数
     *  四、JSON值部分更新
     *   *  4.1.使用 Partial Updates 的条件
         *  4.2.如何在 binlog 中开启 Partial Updates
         *  4.3.关于 Partial Updates 的性能测试
     *  五、如何对 JSON 字段创建索引
     *  六、mybatis取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][https_dev.mysql.com_doc_refman_8.0_en_json.html_json-values]

![在这里插入图片描述][0b7d2de28fda4b3b9b939acdbd7c4fb6.png]

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

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

![在这里插入图片描述][2da63372471f480abdf0b451b80a75ce.png]

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

![在这里插入图片描述][9164284ebe17437ba936dfffff06a1eb.png]

**注意：**

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][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

![在这里插入图片描述][566b593725304693b71ff6a5842f1faa.png]  
以 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][https_blog.csdn.net_weixin_43888891_article_details_130419850]

### 六、mybatis取json类型的数据 ###

[https://blog.csdn.net/weixin\_43888891/article/details/130438841][https_blog.csdn.net_weixin_43888891_article_details_130438841]

### 七、总结 ###

*  使用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，但是会存在转义的问题。

[https_dev.mysql.com_doc_refman_8.0_en_json.html_json-values]: https://dev.mysql.com/doc/refman/8.0/en/json.html#json-values
[0b7d2de28fda4b3b9b939acdbd7c4fb6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/5d3e2b06f86645ad932717cbb3c82529.png
[2da63372471f480abdf0b451b80a75ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/5dc19ebe05c446fa906a4d16d74ff174.png
[9164284ebe17437ba936dfffff06a1eb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/55e7b2c90810476baae5fe79d14f4ec7.png
[https_blog.csdn.net_weixin_43888891_article_details_130431272]: https://blog.csdn.net/weixin_43888891/article/details/130431272
[566b593725304693b71ff6a5842f1faa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/6223041a01494442b1c6abedac50e1db.png
[https_blog.csdn.net_weixin_43888891_article_details_130419850]: https://blog.csdn.net/weixin_43888891/article/details/130419850
[https_blog.csdn.net_weixin_43888891_article_details_130438841]: https://blog.csdn.net/weixin_43888891/article/details/130438841