【MySQL】处理JSON数据

业务需要灵活的数据结构

通常，我们在使用MySQL这类关系型数据库时，会遵守一些准则来设计表结构。

但实际应用场景与“严格的单一准则”是有差距的。因为实际情况中需要考虑多方面的平衡作出妥协。

如，我们刚学完数据库原理时，往往会倾向于努力设计满足BC范式的表结构，或者至少是满足第三范式的表结构。

但当我们在解决实际工程问题时，可能会作出一些无法满足这些范式要求的表结构设计决议。这些设计在当时可能是一个不错的选择（即使事后我们可能会对自己大肆批判）。

如，我们可能会将“start_time”、“end_time”和“elapsed”三个字段共存，其中 elapsed = end_time - start_time 以减少计算量。这就不满足第三范式了

如，我们可能会让“user_id”和“user_name”这两个字段在task记录中共存，以减少连表查询。这就不满足第二范式了

而我们现在要说的JSON类型的字段则导致了“表中表”，不满足第一范式。

 

因为SQL（Structured Query Language，结构化查询语言）式的数据操作方式比较固化，而现实应用中又经常出现灵活性的需求。

虽然有各种NoSQL数据库可以解决很多这方面的问题，但出于各方面成本的考量，有时候我们会将数据都存在MySQL中。即，给部分数据各自分配一个字段固化，并留一个字段存储其它数据复合后的值。

如，对于一个Task表，我们可以将 'id'，'name'，‘type’ 等数据各自分配一个字段固化，

而 'arg' 的结构因为 'type' 的不同也会不同，所以我们可以设置一个 'arg' 字段，存储Task各项参数复合后的数据。

我们可以自定义对这些复合数据字段的解析规则（也就是序列化和反序列化）。当然更多的是选取JSON作为这类字段的数据结构标准。

 

MySQL JSON 类型字段

以前，我们一般选用 MySQL 的 VARCHAR 或 TEXT 等作为这类复合数据字段的类型。

从5.7.8开始，MySQL将 JSON 作为标准的字段类型之一。

与JSON格式的纯文本字段相比，JSON类型的字段有以下优势：

• 自动校验JSON格式。如果添加的数据不符合JSON规范将会报错。
• 注意：MySQL中合法的JSON字符串格式与我们通常处理的JSON数据可能有些不同。某些场景下，我们习惯将JSON字符串解析成对象（{...}）或数组（[...]），而不考虑单个值的情况（如：“1”）。
• 存储格式经过优化。读取JSON内容项的速度更快。
• 因为MySQL提供的内部数据结构允许通过内容项的key或index直接访问目标数据，而无需将处理其它数据。比以前的上层应用读取整块内容再解析的方式更快。

注：

• 虽然JSON字段可以存储的数据量很大，但它也受 max_allowed_packet 的限制
• 不能为JSON字段指定默认值，即JSON字段默认值是NULL
• 虽然可以通过JSON_EXTRACT方法创建Generated Column字段，再通过该字段创建索引。
• 但这种做法的意义值得商榷，因为既然把该字段的信息放入JSON字段，可能意味着它并不是一个值得固化的标准属性字段，即使它是Generated Column也显得“污染”太重。

示例

创建表

 

CREATE TABLE `t1` (
  `id` INT NOT NULL,
  `f1` VARCHAR(45) NULL,
  `f2` JSON NULL,
  PRIMARY KEY (`id`));

 

 

创建 JSON 字段数据

• 方式1：直接将序列化后的JSON文本存入字段

insert into t1 values (1, 'alpha', '{"a":1, "b":"two"}');

 

 

• 方式2：使用 JSON_OBJECT、JSON_ARRAY、JSON_MERGE 等方法创建对象

insert into t1 values (1, 'alpha', JSON_OBJECT("a", 1, "b", "two"));

insert into t1 values (2, 'beta', JSON_ARRAY("i1", 2, 3.4));

