当前位置：首页 > news >正文

利用JSON数据类型优化关系型数据库设计

news 来源：原创 2025/5/19 2:36:42

利用JSON数据类型优化关系型数据库设计

前言

在关系型数据库中，传统的结构化存储方式要求预先定义好所有的列及其数据类型。

然而，随着业务的发展，这种设计可能会显得不够灵活，尤其是在需要扩展单个列的描述功能时。

JSON数据类型的引入，为关系型数据库提供了存储非结构化数据的能力，打破了关系型与非关系型数据库之间的界限。

本文将深入探讨JSON数据类型的优势，并通过实际案例展示如何在业务中有效使用JSON类型。

一、JSON数据类型的优势

1. 灵活的数据结构

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，支持复杂的数据结构，包括对象和数组。

与传统的固定列结构不同，JSON类型允许字段的无限扩展，无需预先定义所有列。

这种灵活性非常适合存储动态或非结构化的数据。

2. 支持多种数据类型

JSON不仅支持字符串、整型、浮点数等基本数据类型，还支持嵌套的JSON对象和数组。

例如，可以存储图片的元数据、用户的登录信息或用户画像标签等复杂数据。

3. 高效的查询与索引

从MySQL 5.7版本开始，JSON类型支持函数索引和多值索引（Multi-Valued Indexes），这使得在JSON字段上进行高效查询成为可能。

通过虚拟列和索引，可以显著提升查询性能。

4. 简化表结构设计

使用JSON类型可以减少表的列数，避免频繁执行 ALTER TABLE操作来添加新列。

这对于需要频繁扩展字段的业务场景非常有用。

二、JSON类型的基本用法

1. JSON对象与数组

JSON对象是由键值对组成的无序集合，而JSON数组是由有序的值组成的列表。例如：

JSON对象：存储图片的元数据

{"Image": {"Width": 800,"Height": 600,"Title": "View from 15th Floor","Thumbnail": {"Url": "http://www.example.com/image/481989943","Height": 125,"Width": 100},"IDs": [116, 943, 234, 38793]}
}

JSON数组：存储多个地理位置信息

[{"precision": "zip","Latitude": 37.7668,"Longitude": -122.3959,"City": "SAN FRANCISCO","State": "CA"},{"precision": "zip","Latitude": 37.371991,"Longitude": -122.026020,"City": "SUNNYVALE","State": "CA"}
]

2. JSON类型的存储与查询

在MySQL中，JSON类型的数据可以通过 JSON_EXTRACT和 ->>等操作符进行查询。例如：

SELECT userId, loginInfo->>"$.cellphone" AS cellphone
FROM UserLogin;

三、实战案例：用户登录信息存储

1. 表结构设计

假设一个用户可以通过手机、微信、QQ等多种方式登录，我们可以使用JSON类型存储登录信息：

CREATE TABLE UserLogin (userId BIGINT NOT NULL,loginInfo JSON,PRIMARY KEY(userId)
);

2. 插入数据

插入用户登录信息：

INSERT INTO UserLogin VALUES 
(1, '{"cellphone": "13918888888", "wxchat": "破产码农", "QQ": "82946772"}'),
(2, '{"cellphone": "15026888888"}');

3. 查询数据

通过 ->>操作符提取JSON字段：

SELECT userId, loginInfo->>"$.cellphone" AS cellphone
FROM UserLogin;

4. 创建虚拟列与索引

为了优化查询性能，可以创建虚拟列并为其添加索引：

ALTER TABLE UserLogin 
ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");ALTER TABLE UserLogin 
ADD UNIQUE INDEX idx_cellphone(cellphone);

四、实战案例：用户画像标签存储

1. 表结构设计

在用户画像场景中，可以使用JSON数组存储用户的标签：

CREATE TABLE UserTag (userId BIGINT NOT NULL,userTags JSON,PRIMARY KEY(userId)
);

2. 插入数据

