5.7.8开始支持 json类型.
create table t(id int,js json,PRIMARY KEY (`id`))
插入数据
insert into t values(1,'{"a":1,"s":"abc"}') insert into t values(2,'[1,2,{"a":123}]') insert into t values(3,'"str"') insert into t values(4,'123')直接提供字符串即可。还可以用JSON_Array和JSON_Object函数来构造
insert into t values(5,JSON_Object('key1',v1,'key2',v2)) insert into t values(4,JSON_Array(v1,v2,v3))JSON_OBJECT([key, val[, key, val] ...]) JSON_ARRAY([val[, val] ...])
JSON_SET(json_doc, path, val[, path, val] ...)修改数据update t set js=json_set('{"a":1,"s":"abc"}','$.a',456,'$.b','bbb') where id=1
结果js={"a":456,"s":"abc","b":"bbb"}
path中$就代表整个doc,然后可以用的方式指定对象属性或者数组下标等.
执行效果,类似json的语法 $.a=456 $.b="bbb"存在就修改,不存在就设置.
$.c.c=123
这个在javascript中会出错,因为.c为null。 但是在json_set('{}','$.c.c',123)中,不存在的路径将直接被忽略。特殊的对于数组,如果目标doc不是数组则会被转换成[doc],然后再执行set,
如果set的下标超过数组长度,只会添加到数组结尾。select json_set('{"a":456}','$[1]',123)
结果[{"a":456},123]。目标现被转换成[{"a":456}],然后应用$[1]=123。select json_set('"abc"','$[999]',123)
结果["abc",123]。再举几个例子
select json_set('[1,2,3]','$[0]',456,'$[3]','bbb') 结果[456,2,3,'bbb']注意:
对于javascript中 var a=[1,2,3] a.a='abc' 是合法的,但是一旦a转成json字符串,a.a就丢失了。而在mysql中,这种算作路径不存在,因此
select json_set('[1,2,3]','$.a',456) 结果还是[1,2,3]
然后还有另外两个版本
JSON_INSERT(json_doc, path, val[, path, val] ...) 如果不存在对应属性则插入,否则不做任何变动JSON_REPLACE(json_doc, path, val[, path, val] ...)
如果存在则替换,否则不做任何变动这两个操作倒是没有javascript直接对应的操作
select json_insert('{"a":1,"s":"abc"}','$.a',456,'$.b','bbb') 结果{"a":1,"s":"abc","b":"bbb"}select json_replace('{"a":1,"s":"abc"}','$.a',456,'$.b','bbb')
结果{"a":456,"s":"abc"}
加上删除节点
JSON_REMOVE(json_doc, path[, path] ...) 如果存在则删除对应属性,否则不做任何变动 select json_replace('{"a":1,"s":"abc"}','$.a','$.b') 结果{"s":"abc"}涉及数组时,三个函数与json_set基本一样
select json_insert('{"a":1}','$[0]',456) 结果不变,认为0元素已经存在了,注意这里结果不是[{"a":1}]select json_insert('{"a":1}','$[999]',456)
结果追加到数组结尾[{"a":1},456] select json_replace('{"a":1}','$[0]',456) 结果456!而非[456]select json_replace('{"a":1}','$[1]',456)
结果不变。其实对于json_insert和json_replace来说一般情况没必要针对数组使用。
select json_remove('{"a":1}','$[0]') 结果不变!select json_remove('[{"a":1}]','$[0]')
结果[]总之涉及数组的时候要小心。
JSON_MERGE(json_doc, json_doc[, json_doc] ...)将多个doc合并select json_merge('[1,2,3]','[4,5]')
结果[1,2,3,4,5]。数组简单扩展select json_merge('{"a":1}','{"b":2}')
结果{"a":1,"b":2}。两个对象直接融合。特殊的还是在数组
select json_merge('123','45') 结果[123,45]。两个常量变成数组select json_merge('{"a":1}','[1,2]')
结果[{"a":1},1,2]。目标碰到数组,先转换成[doc]select json_merge('[1,2]','{"a":1}')
结果[1,2,{"a":1}]。非数组都追加到数组后面。
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
给指定的节点,添加元素,如果节点不是数组,则先转换成[doc]select json_Array_append('[1,2]','$','456')
结果[1,2,456]select json_Array_append('[1,2]','$[0]','456')
结果[[1,456],2]。指定插在$[0]这个节点,这个节点非数组,所以等效为 select json_Array_append('[[1],2]','$[0]','456') JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...) 在数组的指定下标处插入元素SELECT JSON_ARRAY_INSERT('[1,2,3]','$[1]',4)
结果[1,4,2,3]。在$数组的下标1处插入SELECT JSON_ARRAY_INSERT('[1,[1,2,3],3]','$[1][1]',4)
结果[1,[1,4,2,3],3]。在$[1]数组的下标1处插入SELECT JSON_ARRAY_INSERT('[1,2,3]','$[0]',4,'$[1]',5)
结果[4,5,1,2,3]。注意后续插入是在前面插入基础上的,而非[4,1,5,2,3]
提取json信息的函数
JSON_KEYS(json_doc[, path]) 返回指定path的keyselect json_keys('{"a":1,"b":2}')
结果["a","b"]select json_keys('{"a":1,"b":[1,2,3]}','$.b')
结果null。数组没有key
JSON_CONTAINS(json_doc, val[, path])
是否包含子文档select json_contains('{"a":1,"b":4}','{"a":1}')
结果1select json_contains('{"a":2,"b":1}','{"a":1}')
结果0select json_contains('{"a":[1,2,3],"b":1}','[1,2]','$.a')
结果1。数组包含则需要所有元素都存在。select json_contains('{"a":[1,2,3],"b":1}','1','$.a')
结果1。元素存在数组元素中。 JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) 检查路径是否存在select JSON_CONTAINS_PATH('{"a":1,"b":1}', 'one','$.a','$.c')
结果1。只要存在一个select JSON_CONTAINS_PATH('{"a":1,"b":1}', 'all','$.a','$.c')
结果0。必须全部存在。 select JSON_CONTAINS_PATH('{"a":1,"b":{"c":{"d":1}}}', 'one','$.b.c.d') 结果1。select JSON_CONTAINS_PATH('{"a":1,"b":{"c":{"d":1}}}', 'one','$.a.c.d')
结果0。 JSON_EXTRACT(json_doc, path[, path] ...) 获得doc中某个或多个节点的值。select json_extract('{"a":1,"b":2}','$.a')
结果1select json_extract('{"a":[1,2,3],"b":2}','$.a[1]')
结果2 select json_extract('{"a":{"a":1,"b":2,"c":3},"b":2}','$.a.*') 结果[1,2,3]。a.*通配a所有属性的值返回成数组。 select json_extract('{"a":{"a":1,"b":2,"c":3},"b":4}','$**.b') 结果[2,4]。通配$中所有层次下的属性b的值返回成数组。mysql5.7.9开始增加了一种简写方式:column->path
select id,js->'$.id' from t where js->'$.a'=1 order by js->'$.b'
等价于 select id,json_extract(js,'$.id') from t where json_extract(js,'$.a')=1 order by json_extract(js,'$.b')
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
强大的查询函数,用于在doc中返回符合条件的节点,select则是在表中返回符合要求的纪录。select json_search('{"a":"abc","b":{"c":"dad"}}','one','%a%')
结果$.a。和like一样可以用%和_匹配,在所有节点的值中匹配,one只返回一个。
select json_search('{"a":"abc","b":{"c":"dad"}}','all','%a%')
结果["$.a","$.b.c"] select json_search('{"a":"abc","b":{"c":"dad"}}','all','%a%',null,'$.b') 结果["$.b.c"]。限制查找范围。select json_search('{"a":"abc","b":{"c":"dad"},"c":{"b":"aaa"}}','all','%a%',null,'$**.b')
结果["$.b.c","$.c.b"]。查找范围还可使用通配符!在每个匹配节点和其下查找。 注意,只有json_extract和json_search中的path才支持通配,其他json_set,json_insert等都不支持。 JSON_LENGTH(json_doc[, path]) 返回数组的长度,如果是object则是属性个数,常量则为1select json_length('[1,2,3]')
结果3select json_length('123')
结果1select json_length('{"a":1,"b":2}')
结果2可再跟path参数
select json_length('{"a":1,"b":[1,2,3]}','$.b') 结果3 JSON_DEPTH(json_doc) 返回doc深度 select json_depth('{}'),json_depth('[]'),json_depth('123') 结果1,1,1select json_depth('[1,2,3,4,5,6]')
结果2select json_depth('{"a":{"b":{"c":1}}}')
结果4