MySQL Json函数(5.7以上)

Wesley13
• 阅读 1252

oracle mysql 5.7.8 之后增加了对json数据格式的函数处理,可更加灵活的在数据库中操作json数据,如可变属性、自定义表单等等都使用使用该方式解决。

在创建表时,可以使用“GENERATED ALWAYS AS” 与json中的某个字段关联,并创建虚拟字段使json字符串也可以添加索引。

-- 创建测试json表

CREATE TABLE `test_json` (
  `$json` json NOT NULL,
  `userid` varchar(50) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`$json`,_utf8mb4'$."userid"'))) VIRTUAL,
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `$createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `$updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(60) GENERATED ALWAYS AS ((json_extract(`$json`,_utf8mb4'$."name"') = TRUE)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `by_userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

创建json

json_array(val1,val2,val3...)

创建json数组

json_object(key1,value1,key2,value2...)

创建json对象

json_quote

将json转成json字符串类型

插入json数据

-- 方式1 :直接插入json字符串
insert into test_json (id,`$json`) values(1,'{"userid":"1","name":"test name","sex":"男"}');

-- 方式2 :使用json_object
insert into test_json (id,`$json`) values(2,json_object("userid","2","name","test name2","sex","男"));

-- 方式3 :组合使用
insert into test_json (id,`$json`) values(3,json_object("userid","3","name","name3","sex","女","item",json_array("item1","item2","item3")));

查询json

json_contains(json_doc,val[,path])

判断是否包含某个json值

json_contains_path(json_doc,one_or_all,path[,path]...)

判断是否有某个路径

json_extract(json_doc,path[,path])

提取json值

column->path json_extract

简洁写法5.7.9开始支持

column->>path json_unquote(column -> path)

简洁写法5.7.13开始支持相当于

JSON_UNQUOTE(JSON_EXTRACT())

json_keys(json_doc[,path])

提取json中的键值结果为json数组

json_search(json_doc, one_or_all, search_str[,escape_char[,path]...])

按给定字符串关键字搜索json,返回匹配的路径

 搜索数组下的多个属性时可使用通配符“*”,如获取数组下对象的某属性$.item[*].name

-- 判断是否包含某个json值 

-- 方式1
select json_contains(`$json`,'{"name":"test name2"}') from test_json;

-- 方式2 (请注意第二个参数,带双引号,官网案例是number类型)
select json_contains(`$json`,'"name3"','$.name') from test_json;



-- 判断json是否指定路径,one至少存在一条路径,all存在所有路径
select json_contains_path($json,'one','$.item') from test_json;


-- 获取json值 
-- 方式1
select json_extract(`$json`,'$.item') from test_json;

-- 方式2 简洁写法
select `$json` -> '$.item' from test_json;

-- 方式3 简洁写法,并取消字符串,可用于select\where\having子句
select `$json` ->> '$.name' from test_json;


-- 获取json中的key数组
select json_keys($json) from test_json;


-- 获取json中指定value的json_path
select json_search($json,'one','item2') from test_json;

-- 可使用通配符
select json_search($json,'one','item%') from test_json;


select json_search($json,'all','2') from test_json;

修改json

json_append (废弃)

废弃,MySQL 5.7.9开始改名为json_array_append

json_array_append(json_doc,path,val[,path,val]...)

末尾添加数组元素,如果原有值是数值或json对 象,则转成数组后,再添加元素

json_array_insert(json_doc,path,val[,path,val]...)

插入数组元素

json_insert(json_doc,path,val[,path,val]...)

插入值(插入新值,但不替换已经存在的旧值)

json_merge(json_doc,json_doc[,json_doc]...)

合并json数组或对象

json_remove(json_doc,path[,path]...)

删除json数据

json_replace(json_doc,path,val[,path,val]...)

替换值(只替换已经存在的旧值)

json_set(json_doc,path,val[,path,val])

设置值(替换旧值,并插入不存在的新值)

json_unquote(val)

去除json字符串的引号,将值转成string类型

CAST('jsonString' as json)

可将json字符串转为json对象格式

-- 修改json

-- 只会给有item属性的json添加

select json_array_append(`$json`,'$.item','new item') from test_json ;


-- 会将对象转为数组

select json_array_append(`$json`,'$.name','new item') from test_json ;

-- 向数组指定位置插入,指定的json path必须是数组类型

select json_array_insert(`$json`,'$.item[10]','new item') from test_json ;


-- 添加新属性,如果没有新属性会增加
select json_insert(`$json`,'$.address','北京') from test_json ;

-- 修改原属性,如果没有属性会增加,如果有则不处理

select json_insert(`$json`,'$.name','新名字') from test_json ;

-- 也可向数组中插入
select json_insert(`$json`,'$.item[10]','new item') from test_json ;

-- 合并,根据属性进行合并,如有相同属性转为数组
select json_merge(`$json`,`$json`) from test_json ;

-- 添加新属性,合并数组
select json_merge(`$json`,'{"company":"companyName","address":"address","item":["newItem"]}') from test_json ;

-- 删除指定路径属性或数组值
select json_remove(`$json`,'$.item','$.sex') from test_json ;

select json_remove(`$json`,'$.item[0]','$.sex') from test_json ;

-- 替换属性值
select json_replace(`$json`,'$.sex','男') from test_json ;

-- 替换没有的属性不做任何操作
select json_replace(`$json`,'$.address','替换不存在的地址属性','$.item[20]','4444') from test_json ;

-- 有的属性做替换值,没有的做添加
select json_set(`$json`,'$.sex','男','$.address','替换不存在的地址属性','$.item[20]','4444') from test_json ;




-- 原始获取json会带引号

select `$json` -> '$.name' from test_json ;

-- 可去除双引号
select json_unquote(`$json` -> '$.name') from test_json ;

返回json属性

json_depth(json_doc)

返回json文档的最大深度

json_length(json_doc[,path])

返回json文档的长度

json_type(json_val)

返回json值得类型

json_valid()val

判断是否为合法json文档

-- json属性最大深度
select json_depth(`$json`) from test_json ;

-- json对象则是属性数,数组则是数组长度
select json_length(`$json`) from test_json ;

-- 判断数据类型

select json_type(`$json`) from test_json ;


select json_type(`$json` -> '$.name') from test_json ;


select json_type(`$json` -> '$.item') from test_json ;

json类型

ARRAY

JSON数组

BOOLEAN

JSON true和false字符串

NULL

JSON NULL字符串

数字类型

INTEGER

MySQL中 TINYINT, SMALLINT, MEDIUMINT, INT 和 BIGINT 

DOUBLE

MySQL中 DOUBLE FLOAT 

DECIMAL

DECIMAL 和 NUMERIC 

时间类型

DATETIME

MySQL中 DATETIME 和 TIMESTAMP 

DATE

MySQL中 DATE 

TIME

MySQL中 TIME 

字符串类型

STRING

MySQL字符串: CHAR, VARCHAR, TEXT, ENUM, 和 SET

二进制

BLOB

MySQL 二进制: BINARY, VARBINARY, BLOB

BIT

MySQL中 BIT

其他

OPAQUE

(raw bits)

JSON的存储结构及具体实现

引用:https://blog.csdn.net/qian\_xiaoqian/article/details/53128170

在处理JSON时,MySQL使用的utf8mb4字符集,utf8mb4是utf8和ascii的超集。由于历史原因,这里utf8并非是我们常说的UTF-8 Unicode变长编码方案,而是MySQL自身定义的utf8编码方案,最长为三个字节。具体区别非本文重点,请大家自行Google了解。

MySQL在内存中是以DOM的形式表示JSON文档,而且在MySQL解析某个具体的路径表达式时,只需要反序列化和解析路径上的对象,而且速度极快。要弄清楚MySQL是如何做到这些的,我们就需要了解JSON在硬盘上的存储结构。有个有趣的点是,JSON对象是BLOB的子类,在其基础上做了特化。

使用示意图更清晰的展示它的结构:

MySQL Json函数(5.7以上)

JSON文档本身是层次化的结构,因而MySQL对JSON存储也是层次化的。对于每一级对象,存储的最前面为存放当前对象的元素个数,以及整体占的大小。需要注意的是:

  • JSON对象的Key索引(图中橙色部分)都是排序好的,先按长度排序,长度相同的按照code point排序;Value索引(图中黄色部分)根据对应的Key的位置依次排列,最后面真实的数据存储(图中白色部分)也是如此

  • Key和Value的索引对存储了对象内的偏移和大小,单个索引的大小固定,可以通过简单的算术跳转到距离为N的索引

  • 通过MySQL5.7.16源代码可以看到,在序列化JSON文档时,MySQL会动态检测单个对象的大小,如果小于64KB使用两个字节的偏移量,否则使用四个字节的偏移量,以节省空间。同时,动态检查单个对象是否是大对象,会造成对大对象进行两次解析,源代码中也指出这是以后需要优化的点

  • 现在受索引中偏移量和存储大小四个字节大小的限制,单个JSON文档的大小不能超过4G;单个KEY的大小不能超过两个字节,即64K

  • 索引存储对象内的偏移是为了方便移动,如果某个键值被改动,只用修改受影响对象整体的偏移量

  • 索引的大小现在是冗余信息,因为通过相邻偏移可以简单的得到存储大小,主要是为了应对变长JSON对象值更新,如果长度变小,JSON文档整体都不用移动,只需要当前对象修改大小

  • 现在MySQL对于变长大小的值没有预留额外的空间,也就是说如果该值的长度变大,后面的存储都要受到影响

  • 结合JSON的路径表达式可以知道,JSON的搜索操作只用反序列化路径上涉及到的元素,速度非常快,实现了读操作的高性能

  • 不过,MySQL对于大型文档的变长键值的更新操作可能会变慢,可能并不适合写密集的需求

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
3年前
java将前端的json数组字符串转换为列表
记录下在前端通过ajax提交了一个json数组的字符串,在后端如何转换为列表。前端数据转化与请求varcontracts{id:'1',name:'yanggb合同1'},{id:'2',name:'yanggb合同2'},{id:'3',name:'yang
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这