MySQL 利用JSON特性完成复杂数据存储和查询

MySQL 利用JSON特性完成复杂数据存储和查询

引用

CSDN

1.

https://blog.csdn.net/liangmengbk/article/details/144172360

在数据库设计中，如何存储结构复杂且多变的数据一直是一个挑战。本文通过一个文库类信息存储的场景，详细介绍了如何在MySQL中使用JSON数据类型来存储和查询复杂数据，并通过创建虚拟列和索引优化查询性能。

情景描述

下面是一个应用场景：数据库需要存储文库类的信息。文库分多个种类，比如图书类、论文类等多个类别，每个类别有不同的字段信息。

常规处理方法

要在单张表中去存储不同种类的文库数据，表就会变成这样的结构：

查询语句会是下面这样：

这样的设计会有以下问题：

使用JSON处理

利用JSON解决动态数据问题，MySQL 5.7以后提供了JSON数据类型，可以直接对JSON存储、提取与解析。

因为JSON是弱约束的，因此存储数据非常灵活，同时也可基于虚拟列实现索引优化。

表结构变为如下格式：

表中插入一些测试数据（对应的SQL脚本在文章底部）：

根据表中的dist_request_id字段值进行数据查找，SQL如下：

SELECT * from document WHERE extra->'$.dist_request_id'='5bdba7333cda2ed6f3020cf44c43e2e5';

extra-> 指要提取某个字段
$.XXX，$.表示要解析的是JSON数据，XXX是对应JSON中的属性名称。

上面的SQL执行时，将会对整个表进行全表扫描，数据量大时，查询就会慢。

增加虚拟列，解决上述问题，虚拟列是需要跟JSON中的某个属性名称进行对应的。

对JSON中dist_request_id属性创建虚拟列v_request_id，对应的SQL如下：

ALTER TABLE document 
Add COLUMN `v_request_id` varchar(32)  
GENERATED ALWAYS AS (json_unquote(json_extract(`extra`,_utf8mb4'$.dist_request_id'))) VIRTUAL NULL;

SQL解读：

• json_extract 代表从指定属性名称中提取对应的属性的值。
• json_unquote 去掉JSON中的引号，转为字符串。
• VIRTUAL 代表该列为虚拟列。
• null 代表字段允许为空。

执行完毕新增虚拟列脚本后，再次查询表数据：

会发现表中多了一列数据，这一列数据就是虚拟列。此时的表结构如下：

虚拟列的数据会自动跟着JSON字符串种关联的属性值变化而变化。

针对虚拟字段进行条件查询，发现仍然是全表扫描，因为还没有建立索引。

现在对虚拟字段建立索引：

create index idx_v_request_id on document(v_request_id);

再次查看执行计划：

此时，索引已经生效了。

通过上面的案例分析，如果要对JSON中其他字段进行查询，同样也可以创建虚拟列，目的就是把JSON中的属性名称映射为一个一个的字段，然后对字段创建索引。

测试数据脚本

使用到的表和数据对应的SQL脚本：

DROP TABLE IF EXISTS `document`;
CREATE TABLE `document`  (
  `doc_id` int(0) NOT NULL AUTO_INCREMENT,
  `doc_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `extra` json NULL,
  PRIMARY KEY (`doc_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of document
-- ----------------------------
INSERT INTO `document` VALUES (1, '十万个为什么', '{\"trade\": [{\"id\": 1, \"name\": \"幼儿\"}, {\"id\": 1, \"name\": \"科普\"}], \"doc_id\": 1, \"doc_name\": \"十万个为什么\", \"properties\": {\"isbn\": \"123456\", \"出版社\": \"文艺出版社\"}, \"doc_type_id\": 1, \"doc_type_name\": \"图书类\", \"dist_request_id\": \"5bdba7333cda2ed6f3020cf44c43e2e5\"}');
INSERT INTO `document` VALUES (2, 'Java从入门到精通', '{\"trade\": [{\"id\": 1, \"name\": \"编程\"}, {\"id\": 1, \"name\": \"IT\"}], \"doc_id\": 1, \"doc_name\": \"Java从入门到精通\", \"properties\": {\"isbn\": \"4556655\", \"出版社\": \"人民邮电出版社\"}, \"doc_type_id\": 1, \"doc_type_name\": \"图书类\", \"dist_request_id\": \"63e0f3dde01d2f0b5c3dda986cf563b6\"}');
INSERT INTO `document` VALUES (3, '论如何成为一个高手', '{\"trade\": [{\"id\": 1, \"name\": \"杂志\"}], \"doc_id\": 1, \"doc_name\": \"论如何成为一个高手\", \"properties\": {\"刊号\": \"20241201\", \"杂志\": \"测试杂志数据BBBB\", \"版面\": \"测试版面数据AAAA\"}, \"doc_type_id\": 2, \"doc_type_name\": \"论文类\", \"dist_request_id\": \"904468b02142dd8d4412254e0fa503f6\"}');