【MySQL】JSON 格式字段处理
MySQL 5.7 版本后已支持 JSON 格式,这虽是 MySQL 的一小步,但可以说是程序开发的一大步,再也不用将 JSON 内容塞到 VARCHAR 类型字段了,程序设计也会变得更加灵活。网上大多只针对JSONObject 对象类型,本文也将详解 JSONArray 数组类型。
1 定义
JSON(JavaScript Object Notation)是一种轻量级的数据交换格式,它以键值对的方式存储数据,并以大括号和方括号进行标记,MySQL 的 JSON 格式字段可以存储 JSON 对象和数组。
JSONObject:对象
{"name": "yinyu","age": "18"
}
JSONArray:数组
["yinyu", "tom", "jack"]
而且对象和数组都可以嵌套其他对象或数组,从而形成复杂的数据结构,比如 👇
[{"name": "yinyu","age": [18, 28, 38]},"tom","jack"
]
对于对象来说,它的键只能为字符串,值类型支持 null,string,boolean,number,object,array 等。
建表 SQL
在创建表时,可以指定字段类型为 JSON ,不过无需指定 JSON 类型的长度,因为默认值只能为 null 。此外,JSON 字段类型可以根据实际存储的数据自动推断是对象还是数组结构,因此无需显式指定。
create table `test_json_tb` (`id` bigint(20) not null auto_increment,`json_obj` json default null comment 'json 对象字段',`json_arr` json default null comment 'json 数组字段',primary key (`id`)
) engine=innodb default charset=utf8mb4;
插入数据 SQL
insert into test_json_tb(json_obj, json_arr) values
('{"name":"yinyu", "age":18, "tags":["rap", "dance"]}', '["yinyu", "yinyu1", "yinyu2"]'),
('{"name":"tom", "age":19, "tags":["rap"]}', '["tom"]'),
('{"name":"jack", "age":20, "tags":["dance"]}', '[{"name":"jack"}, {"age":"20"}]');
建表如图 👇,接下来将使用该数据表来教学 MYSQL 的 JSON 常用函数。
2 原生查询
原生查询规则
针对 JSONObject 对象的规则:json对象字段名->’$.json属性名’
针对 JSONArray 数组的规则:json数组字段名->’$[数组索引]’ or json对象字段名->’$数组的键名[数组索引]’
示例:
selectjson_obj->'$.name' name,json_obj->'$.tags[0]' tags0,json_arr->'$[0]' arr0
from test_json_tb;
可以看到查询到的字段字符串类型有个双引号,那么可以通过将 -> 替换成 ->> 去除,转义符同时也会去除。
条件查询
那么我们使用原生查询规则来试下条件查询,比如 👇
select *
from test_json_tb
where json_obj->'$.name' = 'yinyu';
由于 JSON 字段的模糊搜索仅支持 %str% 格式,因此它的模糊搜索时索引无效:
select *
from test_json_tb
where json_obj->'$.name' like '%yin%';
对于联表查询、多条件查询均是支持的~
3 常用函数
JSONObject 对象类型和 JSONArray 数组类型基本上都可以使用以下函数,而网上攻略大多只针对JSONObject 对象类型,因此我补充了 JSONArray 数组类型的相关规则和示例。
① JSON_EXTRACT() 提取
规则和原生查询类型~
针对 JSONObject 对象类型,规则:json_extract(对象字段名, '$.属性名')
针对 JSONArray 数组类型,规则:json_extract(数组字段名, '$[数组索引]') or json_extract(对象字段名, '$.数组的键名[数组索引]')
select id,json_extract(json_obj,'$.name') as name,json_extract(json_obj,'$.tags[1]') as tags1,json_extract(json_arr,'$[0]') as arr0
from test_json_tb;
若想去除双引号,可以使用 JSON_UNQUOTE(JSON_EXTRACT())。
② JSON_SET() 更新
将数据插入到 JSON 格式字段中,如果是 JSONObject 对象类型,则有 key 则替换,无 key 则新增;如果是 JSONArray 数组类型,则根据索引进行替换或新增,规则以 JSONObject 对象类型为例。
规则:JSON_SET(json数据, '$.属性名', '更新/插入的值/数组', '$.数组的键名[数组索引]', '更新/插入的值'......)
示例:比如修改第2条数据,将他的 age 修改为 20,并且 tags 增加一个 “dance”:
update test_json_tb t1
set json_obj = json_set(t1.json_obj,'$.age',20, '$.tags[1]', 'dance')
where id=2;
更新后的记录 👇
③ JSON_INSERT() 插入
和 JSON_SET() 类似,但 JSON_INSERT() 只插入不更新(有 key 保持原样)。
规则:JSON_INSERT(json数据, '$.属性名', '更新/插入的值/数组', '$.数组的键名[数组索引]', '更新/插入的值'......)
示例:比如给第3条数据新增一个 age 属性和 from 属性,但是执行 sql 后会发现 age 属性插入是不生效的,这是因为 age 属性已存在,而 from 属性新增成功。
update test_json_tb t1
set json_obj = json_insert(t1.json_obj,'$.age', 21,'$.from', 'china')
where id=3;
④ JSON_REPLACE() 替换
JSON_REPLACE() 的作用就是只替换/更新,不插入,针对JSONObject 对象类型,则有 key 则替换,无 key 则保持原样;如果是 JSONArray 数组类型,则根据索引进行替换,规则以 JSONObject 对象类型为例。
规则:JSON_REPLACE(json数据, '$.属性名', '替换的值/数组', '$.数组的键名[数组索引]', '替换的值'......)
示例:接着上边第3条记录,给 age 属性的值替换成 21,tags 数组的第一个值替换成 “rap”
update test_json_tb t1
set json_obj = json_replace(t1.json_obj,'$.age', 21,'$.tags[0]', 'rap')
where id=3;
注意:如果该属性或数组索引不存在,那么是不会进行替换的,和 JSON_INSERT() 正好是反着来,而 JSON_SET() 集合了这两者。
⑤ JSON_REMOVE() 移除
顾名思义,该函数的作用是从删除 JSON 数据。
规则:JSON_REMOVE(json数据, '$.属性名', '$.数组的键名[数组索引]')
示例:之前不是给第3条记录增加了 from 属性么,我们来移除它,并且移除 json_arr 字段的第二个属性。
update test_json_tb t1
set json_obj = json_remove(t1.json_obj,'$.from'),json_arr = json_remove(t1.json_arr,'$[1]')
where id=3;
⑥ JSON_OBJECT()、JSON_ARRAY()
这两个函数分别对应 JSON 对象和 JSON 数组,分别用来创建 JSON 对象和 JSON 数组,可以搭配 JSON_SET()、JSON_INSERT()、JSON_REPLACE() 等函数,也可用于查询等操作。
规则:JSON_OBJECT(键名, 值, 键名, 值......)、JSON_ARRAY(元素、元素、元素......)
接下来以插表 SQL 作为示例:
insert into test_json_tb(json_obj, json_arr) values
(json_object('age',22,'name','mike','tags',json_array('sing')), json_array(11,22,33));
⑦ JSON_CONTAINS() 是否包含
校验 JSON 数据中是否包含特定值(可以是属性、对象、数组、数组索引等)。
规则:JSON_CONTAINS(target, candidate[, path]),详情看示例
示例1:查询出 json_obj 字段中包含 age = 18 的记录
select * from test_json_tb
where json_contains(json_obj, json_object('age',18))
示例2:查询出 json_arr 字段中包含 11 的记录
select * from test_json_tb
where json_contains(json_arr, json_array(11))
示例3:查询出 json_obj 字段中 tags 属性中同时包含 “rap” 和 “dance” 的记录
select * from test_json_tb
where json_contains(json_obj, json_array('rap','dance'), '$.tags')
⑧ JSON_TYPE()类型
查询某个 JSON 字段属性类型。
规则:JSON_TYPE(原生查询或 JSON_EXTRACT())
示例:查看 json_obj 字段中 tags 属性的类型
select json_obj->'$.tags' ,json_type(json_obj->'$.tags') as type
from test_json_tb
-- or
select json_extract(json_obj,'$.tags') ,json_type(json_extract(json_obj,'$.tags')) as type
from test_json_tb
⑨ JSON_KEYS() 键名
查询 JSON 数据中的所有键/key,返回列表,针对 JSON 对象,因为数组是没有键的。
规则:JSON_KEYS(json_value)
select json_keys(json_obj)
from test_json_tb
⑩ JSON_SEARCH() 深度查询
JSON_SEARCH() 函数,简单来说就是加强版查询,其实 JSON_EXTRACT() + limit 等 SQL也能达到效果。
规则:JSON_SEARCH(json数据,one/all,json 查询规则)
one 是返回1条记录,all 是返回符合条件的所有记录
示例1:查询路径--以 json 对象为例
select json_search(json_obj,'all','yinyu', null)
from test_json_tb
他会返回符合条件的记录里的路径,若是路径编写有困难,那么可以试下这个深度查询!
null -- 若搜索内容不存在,则返回 NULL,第三个参数为值。
示例2:条件查询--针对 json 对象(第五个参数为路径,条件查询时搭配 is not null)
查询 json_obj 字段中 name 属性为 “yinyu” 的记录
select *
from test_json_tb
where json_search(json_obj,'all','yinyu', null,'$.name') is not null
示例3:条件查询--针对 json 数组
查询 json_arr 字段中第一个元素为 “yinyu” 的记录
select *
from test_json_tb
where json_search(json_arr,'all','yinyu', null,'$[0]') is not null
示例4:模糊查询
select *
from test_json_tb
where json_search(json_obj,'all','%t%', null,'$.name') is not null
总结
本文完善了 JSON 数组的相关操作,大家如果有疑问都可以评论提出,有不足之处请大家批评指正,希望能多结识这方面的朋友,共同学习、共同进步。
相关文章:

