mysql5.7强势支持原生json格式！！全面掌握

1.新建数据表

# 新建test1表

create table test_57.test1 (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

`info` JSON NULL,

primary key (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试5.7版本json格式';


2.插入json数据

#insert into test_57.test1 (info) values('{"name":"yuhaiwei","age":18,"hobby":"swim,reading,writting"}');

#insert into test_57.test1 (info) values('{"name":"tangwei","age":28,"hobby":"show,song,movie"}');


3.json 函数详解

#json_array 数组json串

#insert into test57.test1 (info) values (json_array('"testname', 99, 'basketball'));


#json_object 对象json串

#insert into test_57.test1 (info) values (json_object('name','"json_object_test_name"', 'age',99, 'hobby','basketball'));


#JSON_MERGE(json_doc, json_doc[, json_doc] ...) 合并json

select json_merge('[{"name":"yhw"},{"name":"test"}]', '{"name":"test1"}');


#json_type 判断json类型，是数组还是对象

select json_type(info) from test_57.test1;


#JSON_VALID(val) 判断是否合法json

select info,json_valid(info) from test_57.test1;


#json_quote  用双引号括起来，并对json中的一些特殊字符转义

insert into test_57.test1 (info) values((('{"name":"noquote_name","age":18,"hobby":"swim,reading,writting"}')));


#JSON_UNQUOTE(val) 和 json_quote相反

#JSON_EXTRACT(json_doc, path[, path] ...) 查询出json的属性值 $-整个json，$[0]-json数组第一个元素，$.name-json的name属性值

select info,json_extract(info, '$.name') from test_57.test1;


# ->符号 ，作用等同于 json_extract

select info->'$.name' from test_57.test1 limit 1;


# JSON_SET(json_doc, path, val[, path, val] ...) 替换json串中的值, 有新属性值会添加

select json_set(info, '$.name', 'yuhaiweiset', '$.age', 11, '$.key1', 'value1') from test_57.test1 limit 1;

# json_insert 添加值（不替换）,用法同json_set

# json_replace 替换值 （不添加）,用法同json_set


# json_remove 删除

select json_remove(info, '$.name', '$.age', '$.name') from test_57.test1 limit 1;


#JSON_CONTAINS(json_doc, val[, path]) json_doc的path值 是否包含val

SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '{"d": 4}', '$.c'); #结果 1


#JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) json_doc是否包含路径path,第二个参数为one 只要包含一个就返回true

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); #结果 1

SELECt JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e'); #结果 0


#json_keys(json,[,path]) json的path路径的所有属性名

SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'); #结果["c"]


#JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) 返回search_str在json_doc的path路径下的全路径

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]'); #结果 "$[2].x"


#JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...) 在最后添加子节点

SELECT JSON_ARRAY_APPEND('{"a": 1, "b": [2, 3], "c": 4}', '$.b[0]', 'y'); # 结果{"a": 1, "b": [[2, "y"], 3], "c": 4}


#JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...) 在前面添加兄弟节点

SELECT JSON_ARRAY_INSERT('{"a": 1, "b": [2, 3], "c": 4}', '$.b[0]', 'y'); # 结果{"a": 1, "b": ["y", 2, 3], "c": 4}


#JSON_DEPTH(json_doc) json深度

SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'), JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); #结果：1 1 1 2 2


#JSON_LENGTH(json_doc[, path]) json的长度，不包括嵌套的子元素的长度

SELECT JSON_LENGTH('[1, 2, {"a": 3}]'); #结果：3

SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') #结果 1