转自: https://blog.csdn.net/hchaoh/article/details/84926180
业务需要灵活的数据结构
通常，我们在使用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 方法作为辅助）
扫一扫
388
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
