利用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)。以下是对这三者的比较以及它…...
浏览器访问 AWS ECS 上部署的 Docker 容器(监听 80 端口)
✅ 一、ECS 服务配置 Dockerfile 确保监听 80 端口 EXPOSE 80 CMD ["nginx", "-g", "daemon off;"]或 EXPOSE 80 CMD ["python3", "-m", "http.server", "80"]任务定义(Task Definition&…...

【大模型RAG】拍照搜题技术架构速览:三层管道、两级检索、兜底大模型
摘要 拍照搜题系统采用“三层管道(多模态 OCR → 语义检索 → 答案渲染)、两级检索(倒排 BM25 向量 HNSW)并以大语言模型兜底”的整体框架: 多模态 OCR 层 将题目图片经过超分、去噪、倾斜校正后,分别用…...
谷歌浏览器插件
项目中有时候会用到插件 sync-cookie-extension1.0.0:开发环境同步测试 cookie 至 localhost,便于本地请求服务携带 cookie 参考地址:https://juejin.cn/post/7139354571712757767 里面有源码下载下来,加在到扩展即可使用FeHelp…...
oracle与MySQL数据库之间数据同步的技术要点
Oracle与MySQL数据库之间的数据同步是一个涉及多个技术要点的复杂任务。由于Oracle和MySQL的架构差异,它们的数据同步要求既要保持数据的准确性和一致性,又要处理好性能问题。以下是一些主要的技术要点: 数据结构差异 数据类型差异ÿ…...

Cloudflare 从 Nginx 到 Pingora:性能、效率与安全的全面升级
在互联网的快速发展中,高性能、高效率和高安全性的网络服务成为了各大互联网基础设施提供商的核心追求。Cloudflare 作为全球领先的互联网安全和基础设施公司,近期做出了一个重大技术决策:弃用长期使用的 Nginx,转而采用其内部开发…...
爬虫基础学习day2
# 爬虫设计领域 工商:企查查、天眼查短视频:抖音、快手、西瓜 ---> 飞瓜电商:京东、淘宝、聚美优品、亚马逊 ---> 分析店铺经营决策标题、排名航空:抓取所有航空公司价格 ---> 去哪儿自媒体:采集自媒体数据进…...

在WSL2的Ubuntu镜像中安装Docker
Docker官网链接: https://docs.docker.com/engine/install/ubuntu/ 1、运行以下命令卸载所有冲突的软件包: for pkg in docker.io docker-doc docker-compose docker-compose-v2 podman-docker containerd runc; do sudo apt-get remove $pkg; done2、设置Docker…...
DeepSeek 技术赋能无人农场协同作业:用 AI 重构农田管理 “神经网”
目录 一、引言二、DeepSeek 技术大揭秘2.1 核心架构解析2.2 关键技术剖析 三、智能农业无人农场协同作业现状3.1 发展现状概述3.2 协同作业模式介绍 四、DeepSeek 的 “农场奇妙游”4.1 数据处理与分析4.2 作物生长监测与预测4.3 病虫害防治4.4 农机协同作业调度 五、实际案例大…...

20个超级好用的 CSS 动画库
分享 20 个最佳 CSS 动画库。 它们中的大多数将生成纯 CSS 代码,而不需要任何外部库。 1.Animate.css 一个开箱即用型的跨浏览器动画库,可供你在项目中使用。 2.Magic Animations CSS3 一组简单的动画,可以包含在你的网页或应用项目中。 3.An…...

C# 表达式和运算符(求值顺序)
求值顺序 表达式可以由许多嵌套的子表达式构成。子表达式的求值顺序可以使表达式的最终值发生 变化。 例如,已知表达式3*52,依照子表达式的求值顺序,有两种可能的结果,如图9-3所示。 如果乘法先执行,结果是17。如果5…...