oracle mysql 5.7之后增加了对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值
json_contains_path
判断某个路径下是否包json值
json_extract
提取json值
column->path json_extract
简洁写法,MySQL 5.7.9开始支持
column->>path json_unquote(column -> path)
简洁写法
json_keys
提取json中的键值为json数组
json_search
按给定字符串关键字搜索json,返回匹配的路径
修改json
json_append
废弃,MySQL 5.7.9开始改名为json_array_append
json_array_append
末尾添加数组元素,如果原有值是数值或json对 象,则转成数组后,再添加元素
json_array_insert
插入数组元素
json_insert
插入值(插入新值,但不替换已经存在的旧值)
json_merge
合并json数组或对象
json_remove
删除json数据
json_replace
替换值(只替换已经存在的旧值)
json_set
设置值(替换旧值,并插入不存在的新值)
json_unquote
去除json字符串的引号,将值转成string类型
返回json属性
json_depth
返回json文档的最大深度
json_length
返回json文档的长度
json_type
返回json值得类型
json_valid
判断是否为合法json文档