mysql的json数据类型处理基础入门

使用场景

mysql从5.7版本之后加入了json数据类型,官网也明确的说明了将会拥抱nosql.但是对于json数据类型的处理网络上资料还是很少,实际项目中也没有几个人想要去用.
下面主要讨论一下json数据类型最实用的使用姿势.

非关系型数据库

前几年非关系型(nosql)发展形势异常凶猛,特别是对于使用nodejs开发的程序员,node和mongoDB简直就是黄金搭档,但真正用到实际项目中的并不是很多,说明nosql并不能够取代传统数据库,传统数据库只要适当的转型,例如mysql这么搞,就能够稳住自己的地位不变.
同学们用mysql存储一些分层级的数据时是不是感觉特别不爽,类似省市区三级数据.

[
	{id:1,parentId:0,name:"一级菜单A"},
	{id:2,parentId:0,name:"一级菜单B"},
	{id:3,parentId:0,name:"一级菜单C"},
	{id:4,parentId:1,name:"二级菜单A-A"},
	{id:5,parentId:1,name:"二级菜单A-B"},
	{id:6,parentId:2,name:"二级菜单B-A"},
	{id:7,parentId:4,name:"三级菜单A-A-A"}
	...
]

只能以这种姿势存在数据表里,后端查到以后还要用递归函数处理成对象类型,看到递归头就大…

[
	{id:1,parentId:0,name:"一级菜单A",children:[...]},
	{...}
]

这样的数据结构才是我们喜欢的姿势.在5.7版本以前,我们也可以把对象转成json字符串,然后以字符串的格式存入mysql中去,取的时候只要parse一下.
对于全国省市区这种固定不变的数据没什么问题,但如果数据需要经常改动,哪怕只是改动数据里面很小的一个地方,你都需要全读出来,修改完以后再全存进去,操作两次数据库,用脑子想一想一定是非常浪费资源的行为.
现在有了原生json数据的支持,你就可以把各种非结构化数据丢给它了.

常用的操作

nosql并不是不用写sql,反而用sql去操作非结构化数据让我们这些传统程序员感觉很亲切.

--5.7以后版本支持json格式

常用
--创建含json格式的表
CREATE TABLE t_json(id INT PRIMARY KEY, sname VARCHAR(20) , info  JSON);

-- json数组
INSERT INTO t_json(id,sname,info) VALUES( 1, 'name1', JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()));
-- json对象
INSERT INTO t_json(id,sname,info) VALUES( 2, 'name2', JSON_OBJECT("age", 20, "time", now()));
-- 相当于字面量对象
INSERT INTO t_json(id,sname,info) VALUES( 3, 'name3', '{"age":20, "time":"2018-07-14 10:52:00"}');


-- 查询记录  所有键为age的值
SELECT sname,JSON_EXTRACT(info,'$.age') age FROM t_json;
SELECT sname,info->'$.age' age FROM t_json;
-- 查询key  所有键(数组)
SELECT id,JSON_KEYS(info) infoKey FROM t_json;


-- 增加键 没有就是增加 有了就是修改
UPDATE t_json SET info = json_set(info,'$.ip','192.168.1.1') WHERE id = 2;
-- 变更值
UPDATE t_json SET info = json_set(info,'$.ip','192.168.1.2') WHERE id = 2;
-- 删除键
UPDATE t_json SET info = json_remove(info,'$.ip') WHERE id = 2;






查询 详细
-- 测试用变量
set @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
-- JSON_CONTAINS(json_doc, val[, path])
-- 查询json文档是否在指定path包含指定的数据，包含则返回1，否则返回0。如果有参数为NULL或path不存在，则返回NULL
SELECT JSON_CONTAINS(@j, '4', '$.c.d'); -- 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(@j, 'one', '$.a', '$.e'); -- 1
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.c.d'); -- 1


-- JSON_EXTRACT(json_doc, path[, path] ...)
-- 从json文档里抽取数据。如果有参数有NULL或path不存在，则返回NULL。如果抽取出多个path，则返回的数据封闭在一个json array里
set @j2 = '[10, 20, [30, 40]]';
SELECT JSON_EXTRACT(@j2, '$[1]'); -- 20
SELECT JSON_EXTRACT(@j2, '$[1]', '$[0]'); -- [20, 10]
SELECT JSON_EXTRACT(@j2, '$[2][*]'); -- [30, 40] 数组2里的所有值