【MySQL】JSON 格式字段处理
MySQL 5.7 版本后已支持 JSON 格式,这虽是 MySQL 的一小步,但可以说是程序开发的一大步,再也不用将 JSON 内容塞到 VARCHAR 类型字段了,程序设计也会变得更加灵活。网上大多只针对JSONObject 对象类型,本文也将详解 JS…...
数据库选型<1>
数据库选型 1.SQL与NoSQL1.SQL2.NoSQL 2.各种数据存储的适应场景1.MySQL 3.构建MySQL开发环境 1.SQL与NoSQL 1.SQL 关系型数据库 MySQLOracleSQL serverPostGreSQL 关系型数据库的特点 数据结构化存储在二维表中(新增JSON存储方式,也有nosql的特点)支持事务的原子…...

1.Flink源码编译
目录 1.环境版本 1.1 jdk 1.2.maven 1.3.node 1.4.scala 2.下载flink源码 3.编译源码 4.idea打开flink源码 5.运行wordcount 1.环境版本 软件地址 链接:https://pan.baidu.com/s/1ZxYydR8rBfpLCcIdaOzxVg 提取码:12xq 1.1 jdk 1.2 maven 1.…...

Linux内核数据结构 散列表
1、散列表数据结构 在Linux内核中,散列表(哈希表)使用非常广泛。本文将对其数据结构和核心函数进行分析。和散列表相关的数据结构有两个:hlist_head 和 hlist_node //hash桶的头结点 struct hlist_head {struct hlist_node *first…...