插入用户标签数据：

INSERT INTO UserTag VALUES 
(1, '[2, 6, 8, 10]'),  -- 80后、高学历、小资、有房、常看电影
(2, '[3, 10, 12]');   -- 90后、常看电影、爱外卖

3. 多值索引与查询

从MySQL 8.0.17开始，支持在JSON数组上创建多值索引：

ALTER TABLE UserTag
ADD INDEX idx_user_tags ((CAST(userTags->"$" AS UNSIGNED ARRAY)));

通过 MEMBER OF、JSON_CONTAINS等函数进行高效查询：

-- 查询常看电影的用户
SELECT * FROM UserTag 
WHERE 10 MEMBER OF(userTags->"$");-- 查询80后且常看电影的用户
SELECT * FROM UserTag 
WHERE JSON_CONTAINS(userTags->"$", '[2, 10]');

五、总结

JSON数据类型为关系型数据库提供了存储和处理非结构化数据的能力，极大地增强了数据库的灵活性。

通过合理使用JSON类型，可以有效解决业务中的动态字段扩展、复杂数据存储等问题。

然而，使用JSON类型时也需要注意以下几点：

避免滥用：JSON类型适合存储动态或非结构化数据，但对于固定结构的字段，仍建议使用传统的列存储。
索引优化：通过虚拟列和多值索引，可以显著提升JSON字段的查询性能。
版本兼容性：JSON类型从MySQL 5.7开始支持，建议在生产环境中使用MySQL 8.0及以上版本，以获得更好的性能和功能支持。

– 欢迎点赞、关注、转发、收藏【我码玄黄】，各大平台同名。

利用JSON数据类型优化关系型数据库设计

利用JSON数据类型优化关系型数据库设计前言在关系型数据库中，传统的结构化存储方式要求预先定义好所有的列及其数据类型。然而，随着业务的发展，这种设计可能会显得不够灵活，尤其是在需要扩展单个列的描述功能时。 JSON数据…...

编程日记 2025/5/19 2:36:42

如何学习Java后端开发

文章目录一、Java 语言基础二、数据库与持久层三、Web 开发基础四、主流框架与生态五、分布式与高并发六、运维与部署七、项目实战八、持续学习与提升总结路线图学习 Java 后端开发需要系统性地掌握多个技术领域，从基础到进阶逐步深入。以下是一个详细的学习路线和…...

编程日记 2025/5/14 2:35:43

AI刷题-蛋糕工厂产能规划、优质章节的连续选择

挑两个简单的写写目录一、蛋糕工厂产能规划问题描述输入格式输出格式解题思路： 问题理解数据结构选择算法步骤关键点最终代码： 运行结果：编辑二、优质章节的连续选择问题描述输入格式输出格式解题思路&a…...

编程日记 2025/5/19 2:27:04

SpringBoot统一数据返回格式统一异常处理

统一数据返回格式 & 统一异常处理 1. 统一数据返回格式1.1 快速入门1.2 存在问题1.3 案列代码修改1.4 优点 2. 统一异常处理 1. 统一数据返回格式强制登录案例中,我们共做了两部分⼯作通过Session来判断⽤⼾是否登录对后端返回数据进⾏封装,告知前端处理的结果回顾后…...

编程日记 2025/5/14 5:23:28

[MySQL]事务的理论、属性与常见操作

目录一、事物的理论 1.什么是事务 2.事务的属性（ACID） 3.再谈事务的本质 4.为什么要有事务二、事务的操作 1.事务的支持版本 2.事务的提交模式介绍自动提交模式手动提交模式 3.事务的操作 4.事务的操作演示验证事务的回滚事务异常…...

编程日记 2025/5/17 12:55:27

JWT实现单点登录

文章目录 JWT实现单点登录JWT 简介存在问题及解决方案登录流程后端程序实现前端保存Tokenstore存放信息的缺点及解决校验流程：为gateway增加登录校验拦截器另一种单点登录方法：Token＋Redis实现单点登录 JWT实现单点登录登录流程&#xff…...