-- JSON_KEYS(json_doc[, path])
-- 获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL。
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}'); -- ["a", "b"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'); -- ["c"]  b下面的所有键
SELECT id,json_keys(info) FROM t_json;  -- 得到集合


-- 和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下查。
SET @j3 = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j3, 'one', 'abc'); -- "$[0]"
SELECT JSON_SEARCH(@j3, 'all', 'abc'); -- ["$[0]", "$[2].x"]
SELECT JSON_SEARCH(@j3, 'all', 'abc', NULL, '$[2]'); -- "$[2].x"
SELECT JSON_SEARCH(@j3, 'all', '10'); -- "$[1][0].k"
SELECT JSON_SEARCH(@j3, 'all', '%b%'); -- ["$[0]", "$[2].x", "$[3].y"] 只要包含b
SELECT JSON_SEARCH(@j3, 'all', '%b%', NULL, '$[2]'); -- "$[2].x"  三个值都要有







增  对已经存在的json操作 元素内部的尾部追加 子级
-- JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
-- 在指定path的json array尾部追加val。如果指定path是一个json object，则将其封装成一个json array再追加。如果有参数为NULL，则返回NULL。
SET @j4 = '["a", ["b", "c"], "d"]';
SELECT JSON_ARRAY_APPEND(@j4, '$[1][0]', 3); -- ["a", [["b", 3], "c"], "d"]
SET @j5 = '{"a": 1, "b": [2, 3], "c": 4}';
SELECT JSON_ARRAY_APPEND(@j5, '$.b', 'x'); -- {"a": 1, "b": [2, 3, "x"], "c": 4} 
SELECT JSON_ARRAY_APPEND(@j5, '$.c', 'y'); -- {"a": 1, "b": [2, 3], "c": [4, "y"]}
SELECT JSON_ARRAY_APPEND(@j5, '$', 'z'); -- [{"a": 1, "b": [2, 3], "c": 4}, "z"]
(废弃)

