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

【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系列图像识别和行为分析,智慧课堂学生行为检测评估算法评估学生的表情、是否交头接耳行为、课堂参与度以及互动质量,并提供相应的反馈和建议。智慧课堂学生行为检测评估算法能够实时监测学生的上课行为&#xff0…...

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断言界面参数说明&#xff1a…...

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&#xff0c;简称PR&#xff0c;是Adobe公司开发的一款非线性视频编辑软件&#xff0c;被广泛应用于电影、电视剧、广告、纪录片、独立电影和音乐会等影视制作领域。它被公认为是行业内的标准工具&#xff0c…...

springboot设置文件上传大小,默认是1mb

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

Unity 之transform.LookAt() 调整一个物体的旋转,使其朝向指定的位置

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

linux————haproxy

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

XML Group端口详解

在XML数据映射过程中&#xff0c;经常需要对数据进行分组聚合操作。例如&#xff0c;当处理包含多个物料明细的XML文件时&#xff0c;可能需要将相同物料号的明细归为一组&#xff0c;或对相同物料号的数量进行求和计算。传统实现方式通常需要编写脚本代码&#xff0c;增加了开…...

深入剖析AI大模型:大模型时代的 Prompt 工程全解析

今天聊的内容&#xff0c;我认为是AI开发里面非常重要的内容。它在AI开发里无处不在&#xff0c;当你对 AI 助手说 "用李白的风格写一首关于人工智能的诗"&#xff0c;或者让翻译模型 "将这段合同翻译成商务日语" 时&#xff0c;输入的这句话就是 Prompt。…...

IGP(Interior Gateway Protocol,内部网关协议)

IGP&#xff08;Interior Gateway Protocol&#xff0c;内部网关协议&#xff09; 是一种用于在一个自治系统&#xff08;AS&#xff09;内部传递路由信息的路由协议&#xff0c;主要用于在一个组织或机构的内部网络中决定数据包的最佳路径。与用于自治系统之间通信的 EGP&…...

Nuxt.js 中的路由配置详解

Nuxt.js 通过其内置的路由系统简化了应用的路由配置&#xff0c;使得开发者可以轻松地管理页面导航和 URL 结构。路由配置主要涉及页面组件的组织、动态路由的设置以及路由元信息的配置。 自动路由生成 Nuxt.js 会根据 pages 目录下的文件结构自动生成路由配置。每个文件都会对…...

MODBUS TCP转CANopen 技术赋能高效协同作业

在现代工业自动化领域&#xff0c;MODBUS TCP和CANopen两种通讯协议因其稳定性和高效性被广泛应用于各种设备和系统中。而随着科技的不断进步&#xff0c;这两种通讯协议也正在被逐步融合&#xff0c;形成了一种新型的通讯方式——开疆智能MODBUS TCP转CANopen网关KJ-TCPC-CANP…...

Python爬虫(一):爬虫伪装

一、网站防爬机制概述 在当今互联网环境中&#xff0c;具有一定规模或盈利性质的网站几乎都实施了各种防爬措施。这些措施主要分为两大类&#xff1a; 身份验证机制&#xff1a;直接将未经授权的爬虫阻挡在外反爬技术体系&#xff1a;通过各种技术手段增加爬虫获取数据的难度…...

pikachu靶场通关笔记22-1 SQL注入05-1-insert注入(报错法)

目录 一、SQL注入 二、insert注入 三、报错型注入 四、updatexml函数 五、源码审计 六、insert渗透实战 1、渗透准备 2、获取数据库名database 3、获取表名table 4、获取列名column 5、获取字段 本系列为通过《pikachu靶场通关笔记》的SQL注入关卡(共10关&#xff0…...

【网络安全】开源系统getshell漏洞挖掘

审计过程&#xff1a; 在入口文件admin/index.php中&#xff1a; 用户可以通过m,c,a等参数控制加载的文件和方法&#xff0c;在app/system/entrance.php中存在重点代码&#xff1a; 当M_TYPE system并且M_MODULE include时&#xff0c;会设置常量PATH_OWN_FILE为PATH_APP.M_T…...

相关类相关的可视化图像总结

目录 一、散点图 二、气泡图 三、相关图 四、热力图 五、二维密度图 六、多模态二维密度图 七、雷达图 八、桑基图 九、总结 一、散点图 特点 通过点的位置展示两个连续变量之间的关系&#xff0c;可直观判断线性相关、非线性相关或无相关关系&#xff0c;点的分布密…...

Heygem50系显卡合成的视频声音杂音模糊解决方案

如果你在使用50系显卡有杂音的情况&#xff0c;可能还是官方适配问题&#xff0c;可以使用以下方案进行解决&#xff1a; 方案一&#xff1a;剪映替换音色&#xff08;简单适合普通玩家&#xff09; 使用剪映换音色即可&#xff0c;口型还是对上的&#xff0c;没有剪映vip的&…...