编程日记 2025/5/19 2:10:15

docker 学习笔记

一、docker容器快速上手以及简单操作 docker的image和container image镜像 docker image就是一个read.only文件，可以理解成一个模版，docker image具有分层的概念可以自己制作，也可以从registry拉去 container容器一个运行中的docker …...

编程日记 2025/5/19 2:31:58

Lesson 119 A true story

Lesson 119 A true story 词汇 story n. 故事，传记，小说，楼层storey 搭配：tell a story 讲故事，说谎　　　true story 真实的故事　　　the second floor 二楼例句：我猜他正在说谎。　　　I guess he…...

编程日记 2025/5/19 2:04:07

c语言版贪吃蛇（Pro Max版）附源代码

1 背景贪吃蛇是一款经典的电子游戏，最早出现在20世纪70年代的街机游戏中。游戏的核心玩法是玩家控制一条蛇在有限的空间内移动，通过吃食物来增长身体长度，同时避免撞到墙壁、障碍物或自身。随着蛇的长度增加，游戏难度逐渐提升。 …...

编程日记 2025/5/16 11:06:43

蓝桥村打花结的花纸选择问题

在这篇文章中，我们将探讨一个有趣的算法问题，这个问题涉及到中国传统手工艺——打花结。我们需要判断给定的矩形花纸是否可以通过折叠操作使其面积变为特定的值 X，从而适合用来打花结。问题描述解题思路这个问题可以通过循环方法来解决。…...

编程日记 2025/5/18 7:09:30

SSM开发(三) spring与mybatis整合(含完整运行demo源码)

目录本文主要内容一、Spring整合MyBatis的三个关键点二、整合步骤 1、创建一个Maven项目 2、在pom.xml文件中添加jar包的依赖 3、配置MyBatis 注解实现方式 XML配置文件实现 4、配置Spring 5、测试运行本文主要内容 1. Spring + Mybatis整合； 2. MyBatis两种SQL…...

编程日记 2025/5/12 14:48:04

【Matlab高端绘图SCI绘图模板】第006期对比绘柱状图（只需替换数据）

1. 简介柱状图作为科研论文中常用的实验结果对比图，本文采用了3组实验对比的效果展示图，代码已调试好，只需替换数据即可生成相关柱状图，为科研加分。通过获得Nature配色的柱状图，让你的论文看起来档次更高&#xff0…...

编程日记 2025/5/17 3:47:25

Elasticsearch中的度量聚合：深度解析与实战应用

在大数据和实时分析日益重要的今天，Elasticsearch以其强大的搜索和聚合能力，成为了众多企业和开发者进行数据分析和处理的首选工具。本文将深入探讨Elasticsearch中的度量聚合（Metric Aggregations），展示其如何在数据分…...

编程日记 2025/5/19 1:53:08

重回C语言之老兵重装上阵（十六）C语言可变参数

C语言可变参数在C语言中，标准库提供了一些函数允许接收可变数量的参数。最典型的例子就是 printf 和 scanf，它们能够处理不确定数量的参数。为了实现这一功能，C语言提供了可变参数函数的概念。 1. 可变参数函数的概念可变参数函数是指函数…...

编程日记 2025/5/19 2:33:25

第4章神经网络【1】——损失函数

4.1.从数据中学习实际的神经网络中，参数的数量成千上万，因此，需要由数据自动决定权重参数的值。 4.1.1.数据驱动数据是机器学习的核心。我们的目标是要提取出特征量，特征量指的是从输入数据/图像中提取出的本质的数 …...

编程日记 2025/5/19 2:36:42

动态规划——斜率优化DP

