mysql中的JSON类型
汪汪。。。。。。
直接说重点吧,早点解决问题:
1 mysql 5.7中引入的JSON类型
2 JSON对应string类型
3 重点说一下如何使用
3.1 创建表
create table news(
id bigint(20) not null primary key auto_increment,
info json
)
3.2 插入
-- json数组
INSERT INTO news(id,info) VALUES( 1, JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()));
-- json对象
INSERT INTO news(id,info) VALUES( 2,JSON_OBJECT("age", 20, "time", now()));
-- 相当于字面量对象
INSERT INTO news(id,info) VALUES( 3, '{"age":20, "time":"2018-07-14 10:52:00"}');
3.3 查询
-- 查询记录 所有键为age的值
SELECT id,JSON_EXTRACT(info,'$.age') age FROM news;
SELECT id,info->'$.age' age FROM news;
-- 查询key 所有键(数组)
SELECT id,JSON_KEYS(info) infoKey FROM news;
3.4 更新
-- 增加键 没有就是增加 有了就是修改
UPDATE news SET info = json_set(info,'$.ip','192.168.1.1') WHERE id = 2;
-- 变更值
UPDATE news SET info = json_set(info,'$.ip','192.168.1.2') WHERE id = 2;
3.5 删除
UPDATE news SET info = json_remove(info,'$.ip') WHERE id = 2;
4 其他使用
4.1 模拟数据
INSERT INTO news(id,info) VALUES( 4, '{"a": 1, "b": 2, "c": {"d": 4}}');
4.2 JSON_CONTAINS & JSON_CONTAINS_PATH
-- JSON_CONTAINS(json_doc, val[, path])
-- 查询json文档是否在指定path包含指定的数据,包含则返回1,否则返回0。如果有参数为NULL或path不存在,则返回NULL
SELECT JSON_CONTAINS(info, '4', '$.c.d') FROM news; -- 1 是否包含键值
-- JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
-- 查询是否存在指定路径,存在则返回1,否则返回0。如果有参数为NULL,则返回NULL。
-- one_or_all只能取值"one"或"all",one表示只要有一个存在即可;all表示所有的都存在才行。
SELECT JSON_CONTAINS_PATH(info, 'one', '$.a', '$.e') FROM news; -- 1
SELECT JSON_CONTAINS_PATH(info, 'all', '$.a', '$.c.d') FROM news; -- 1
4.3 JSON_EXTRACT
模拟数据
INSERT INTO news(id,info) VALUES( 5, '[10, 20, [30, 40]]');
- JSON_EXTRACT(json_doc, path[, path] ...)
-- 从json文档里抽取数据。如果有参数有NULL或path不存在,则返回NULL。如果抽取出多个path,则返回的数据封闭在一个json array里
SELECT JSON_EXTRACT(info, '$[1]') FROM news WHERE id=5; -- 20
SELECT JSON_EXTRACT(info, '$[1]', '$[0]') FROM news WHERE id=5; -- [20, 10]
SELECT JSON_EXTRACT(info, '$[2][*]') FROM news WHERE id=5; -- [30, 40] 数组2里的所有值
4.4 JSON_KEYS
-- JSON_KEYS(json_doc[, path])
-- 获取json文档在指定路径下的所有键值,返回一个json array。如果有参数为NULL或path不存在,则返回NULL。
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}') FROM news; -- ["a", "b"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') FROM news; -- ["c"] b下面的所有键
SELECT id,json_keys(info) FROM news; -- 得到集合
4.5 JSON_SEARCH
-- 和JSON_EXTRACT是相对的 这是通过值获取键
-- JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
-- 查询包含指定字符串的paths,并作为一个json array返回。如果有参数为NUL或path不存在,则返回NULL。
-- one_or_all:"one"表示查询到一个即返回;"all"表示查询所有。
-- search_str:要查询的字符串。可以用LIKE里的'%'或‘_’匹配。
-- path:在指定path下查。
INSERT INTO news(id,info) VALUES( 5, '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]');
SELECT JSON_SEARCH(info, 'one', 'abc') FROM news; -- "$[0]"
SELECT JSON_SEARCH(info, 'all', 'abc') FROM news; -- ["$[0]", "$[2].x"]
SELECT JSON_SEARCH(info, 'all', 'abc', NULL, '$[2]') FROM news; -- "$[2].x"
SELECT JSON_SEARCH(info, 'all', '10') FROM news; -- "$[1][0].k"
SELECT JSON_SEARCH(info, 'all', '%b%') FROM news; -- ["$[0]", "$[2].x", "$[3].y"] 只要包含b
SELECT JSON_SEARCH(info, 'all', '%b%', NULL, '$[2]') FROM news; -- "$[2].x" 三个值都要有
4.6 JSON_ARRAY_INSERT
数组指定元素前面 同级插入
-- JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
-- 在path指定的json array元素插入val,原位置及以右的元素顺次右移。如果path指定的数据非json array元素,则略过此val;如果指定的元素下标超过json array的长度,则插入尾部。
INSERT INTO news(id,info) VALUES( 6, '["a", {"b": [1, 2]}, [3, 4]]');
SELECT JSON_ARRAY_INSERT(info, '$[1]', 'x'); -- ["a", "x", {"b": [1, 2]}, [3, 4]] FROM news;
SELECT JSON_ARRAY_INSERT(info, '$[100]', 'x'); -- ["a", {"b": [1, 2]}, [3, 4], "x"] FROM news;
SELECT JSON_ARRAY_INSERT(info, '$[1].b[0]', 'x'); -- ["a", {"b": ["x", 1, 2]}, [3, 4]] FROM news;
SELECT JSON_ARRAY_INSERT(info, '$[0]', 'x', '$[3][1]', 'y'); -- ["x", "a", {"b": [1, 2]}, [3, "y", 4]] FROM news;
4.7 JSON_INSERT & JSON_REPLACE &JSON_SET
对象插入
-- JSON_INSERT(json_doc, path, val[, path, val] ...)
-- 在指定path下插入数据,如果path已存在,则忽略此val(不存在才插入)。
INSERT INTO news(id,info) VALUES( 7, '{ "a": 1, "b": [2, 3]}');
已经存在的值不会改变,想改变用下面的JSON_REPLACE
SELECT JSON_INSERT(info, '$.a', 10, '$.c', '[true, false]') FROM news; -- {"a": 1, "b": [2, 3], "c": "[true, false]"}
只替换存在的
-- JSON_REPLACE(json_doc, path, val[, path, val] ...)
-- 替换指定路径的数据,如果某个路径不存在则略过(存在才替换)。如果有参数为NULL,则返回NULL。
SELECT JSON_REPLACE(info, '$.a', 10, '$.c', '[true, false]') FROM news; -- {"a": 10, "b": [2, 3]}
存在则替换,不存在则增加
-- JSON_SET(json_doc, path, val[, path, val] ...)
-- 设置指定路径的数据(不管是否存在)。如果有参数为NULL,则返回NULL。
SELECT JSON_SET(info, '$.a', 10, '$.c', '[true, false]') FROM news; -- {"a": 10, "b": [2, 3], "c": "[true, false]"}
4.8 JSON_REMOVE
-- JSON_REMOVE(json_doc, path[, path] ...)
-- 移除指定路径的数据,如果某个路径不存在则略过此路径。如果有参数为NULL,则返回NULL。
INSERT INTO news(id,info) VALUES( 8, '["a", ["b", "c"], "d"]');
SELECT JSON_REMOVE(info, '$[1]') FROM news; -- ["a", "d"]
免责声明:
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源,请您支持作者,购买正版内容。
6、资源失效,请下方留言,欢迎分享资源链接
文章评论