Json 部分数据的更新

OceanBase 数据库支持对 Json 部分数据的更新（Json Partial Update）。当只需修改 Json 文档中的特定字段时，该特性允许仅更新所更改的部分，而无需全量更新整个 Json 文档。

使用限制

Json Partial Update 开关

OceanBase 数据库的 Json Partial Update 功能默认是不开启的，开关由系统变量 log_row_value_options 控制，详细信息，参见 log_row_value_options。

示例如下：

• 开启 Json Partial Update 功能。

  • Session 级别：

  SET log_row_value_options="partial_json";
  

  • Global 级别：

  SET GLOBAL log_row_value_options="partial_json";
  

• 关闭 Json Partial Update 功能。

  • Session 级别：

  SET log_row_value_options="";
  

  • Global 级别：

  SET GLOBAL log_row_value_options="";
  

• 查询 log_row_value_options 的值。

  SHOW VARIABLES LIKE 'log_row_value_options';
  

  返回结果如下：

  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | log_row_value_options |       |
  +-----------------------+-------+
  1 row in set
  

可用于 Partial Update 的 Json 表达式

除了 Json Partial Update 功能开关 log_row_value_options 外，还需要使用特定的表达式更新 Json 文档，才能触发 Json Partail Update。

当前 OceanBase 数据库 MySQL 模式可以进行 Partial Update 的 Json 表达式如下：

• json_set 或 json_replace：用于更新 Json 字段的值。
• json_remove：用于删除 Json 字段。

示例如下：

1. 创建表 json_test。

   CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON);
   

2. 插入数据。

   INSERT INTO json_test VALUES(1, CONCAT('{"name": "John", "content": "', repeat('x',8), '"}'));
   

   返回结果如下：

   Query OK, 1 row affected
   

3. 查询 Json 列 j 的数据。

   SELECT j FROM json_test;
   

   返回结果如下：

   +-----------------------------------------+
   | j                                       |
   +-----------------------------------------+
   | {"name": "John", "content": "xxxxxxxx"} |
   +-----------------------------------------+
   1 row in set
   

4. 使用 json_repalce 更新 Json 列 name 字段的值。

   UPDATE json_test SET j = json_replace(j, '$.name', 'ab') WHERE pk = 1;
   

   返回结果如下：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

5. 查询 Json 列 j 修改后的数据。

   SELECT j FROM json_test;
   

   返回结果如下：

   +---------------------------------------+
   | j                                     |
   +---------------------------------------+
   | {"name": "ab", "content": "xxxxxxxx"} |
   +---------------------------------------+
   1 row in set
   

6. 使用 json_set 更新 Json 列 name 字段的值。

   UPDATE json_test SET j = json_set(j, '$.name', 'cd') WHERE pk = 1;
   

   返回结果如下：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

7. 查询 Json 列 j 修改后的数据。

   SELECT j FROM json_test;
   

   返回结果如下：

   +---------------------------------------+
   | j                                     |
   +---------------------------------------+
   | {"name": "cd", "content": "xxxxxxxx"} |
   +---------------------------------------+
   1 row in set
   

8. 使用 json_remove 删除 Json 列 name 字段值。

   UPDATE json_test SET j = json_remove(j, '$.name') WHERE pk = 1;
   

   返回结果如下：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

9. 查询 Json 列 j 修改后的数据。

   SELECT j FROM json_test;
   

   返回结果如下：

   +-------------------------+
   | j                       |
   +-------------------------+
   | {"content": "xxxxxxxx"} |
   +-------------------------+
   1 row in set
   

更新粒度

OceanBase 数据库的 Json 的数据基于 Lob 存储的，而 OceanBase 数据库的 Lob 底层是分块存储的，所以每次 Partial Update 的数据量最小为一个 Lob 分块。如果 Lob 分块越小，那么写的数据量就会越小。为此也提供了设置 Lob 分块大小的 DDL 语法，可以在创建列时指定。

示例如下：

CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON CHUNK '4k');