数据库系统课设——基于python+pyqt5+mysql的酒店管理系统(可直接运行)--GUI编程
几个月之前写的一个项目,通过这个项目,你能学到关于数据库的触发器知识,python的基本语法,python一些第三方库的使用,包括python如何将前后端连接起来(界面和数据),还有界面的设计等…...
《C和指针》笔记9: typedef
C语言支持一种叫作typedef的机制,它允许你为各种数据类型定义新名字。typedef声明的写法和普通的声明基本相同,只是把typedef这个关键字出现在声明的前面。例如,下面这个声明: char *ptr_to_char;把变量ptr_to_char声明为一个指向…...
《C和指针》笔记6:gets/puts/scanf/printf/getchar函数用法
本博客可以了解一些gets/puts/scanf/printf/getchar函数的基本用法。 文章目录 1. gets函数2. puts函数3. scanf函数4. printf函数5. getchar函数6. putchar函数 1. gets函数 gets函数从标准输入读取一行文本并把它存储于作为参数传递给它的数组中。一行输入由一串字符组成&a…...

智慧课堂学生行为检测评估算法
智慧课堂学生行为检测评估算法通过yolov5系列图像识别和行为分析,智慧课堂学生行为检测评估算法评估学生的表情、是否交头接耳行为、课堂参与度以及互动质量,并提供相应的反馈和建议。智慧课堂学生行为检测评估算法能够实时监测学生的上课行为࿰…...