题目清单 acwing300.任务安排1 状态表示f[i]: 集合：完成前i个任务且第i个任务为最后一个批次最后一个任务的方案。属性：min 状态计算： f [ i ] m i n { f [ j ] s u m t [ i ] ∑ j 1 i w [ u ] s ∑ j 1 n w [ i ] } f[i]min\{f[j…...

编程日记 2025/5/16 10:40:03

函数栈帧的创建和销毁

1、总述： 大家在前期学习函数的时候，肯定会有诸多疑惑： 1、局部变量怎么创建的？ 2、为什么有时候局部变量是随机值？ 3、函数是怎么传参的？传参的顺序如何？ 4、形参和实参是什么样的关系&am…...

编程日记 2025/5/14 13:53:58

【MQ】探索 Kafka

高性能消息的顺序性、顺序写磁盘零拷贝 RocketMQ内部主要是使用基于mmap实现的零拷贝，用来读写文件减少cpu的拷贝次数和上下文切换次数，实现文件的高效读写操作 Kafka 零拷贝 Kafka 使用到了 mmap 和 sendfile 的方式来实现零拷贝。分别对应 Jav…...

编程日记 2025/5/18 17:30:57

c++ set/multiset 容器

1. set 基本概念简介： 所有元素都会在插入时自动排序本质： set/multiset属于关联式容器，底层结构是用二叉树实现。set 和 multiset 区别： set容器不允许有重复的元素。 multiset允许有重复的元素。2. set 构造和赋值构造&a…...

编程日记 2025/5/19 2:19:43

react-bn-面试

1.主要内容工作台待办实现思路： 1，待办list由后端返回，固定需要的字段有id(查详细)、type(本条待办的类型)，还可能需要时间，状态等 2，一个集中处理待办中转路由页，所有待办都跳转到这个页面…...

编程日记 2025/5/19 2:28:45

【C++数论】880. 索引处的解码字符串|2010

本文涉及知识点数论：质数、最大公约数、菲蜀定理 LeetCode880. 索引处的解码字符串给定一个编码字符串 s 。请你找出解码字符串并将其写入磁带。解码时，从编码字符串中每次读取一个字符 ，并采取以下步骤： 如果所读的字符是…...

编程日记 2025/5/17 23:24:30

shiro学习五：使用springboot整合shiro。在前面学习四的基础上，增加shiro的缓存机制，源码讲解：认证缓存、授权缓存。

文章目录前言1. 直接上代码最后在讲解1.1 新增的pom依赖1.2 RedisCache.java1.3 RedisCacheManager.java1.4 jwt的三个类1.5 ShiroConfig.java新增Bean 2. 源码讲解。2.1 shiro 缓存的代码流程。2.2 缓存流程2.2.1 认证和授权简述2.2.2 AuthenticatingRealm.getAuthentication…...

编程日记 2025/5/17 20:48:30

Python案例--养兔子

兔子繁殖问题是一个经典的数学问题，最早由意大利数学家斐波那契在13世纪提出。这个问题不仅在数学领域具有重要意义，还广泛应用于计算机科学、生物学和经济学等领域。本文将通过一个具体的Python程序，深入探讨兔子繁殖问题的建模和实现&#…...

编程日记 2025/5/16 17:41:24

【搜索回溯算法】：BFS的魔力--如何使用广度优先搜索找到最短路径

✨感谢您阅读本篇文章，文章内容是个人学习笔记的整理，如果哪里有误的话还请您指正噢✨ ✨ 个人主页：余辉zmh–CSDN博客 ✨ 文章所属专栏：搜索回溯算法篇–CSDN博客文章目录一.广度优先搜索（BFS）解决最短路…...

编程日记 2025/5/12 22:56:25

JavaSE第十一天——集合框架Collection

一、List接口 List接口是一个有序的集合，允许元素有重复，它继承了Collection接口，提供了许多额外的功能，比如基于索引的插入、删除和访问元素等。常见的List接口的实现类有ArrayList、LinkedList和Vector。 List接口的实现类 …...

编程日记 2025/5/17 17:40:54

Three城市引擎地图插件Geo-3d

一、简介基于Three开发，为Three 3D场景提供GIS能力和城市底座渲染能力。支持Web墨卡托、WGS84、GCJ02等坐标系，支持坐标转换，支持影像、地形、geojson建筑、道路，植被等渲染。支持自定义主题。二、效果三、代码 //插件初始化…...

编程日记 2025/5/18 6:32:22

深度学习｜表示学习｜卷积神经网络｜详细推导每一层的维度变化｜14

如是我闻： 一个经典的卷积神经网络（CNN）架构，呈现的是输入图像通过多个卷积层、池化层以及全连接层，最终输出分类结果的过程。整个过程的核心是理解输入特征图的尺寸如何在每一层发生变化，我们可以通过卷积…...

编程日记 2025/5/14 18:27:16

多级缓存（亿级并发解决方案）

多级缓存（亿级流量（并发）的缓存方案） 传统缓存的问题传统缓存是请求到达tomcat后，先查询redis，如果未命中则查询数据库，问题如下： （1）请求要经过tomcat处…...

编程日记 2025/5/15 22:52:42

BOM对象location与数组操作结合——查询串提取案例

BOM对象location与数组操作结合——查询串提取案例前置知识 1. Location 对象 Location 对象是 JavaScript 提供的内置对象之一，它表示当前窗口或框架的 URL，并允许你通过它操作或获取 URL 的信息。可以通过 window.location 访问。主要属性&#…...

编程日记 2025/5/18 16:32:50

读书笔记--分布式服务架构对比及优势

本篇是在上一篇的基础上，主要对共享服务平台建设所依赖的分布式服务架构进行学习，主要记录和思考如下，供大家学习参考。随着企业各业务数字化转型工作的推进，之前在传统的单一系统（或单体应用）模式中&#…...

编程日记 2025/5/18 11:53:06

GOGOGO 枚举

含义：一种类似于类的一种结构作用：是Java提供的一个数据类型，可以设置值是固定的【当某一个数据类型受自身限制的时候，使用枚举】语法格式： public enum 枚举名{…… }有哪些成员？ A、对象 public …...

编程日记 2025/5/18 1:06:23

【Linux】Linux基础开发工具

1 Linux 软件包管理器 yum 1.1软件包在Linux下安装软件, 一个通常的办法是下载到程序的源代码, 并进行编译, 得到可执行程序. 但是这样太麻烦了, 于是有些人把一些常用的软件提前编译好, 做成软件包(可以理解成windows上的安装程序)放在一个服务器上,通过包管理器可以很方便的…...

编程日记 2025/5/14 17:26:15

嵌入式C语言：结构体的多态性之结构体中的void*万能指针

目录一、void*指针在结构体中的应用二、实现方式 2.1. 定义通用结构体 2.2. 定义具体结构体 2.3. 初始化和使用三、应用场景 3.1. 内存管理函数 3.2. 泛型数据结构（链表） 3.3. 回调函数和函数指针 3.4. 跨语言调用或API接口（模拟…...

编程日记 2025/5/17 3:19:03

重构进行时：一秒告别 !=null 判空

重构进行时：一秒告别 !null 判空空指针异常（NullPointerException）是Java开发中常见的错误之一。许多开发者在遇到空指针问题时，往往会习惯性地使用! null来进行判断。然而，当代码中频繁出现这种判断时&#xff…...

编程日记 2025/5/18 22:06:22

React 中hooks之useSyncExternalStore使用总结

1. 基本概念 useSyncExternalStore 是 React 18 引入的一个 Hook，用于订阅外部数据源，确保在并发渲染下数据的一致性。它主要用于： 订阅浏览器 API（如 window.width）订阅第三方状态管理库订阅任何外部数据源 1.1 基…...

编程日记 2025/5/18 21:20:09

Semaphore 与线程池 Executor 有什么区别？

前言：笔者在看Semaphone时突然脑子宕机，啥啥分不清 Semaphore 和 Executor 的作用，故再次记录。一、什么是Semaphore？ Semaphore 是 Java 并发编程（JUC）中一个重要的同步工具，它的作用是控…...

编程日记 2025/5/17 17:40:55

Rust：高性能与安全并行的编程语言

引言在现代编程世界里，开发者面临的最大挑战之一就是如何平衡性能与安全性。在许多情况下，C/C这样的系统级编程语言虽然性能强大，但其内存管理的复杂性导致了各种安全漏洞。为了解决这些问题，Rust 作为一种新的系统级编程语言进入…...

编程日记 2025/5/18 5:36:05

论文笔记（六十三）Understanding Diffusion Models: A Unified Perspective（六）（完结）

Understanding Diffusion Models: A Unified Perspective（六）（完结） 文章概括指导（Guidance）分类器指导无分类器引导（Classifier-Free Guidance） 总结文章概括引用： …...

编程日记 2025/5/18 3:04:01

Redis --- 分布式锁的使用

我们在上篇博客高并发处理 --- 超卖问题一人一单解决方案讲述了两种锁解决业务的使用方法，但是这样不能让锁跨JVM也就是跨进程去使用，只能适用在单体项目中如下图： 为了解决这种场景，我们就需要用一个锁监视器对全部集群进行监视…...

编程日记 2025/5/16 21:27:58

电脑怎么格式化？格式化详细步骤

格式化是我们在日常使用电脑时可能会用到的一种操作，无论是清理磁盘空间、安装新系统，还是解决磁盘读写错误，都可能需要格式化。不过，对于一些不熟悉电脑操作的用户来说，格式化听起来可能有些复杂。其实，只…...

编程日记 2025/5/18 15:03:38

TikTok广告投放优化策略：提升ROI的核心技巧

在短许多品牌和商家纷纷投入广告营销，争夺这片潜力巨大的市场。然而，在激烈的竞争环境中，如何精准有效地投放广告，优化广告效果，实现更高的投资回报率（ROI）成为了广告主关注的核心。一. 精准受…...

编程日记 2025/5/18 3:03:10

视觉语言模型 (VLMs)：跨模态智能的探索

文章目录一. VLMs 的重要性与挑战：连接视觉与语言的桥梁 🌉二. VLMs 的核心训练范式：四种主流策略 🗺️1. 对比训练 (Contrastive Training)：拉近正例，推远负例 ⚖️2. 掩码方法 (Masking)：重构…...

编程日记 2025/5/14 19:32:24

第05章 08 绘制脑部体绘制图的阈值等值面

绘制脑部体绘制图的阈值等值面，例如肌肉和头骨骼，需要对医学图像数据进行阈值处理，并使用体绘制技术来可视化这些结构。以下是一个基于VTK/C的示例代码，展示如何读取DICOM图像数据，应用阈值过滤器来提取特定组织&#…...

编程日记 2025/5/14 3:28:23

类和对象(4)——多态：方法重写与动态绑定、向上转型和向下转型、多态的实现条件

目录 1. 向上转型和向下转型 1.1 向上转型 1.2 向下转型 1.3 instanceof关键字 2. 重写（overidde） 2.1 方法重写的规则 2.1.1 基础规则 2.1.2 深层规则 2.2 三种不能重写的方法 final修饰 private修饰 static修饰 3. 动态绑定 3.1 动态绑…...

编程日记 2025/5/17 20:48:31

动态规划＜九＞两个数组的dp

目录引例 LeetCode经典OJ题 1.第一题 2.第二题 3.第三题 4.第四题 5.第五题 6.第六题 7.第七题引例 OJ传送门LeetCode<1143>最长公共子序列画图分析： 使用动态规划解决 1.状态表示 ------经验题目要求经验为选取第一个字符串的[0,i]区间以及第二个字…...

编程日记 2025/5/17 3:18:42