Chunk Size 不能无限小，太小了会影响 SELECT、INSERT 和 DELETE 的性能。一般建议根据 Json 文档的平均字段大小来设置，如果大部分字段都很小，那可以设置为 1K。OceanBase 数据库为了优化 Lob 类型的读，对于小于 4K 的数据，就直接 INROW 存储了，此时是不会进行 Partial Update。Partial Update 主要还是为了提高大文档更新的性能，小文档全量更新性能反而更好。

Rebuild

Json Partial Update 不会对 Json 列更新前后数据长度有所限制。对于新值的长度小于等于旧值的长度，则直接将原始位置的数据替换为新数据。当新值的长度大于旧值的长度时，则会在最后 Append新数据。OceanBase 数据库设置了一个阈值，当 Append 的数据长度，超过原始数据长度的 30% 时，就会进行 Rebuild，此时不会进行 Partial Update，而是全量覆盖写。

可以使用 JSON_STORAEG_SIZE 表达式获取 JSON 实际的存储长度，以及 JSON_STORAGE_FREE 获取额外的存储开销。

示例如下：

1. 开启 Json Partial Update。

   SET log_row_value_options = "partial_json";
   

2. 创建测试表 json_test。

   CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON CHUNK '1K');
   

3. 向表 json_test 插入一行数据。

   INSERT INTO json_test VALUES(10 , json_object('name', 'zero', 'age', 100, 'position', 'software engineer', 'profile', repeat('x', 4096), 'like', json_array('a', 'b', 'c'), 'tags', json_array('sql boy', 'football', 'summer', 1), 'money' , json_object('RMB', 10000, 'Dollers', 20000, 'BTC', 100), 'nickname', 'noone'));
   

   返回结果如下：

   Query OK, 1 row affected
   

4. 通过 JSON_STORAGE_SIZE 查询 Json 列存储大小（实际占用的存储空间），通过 JSON_STORAGE_FREE 估计 Json 列可以释放的存储空间。

   SELECT JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM json_test WHERE pk = 10;
   

   返回结果如下：

   +----------------------+----------------------+
   | JSON_STORAGE_SIZE(j) | JSON_STORAGE_FREE(j) |
   +----------------------+----------------------+
   |                 4335 |                    0 |
   +----------------------+----------------------+
   1 row in set
   

   由于没有做过 Partial Update，所以 JSON_STORAGE_FREE 值为 0。

5. 使用 json_replace 更新 Json 列 position 字段的值，新值的长度小于旧值的长度。

   UPDATE json_test SET j = json_replace(j, '$.position', 'software enginee') WHERE pk = 10;
   

   返回结果如下：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

6. 再次通过 JSON_STORAGE_SIZE 查询 Json 列存储大小，通过 JSON_STORAGE_FREE 估计 Json 列可以释放的存储空间。

   SELECT JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM json_test WHERE pk = 10;
   

   返回结果如下：

   +----------------------+----------------------+
   | JSON_STORAGE_SIZE(j) | JSON_STORAGE_FREE(j) |
   +----------------------+----------------------+
   |                 4335 |                    1 |
   +----------------------+----------------------+
   1 row in set
   

   Json 列数据更新后，由于新数据比旧数据少一个字节，所以 JSON_STORAGE_FREE 结果为 1。

7. 使用 json_replace 更新 Json 列 position 字段的值，新值的长度大于旧值的长度。

   UPDATE json_test SET j = json_replace(j, '$.position', 'software engineera') WHERE pk = 10;
   

   返回结果如下：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

8. 再次通过 JSON_STORAGE_SIZE 查询 Json 列存储大小，通过 JSON_STORAGE_FREE 估计 Json 列可以释放的存储空间。

   SELECT JSON_STORAGE_SIZE(j), JSON_STORAGE_FREE(j) FROM json_test WHERE pk = 10;
   

   返回结果如下：

   +----------------------+----------------------+
   | JSON_STORAGE_SIZE(j) | JSON_STORAGE_FREE(j) |
   +----------------------+----------------------+
   |                 4355 |                   19 |
   +----------------------+----------------------+
   1 row in set
   

   Json 列数据 Append 更新后，JSON_STORAGE_FREE 长度为 19，表示 Rebuild 后，可以释放 19 字节。