rainbond云原生应用管理平台部署
rainbond简介 rainbond 是 一个 开源的Kubernetes 云原生应用管理平台。 Rainbond 核心100%开源,Serverless体验,不需要懂K8s也能轻松管理容器化应用,平滑无缝过渡到K8s,是国内首个支持国产化信创、适合私有部署的一体化应用管理…...

jemter连接数据json断言
文章目录 一、jmeter连接数据库1、加载JDBC驱动2、连接数据3、SQL Query的Query Type使用方法:4、Variable Name使用方法:5、Result variable name使用方法: 二、Json响应断言1、添加 》 断言 》 JSON断言2、JSON断言界面参数说明:…...

JavaFX 加载 fxml 文件
JavaFX 加载 fxml 文件主要有两种方式,第一种方式通过 FXMLLoader 类直接加载 fxml 文件,简单直接,但是有些控件目前还不知道该如何获取,所以只能显示,目前无法处理。第二种方式较为复杂,但是可以使用与 fx…...
(三)Redis——Set
SADD key value SMEMBERS 127.0.0.1:6379> SADD set aaa 1 127.0.0.1:6379> SMEMBERS set aaa 127.0.0.1:6379> SADD set aaa 0 127.0.0.1:6379> SMEMBERS set aaaSISMEMBER 判断 aaa 是否在 set 中 127.0.0.1:6379> SISMEMBER set aaa 1 127.0.0.1:6379>…...
Vue组件通信方式详解(全面版)
在Vue应用开发中,组件通信是一个重要的话题。不同的组件可能需要在不同的情况下进行数据传递和交互。Vue提供了多种方式来实现组件通信,每种方式都有其适用的场景。本文将详细介绍Vue中实现组件通信的各种方式,并为每种方式提供通俗易懂的代码…...

什么是Promise对象?它的状态有哪些?如何使用Promise处理异步操作?以及 async、await
聚沙成塔每天进步一点点 ⭐ 专栏简介⭐ Promise对象⭐ 创建Promise对象⭐ 使用Promise处理异步操作⭐ async、await⭐ 写在最后 ⭐ 专栏简介 前端入门之旅:探索Web开发的奇妙世界 记得点击上方或者右侧链接订阅本专栏哦 几何带你启航前端之旅 欢迎来到前端入门之旅…...
Android 之自定义绘制一
绘制的基本要素 onDraw(Canvas) 绘制方法 Canvas 绘制工具 Paint 调整风格 粗细等 坐标系: x y ,3D 会有z轴,x 左到右,y 上至下,与数学中y颠倒 尺寸单位: 布局中 dp ,sp ,代码中 px;dp 为了适配不同的尺寸 绘制的关键: draw(Canvas )......(关键类:Paint) Paint.ANTI_A…...

vue3 计算两个表单得到第三个表单数据
<el-formref"ruleFormRef"label-width"150px"label-suffix":":rules"rules":disabled"drawerProps.isView":model"drawerProps.rowData"><el-form-item label"云平台名称" prop"cloudId&…...

Premiere Pro软件安装包分享(附安装教程)
目录 一、软件简介 二、软件下载 一、软件简介 Adobe Premiere Pro,简称PR,是Adobe公司开发的一款非线性视频编辑软件,被广泛应用于电影、电视剧、广告、纪录片、独立电影和音乐会等影视制作领域。它被公认为是行业内的标准工具,…...