insert into t1 values (
  1,
  'alpha',
  JSON_MERGE(
    '{"a":1}',
    '{"b":"two"}'
  )
);

insert into t1 values (
  1,
  'alpha',
  JSON_MERGE(
    JSON_OBJECT("a", 1),
    JSON_OBJECT("b", "two")
  )
);

insert into t1 values (
  1,
  'alpha',
  JSON_MERGE(
    JSON_OBJECT("a", 1),
    '{"b": "two"}'
  )
);

 

 

注：

• 从 MySQL 5.7.22 开始，JSON_MERGE 被 JSON_MERGE_PRESERVE 替代
• 记得规划好JSON字段内部的业务数据结构，不要被自己搞混
• 另外，可通过 JSON_TYPE 方法查看JSON字段的类型

select f2, JSON_TYPE(f2) from t1;

+----------------------+---------------+
| f2                   | json_type(f2) |
+----------------------+---------------+
| {"a": 1, "b": "two"} | OBJECT        |
| ["1", 2, 3.4]        | ARRAY         |
+----------------------+---------------+

 

读取 JSON 字段中的内容项

• 可通过 JSON_EXTRACT 方法获取 JSON 字段中的某部分数据

 

select f2 from t1 where json_extract(f2, '$.b') = 'two';

+----------------------+
| f2                   |
+----------------------+
| {"a": 1, "b": "two"} |
+----------------------+

 

• 或使用 JSON_EXTRACT 方法的简化形式 ‘->’

 

select f2 from t1 where f2->'$[1]' = 2;

+---------------+
| f2            |
+---------------+
| ["1", 2, 3.4] |
+---------------+

 

 

更改 JSON 字段中的内容项

除了直接将序列化后的JSON文本存入字段外，还可以使用 JSON_INSERT、JSON_REPLACE、JSON_SET、JSON_ARRAY_INSERT 等方法满足不同的需求

 

update t1 set f2 = JSON_INSERT(f2, '$.c', '3') where id=1;

update t1 set f2 = JSON_REPLACE(f2, '$.c', '1+1+1') where id=1;

update t1 set f2 = JSON_SET(f2, '$.c', '1+2') where id=1;

 

• JSON_INSERT：增加内容项；如果内容项（key）已存在，则不做任何改动
• JSON_REPLACE：替换内容项；如果内容项（key）不存在，则不做任何改动
• JSON_SET：设置内容项；如内容项（key）已存在，则替换原值；如果内容项（key）不存在，则添加该内容项

因为有时候JSON字段的原值可能是 NULL（JSON字段默认值是NULL），所以上述方法会失效。这时可以使用 COALESCE 方法指定一个初始值。

 

update t1 set f2 = JSON_SET(COALESCE(f2, '{}'), '$.a', '1') where id=3;

 

 

删除 JSON 字段中的内容项

通过 JSON_REMOVE 方法删除内容项

 

update t1 set f2 = JSON_REMOVE(f2, '$.c') where id=1;

 

*修改部分内容

MySQL 8 会对JSON字段部分内容的修改操作进行优化，它是真的只修改部分内容项，而不是创建一个新的整字段值做整体替换。

 

但是条件比较苛刻：

• 所更新的字段必须是JSON类型
• 只能通过 JSON_SET、JSON_REPLACE、JSON_REMOVE 这三个方法对字段进行赋值
• 且这些方法的输入字段必须是要更新的那个字段
• 更新操作只能是操作已有的内容项，不能增加内容项
• 新字段值所占用的存储空间不能比原值多（如果目标字段原来所剩的空间足以满足新增的空间需求，也符合优化条件）

如果将系统变量 binlog_row_value_options 设置为 PARTIAL_JSON，这些部分内容修改的操作也会被记录到 Binary Log 中。

 

 

 

更多MySQL JSON 方法：More

JSON值的比较与排序：Comparison and Ordering of JSON Values （可考虑使用 CAST 方法作为辅助）