数组指定元素前面 同级插入 
-- JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
-- 在path指定的json array元素插入val，原位置及以右的元素顺次右移。如果path指定的数据非json array元素，则略过此val；如果指定的元素下标超过json array的长度，则插入尾部。
SET @j6 = '["a", {"b": [1, 2]}, [3, 4]]';
SELECT JSON_ARRAY_INSERT(@j6, '$[1]', 'x'); -- ["a", "x", {"b": [1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j6, '$[100]', 'x'); -- ["a", {"b": [1, 2]}, [3, 4], "x"]
SELECT JSON_ARRAY_INSERT(@j6, '$[1].b[0]', 'x'); -- ["a", {"b": ["x", 1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j6, '$[0]', 'x', '$[3][1]', 'y'); -- ["x", "a", {"b": [1, 2]}, [3, "y", 4]]

对象插入
-- JSON_INSERT(json_doc, path, val[, path, val] ...)
-- 在指定path下插入数据，如果path已存在，则忽略此val（不存在才插入）。
SET @j7 = '{ "a": 1, "b": [2, 3]}';
已经存在的值不会改变,想改变用下面的JSON_REPLACE
SELECT JSON_INSERT(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 1, "b": [2, 3], "c": "[true, false]"}
只替换存在的
-- JSON_REPLACE(json_doc, path, val[, path, val] ...)
-- 替换指定路径的数据，如果某个路径不存在则略过（存在才替换）。如果有参数为NULL，则返回NULL。
SELECT JSON_REPLACE(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3]}
存在则替换,不存在则增加
-- JSON_SET(json_doc, path, val[, path, val] ...)
-- 设置指定路径的数据（不管是否存在）。如果有参数为NULL，则返回NULL。
SELECT JSON_SET(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3], "c": "[true, false]"}

-- JSON_MERGE(json_doc, json_doc[, json_doc] ...)
-- merge多个json文档。规则如下：
-- 如果都是json array，则结果自动merge为一个json array；
-- 如果都是json object，则结果自动merge为一个json object；
-- 如果有多种类型，则将非json array的元素封装成json array再按照规则一进行mege。
SELECT JSON_MERGE('[1, 2]', '[true, false]'); -- [1, 2, true, false]
SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}'); -- {"id": 47, "name": "x"}
SELECT JSON_MERGE('1', 'true'); -- [1, true]
SELECT JSON_MERGE('[1, 2]', '{"id": 47}'); -- [1, 2, {"id": 47}]
(废弃)


删
-- JSON_REMOVE(json_doc, path[, path] ...)
-- 移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。
SET @j8 = '["a", ["b", "c"], "d"]';
SELECT JSON_REMOVE(@j8, '$[1]'); -- ["a", "d"]


去引号
-- JSON_UNQUOTE(val)
-- 去掉val的引号。如果val为NULL，则返回NULL。
SELECT JSON_UNQUOTE("\"123\""); -- 123
SELECT JSON_UNQUOTE("123"); -- 123








属性 深度
-- JSON_DEPTH(json_doc)
-- 获取json文档的深度。如果参数为NULL，则返回NULL。
-- 空的json array、json object或标量的深度为1。
SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'); -- 1 1 1
SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); -- 2 2
SELECT JSON_DEPTH('[10, {"a": 20}]'); -- 3
长度
-- JSON_LENGTH(json_doc[, path])
-- 获取指定路径下的长度。如果参数为NULL，则返回NULL。　
-- 长度的计算规则：
-- 标量的长度为1；
-- json array的长度为元素的个数；
-- json object的长度为key的个数。
SELECT JSON_LENGTH('[1, 2, {"a": 3}]'); -- 3
SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}'); -- 2
-- 获取某个键值的长度
SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b'); -- 1
类型
-- JSON_TYPE(json_val)
-- 获取json文档的具体类型。如果参数为NULL，则返回NULL。
select JSON_TYPE('[1,2]'); -- ARRAY
SELECT JSON_TYPE('{"aa":2}'); -- OBJECT
有效性
-- JSON_VALID(val)
-- 判断val是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。
SELECT JSON_VALID('{"a": 1}'); -- 1
SELECT JSON_VALID('hello'), JSON_VALID('"hello"'); -- 1



实例
CREATE TABLE `meter_data_2019_12` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `meter_num` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `data_json` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
增加json类型字段
ALTER TABLE meter_data_2019_12 ADD COLUMN data_json JSON;
如果没有则插入,有则追加(完美版)
INSERT INTO meter_data_2019_12(meter_num,data_json) VALUES('11105','{"t20191201":[666,"10:10:01","imgPath"]}') 
ON DUPLICATE KEY UPDATE data_json=JSON_INSERT(data_json, '$.t20191201', CAST('[666,"10:10:01","imgPath"]' AS JSON));
插入数据
INSERT INTO meter_data_2019_12(meter_num,data_json) VALUES('11103','{"t20191201":[666,"10:10:01","imgPath"]}');
追加数据
UPDATE meter_data_2019_12 SET data_json=JSON_INSERT(data_json, '$.t20191203', CAST('[777,"10:10:03","imgPath1"]' AS JSON)) WHERE meter_num='11103';
查找数据 键名不能是纯数字,不能用-符号
SELECT JSON_EXTRACT(data_json,"$.t20191201") js FROM meter_data_2019_12 WHERE meter_num='11103';

如果是数组类型,往数组末尾添加,数组类型不容易查找,数组类型得查找两次才能得到
UPDATE meter_data_2019_12 SET data_json=json_Array_append(data_json,'$','{"time":"2019-12-01"}') WHERE meter_num='11102';
SELECT JSON_SEARCH(data_json,'one','2019-10-12') FROM meter_data_2019_12 WHERE meter_num='11103';   "$[1].time"
SELECT JSON_EXTRACT(data_json, "$[1]") FROM meter_data_2019_12 WHERE meter_num='11103';

能查到的资料很少,以上代码全都是我呕心沥血总结并实践出来的,虽然可求长,相信只要你有心,一定能看懂的…加油

写在最后

如果实在看不下去的话就把最简单的先弄明白,以后新版本可能会变化比较大,毕竟成熟度还不是很高.能做一些简单的应用就够了.