springboot设置文件上传大小,默认是1mb
问题排查和解决过程 之前做了个项目,需要用到文件上传,启动项目正常,正常上传图片也正常,但这里图片刚好都小于1M,在代码配置文件里面也写了配置,限制大小为500M,想着就没问题(测试…...

Unity 之transform.LookAt() 调整一个物体的旋转,使其朝向指定的位置
文章目录 总的介绍补充(用于摄像机跟随的场景) 总的介绍 transform.LookAt 是 Unity 引擎中 Transform 组件的一个方法,用于调整一个物体的旋转,使其朝向指定的位置。通常情况下,它被用来使一个物体(如摄像…...

linux————haproxy
一、概述 HAProxy是一个免费的负载均衡软件,可以运行于大部分主流的Linux操作系统上(CentOS、Ubuntu、Debian、OpenSUSE、Fedora、麒麟、欧拉、UOS)。 HAProxy提供了L4(TCP)和L7(HTTP)两种负载均衡能力,具备丰富的功能。HAProxy具…...

调用支付宝接口响应40004 SYSTEM_ERROR问题排查
在对接支付宝API的时候,遇到了一些问题,记录一下排查过程。 Body:{"datadigital_fincloud_generalsaas_face_certify_initialize_response":{"msg":"Business Failed","code":"40004","sub_msg…...
java_网络服务相关_gateway_nacos_feign区别联系
1. spring-cloud-starter-gateway 作用:作为微服务架构的网关,统一入口,处理所有外部请求。 核心能力: 路由转发(基于路径、服务名等)过滤器(鉴权、限流、日志、Header 处理)支持负…...
Java如何权衡是使用无序的数组还是有序的数组
在 Java 中,选择有序数组还是无序数组取决于具体场景的性能需求与操作特点。以下是关键权衡因素及决策指南: ⚖️ 核心权衡维度 维度有序数组无序数组查询性能二分查找 O(log n) ✅线性扫描 O(n) ❌插入/删除需移位维护顺序 O(n) ❌直接操作尾部 O(1) ✅内存开销与无序数组相…...
MVC 数据库
MVC 数据库 引言 在软件开发领域,Model-View-Controller(MVC)是一种流行的软件架构模式,它将应用程序分为三个核心组件:模型(Model)、视图(View)和控制器(Controller)。这种模式有助于提高代码的可维护性和可扩展性。本文将深入探讨MVC架构与数据库之间的关系,以…...

linux arm系统烧录
1、打开瑞芯微程序 2、按住linux arm 的 recover按键 插入电源 3、当瑞芯微检测到有设备 4、松开recover按键 5、选择升级固件 6、点击固件选择本地刷机的linux arm 镜像 7、点击升级 (忘了有没有这步了 估计有) 刷机程序 和 镜像 就不提供了。要刷的时…...

高等数学(下)题型笔记(八)空间解析几何与向量代数
目录 0 前言 1 向量的点乘 1.1 基本公式 1.2 例题 2 向量的叉乘 2.1 基础知识 2.2 例题 3 空间平面方程 3.1 基础知识 3.2 例题 4 空间直线方程 4.1 基础知识 4.2 例题 5 旋转曲面及其方程 5.1 基础知识 5.2 例题 6 空间曲面的法线与切平面 6.1 基础知识 6.2…...
sqlserver 根据指定字符 解析拼接字符串
DECLARE LotNo NVARCHAR(50)A,B,C DECLARE xml XML ( SELECT <x> REPLACE(LotNo, ,, </x><x>) </x> ) DECLARE ErrorCode NVARCHAR(50) -- 提取 XML 中的值 SELECT value x.value(., VARCHAR(MAX))…...
Rust 异步编程
Rust 异步编程 引言 Rust 是一种系统编程语言,以其高性能、安全性以及零成本抽象而著称。在多核处理器成为主流的今天,异步编程成为了一种提高应用性能、优化资源利用的有效手段。本文将深入探讨 Rust 异步编程的核心概念、常用库以及最佳实践。 异步编程基础 什么是异步…...
大模型多显卡多服务器并行计算方法与实践指南
一、分布式训练概述 大规模语言模型的训练通常需要分布式计算技术,以解决单机资源不足的问题。分布式训练主要分为两种模式: 数据并行:将数据分片到不同设备,每个设备拥有完整的模型副本 模型并行:将模型分割到不同设备,每个设备处理部分模型计算 现代大模型训练通常结合…...
【JavaSE】绘图与事件入门学习笔记
-Java绘图坐标体系 坐标体系-介绍 坐标原点位于左上角,以像素为单位。 在Java坐标系中,第一个是x坐标,表示当前位置为水平方向,距离坐标原点x个像素;第二个是y坐标,表示当前位置为垂直方向,距离坐标原点y个像素。 坐标体系-像素 …...