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) |
引用: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的子类,在其基础上做了特化。
使用示意图更清晰的展示它的结构:
JSON文档本身是层次化的结构,因而MySQL对JSON存储也是层次化的。对于每一级对象,存储的最前面为存放当前对象的元素个数,以及整体占的大小。需要注意的是: