利用JSON数据类型优化关系型数据库设计
利用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数据…...
Nxopen 直齿轮参数化设计
NXUG1953 Visualstudio 2019 参考论文: A Method for Determining the AGMA Tooth Form Factor from Equations for the Generated Tooth Root Fillet //FullGear// Mandatory UF Includes #include <uf.h> #include <uf_object_types.h>// Internal I…...
线程配置经验
工作时,时常会遇到,线程相关的问题与解法,本人会持续对开发过程中遇到的关于线程相关的问题及解决记录更新记录在此篇博客中。 目录 一、线程基本知识 1. 线程和进程 二、问题与解法 1. 避免乘法级别数量线程并行 1)使用线程池…...
火语言RPA--KimiAiFree服务
🚩【组件功能】:KimiAiFree服务支持联网搜索、支持智能体对话、支持长文档解读、支持图像OCR。 配置预览 配置说明 服务地址 支持T或# 自行搭建或第三方提供的KimiAiFree服务地址。 RefreshToken 支持T或# 与KimiAiFree服务交互时用到的token。从ki…...
P6120 [USACO17JAN] Hoof, Paper, Scissor S
难度:普及/提高−; 题意: 石头、剪刀、布游戏,先给出 n n n 轮已经知道的其中一人的对局情况,例如样例: 5 P - 布 P - 布 H - 石头 P - 布 S - 剪刀另外一人,只允许修改一次机会的情况下…...
Android Studio打包APK
1.导出APK安装包 如果是首次打包,Create new 单击蓝色对话框右边文件夹📂图标 ,选择密钥保存路径,然后在下方File name对话框中填写您想要名称,再点击OK回到密钥创建对话框。 在此对话框中填写密码(Passwo…...
08 比特币通用技术介绍
比特币分层 比特币区块结构 存储结构 区块是比特币存储交易的结构,一个区块总是指向其父节点。 一个区块包含三个字段:区块头、区块交易数量、交易列表。交易数量受到区块大小限制,输入、输出数量和脚本都会占用区块空间,矿工往…...
拟合损失函数
文章目录 拟合损失函数一、线性拟合1.1 介绍1.2 代码可视化1.2.1 生成示例数据1.2.2 损失函数1.2.3 绘制三维图像1.2.4 绘制等高线1.2.5 损失函数关于斜率的函数 二、 多变量拟合2.1 介绍2.2 代码可视化2.2.1 生成示例数据2.2.2 损失函数2.2.3 绘制等高线 三、 多项式拟合3.1 介…...
二进制安卓清单 binary AndroidManifest - XCTF apk 逆向-2
XCTF 的 apk 逆向-2 题目 wp,这是一道反编译对抗题。 题目背景 AndroidManifest.xml 在开发时是文本 xml,在编译时会被 aapt 编译打包成为 binary xml。具体的格式可以参考稀土掘金 MindMac 做的类图(2014),下面的博…...
在线免费快速无痕去除照片海报中的文字logo
上期和大家分享了用photoshop快速无痕去除照片海报中的文字logo的方法,有的同学觉得安装PS太麻烦,有那下载安装时间早都日落西山了,问有没有合适的在线方法可以快速去除;达芬奇上网也尝试了几个网站,今天分享一个对国人…...
引领未来科技潮流:Web3 前沿发展趋势
随着技术不断发展,我们正站在一个全新的互联网时代的门槛上,Web3的出现正在重新定义互联网的构架和运作方式。Web3,作为互联网的下一代发展趋势,其核心思想是去中心化、开放与用户主权。与现有的Web2.0相比,Web3更加注…...
【番外篇】鸿蒙扫雷天纪:运混沌灵智勘破雷劫天局
大家好啊,我是小象٩(๑ω๑)۶ 我的博客:Xiao Xiangζั͡ޓއއ 很高兴见到大家,希望能够和大家一起交流学习,共同进步。 这一节课我们不学习新的知识,我们来做一个扫雷小游戏 目录 扫雷小游戏概述一、扫雷游戏分析…...
08.OSPF 特殊区域及其他特性
OSPF 特殊区域及其他特性 一. 前言OSPF的四个特殊区域Stub末梢区域Totally Stub完全末梢区域NSSATotally NSSA完全的NSSA二.Stub 区域和 Totally Stub 区域(1)网络规模变大引发的问题(2)传输区域和末端区域(3)Stub 区域(4)Totally Stub 区域三.NSSA 区域和 Totally NSS…...
人工智能在医疗领域的应用有哪些?
人工智能在医疗领域的应用十分广泛,涵盖了诊断、治疗、药物研发等多个环节,以下是一些主要的应用: 医疗影像诊断 疾病识别:通过分析 X 光、CT、MRI 等影像,人工智能算法能够识别出肿瘤、结节、骨折等病变,…...
c#使用Confluent.Kafka实现生产者发送消息至kafka(远程连接kafka发送消息超时的解决 Local:Message timed out)
水一篇: 参考:c#使用Confluent.Kafka实现生产者发送消息至kafka(远程连接kafka发送消息超时的解决 Local:Message timed out) - 寒冰之光 - 博客园 该死的Kafka,远程连接Kafka超时以及解决办法 - 博客王大…...
【2025年数学建模美赛F题】(顶刊论文绘图)模型代码+论文
全球网络犯罪与网络安全政策的多维度分析及效能评估 摘要1 Introduction1.1 Problem Background1.2Restatement of the Problem1.3 Literature Review1.4 Our Work 2 Assumptions and Justifications数据完整性与可靠性假设:法律政策独立性假设:人口统计…...
DeepSeek 的背景介绍
在全球人工智能大模型蓬勃发展的浪潮中,DeepSeek 宛如一颗耀眼的新星,迅速崛起并吸引了众多关注的目光。它的出现不仅为人工智能领域注入了新的活力,也在一定程度上改变了行业的竞争格局。 一、创立背景与资金支持 DeepSeek,中文…...
Meta 计划 2025 年投资 650 亿美元推动 AI 发展
每周跟踪AI热点新闻动向和震撼发展 想要探索生成式人工智能的前沿进展吗?订阅我们的简报,深入解析最新的技术突破、实际应用案例和未来的趋势。与全球数同行一同,从行业内部的深度分析和实用指南中受益。不要错过这个机会,成为AI领…...
信息学奥赛一本通 2110:【例5.1】素数环
【题目链接】 ybt 2110:【例5.1】素数环 【题目考点】 1. 深搜回溯 2. 质数 【解题思路】 1~n的数字构成一个环,要求相邻数字加和必须是质数。 该题最终输出的是一个序列,只不过逻辑上序列最后一个数字的下一个数字就是序列的第一个数字…...
Redis、MongoDB 和 MySQL评估
Redis、MongoDB 和 MySQL 是三种不同类型的数据库系统,各自有独特的特点和适用场景。MySQL 是一个关系型数据库管理系统(RDBMS),而 Redis 和 MongoDB 是非关系型数据库(NoSQL)。以下是对这三者的比较以及它…...
Oracle查询表空间大小
1 查询数据库中所有的表空间以及表空间所占空间的大小 SELECTtablespace_name,sum( bytes ) / 1024 / 1024 FROMdba_data_files GROUP BYtablespace_name; 2 Oracle查询表空间大小及每个表所占空间的大小 SELECTtablespace_name,file_id,file_name,round( bytes / ( 1024 …...
【OSG学习笔记】Day 16: 骨骼动画与蒙皮(osgAnimation)
骨骼动画基础 骨骼动画是 3D 计算机图形中常用的技术,它通过以下两个主要组件实现角色动画。 骨骼系统 (Skeleton):由层级结构的骨头组成,类似于人体骨骼蒙皮 (Mesh Skinning):将模型网格顶点绑定到骨骼上,使骨骼移动…...
莫兰迪高级灰总结计划简约商务通用PPT模版
莫兰迪高级灰总结计划简约商务通用PPT模版,莫兰迪调色板清新简约工作汇报PPT模版,莫兰迪时尚风极简设计PPT模版,大学生毕业论文答辩PPT模版,莫兰迪配色总结计划简约商务通用PPT模版,莫兰迪商务汇报PPT模版,…...
Redis:现代应用开发的高效内存数据存储利器
一、Redis的起源与发展 Redis最初由意大利程序员Salvatore Sanfilippo在2009年开发,其初衷是为了满足他自己的一个项目需求,即需要一个高性能的键值存储系统来解决传统数据库在高并发场景下的性能瓶颈。随着项目的开源,Redis凭借其简单易用、…...
五子棋测试用例
一.项目背景 1.1 项目简介 传统棋类文化的推广 五子棋是一种古老的棋类游戏,有着深厚的文化底蕴。通过将五子棋制作成网页游戏,可以让更多的人了解和接触到这一传统棋类文化。无论是国内还是国外的玩家,都可以通过网页五子棋感受到东方棋类…...
算术操作符与类型转换:从基础到精通
目录 前言:从基础到实践——探索运算符与类型转换的奥秘 算术操作符超级详解 算术操作符:、-、*、/、% 赋值操作符:和复合赋值 单⽬操作符:、--、、- 前言:从基础到实践——探索运算符与类型转换的奥秘 在先前的文…...
raid存储技术
1. 存储技术概念 数据存储架构是对数据存储方式、存储设备及相关组件的组织和规划,涵盖存储系统的布局、数据存储策略等,它明确数据如何存储、管理与访问,为数据的安全、高效使用提供支撑。 由计算机中一组存储设备、控制部件和管理信息调度的…...
python读取SQLite表个并生成pdf文件
代码用于创建含50列的SQLite数据库并插入500行随机浮点数据,随后读取数据,通过ReportLab生成横向PDF表格,包含格式化(两位小数)及表头、网格线等美观样式。 # 导入所需库 import sqlite3 # 用于操作…...
dvwa11——XSS(Reflected)
LOW 分析源码:无过滤 和上一关一样,这一关在输入框内输入,成功回显 <script>alert(relee);</script> MEDIUM 分析源码,是把<script>替换成了空格,但没有禁用大写 改大写即可,注意函数…...
华硕电脑,全新的超频方式,无需进入BIOS
想要追求更佳性能释放 或探索更多可玩性的小伙伴, 可能会需要为你的电脑超频。 但我们常用的不论是BIOS里的超频, 还是Armoury Crate奥创智控中心超频, 每次调节都要重启,有点麻烦。 TurboV Core 全新的超频方案来了 4不规…...
