测试的表的结构如下:
CREATE TABLE `userinfo` (
`id` int(11) NOT NULL,
`username` varchar(255) DEFAULT NULL,
`jsontest` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
增加记录操作:
json数据格式1:
insert into userinfo
values("3","sda",'{"tag":["1","2","3","5"]');
数据格式2:
insert into userinfo values
("3","sda",'{"tag":["1","2","3","5"],"tagid":2}');
查询
无条件查询:
select * from userinfo;
json 的key选择性显示
select id,json_extract(jsontest,"$.tagid") AS tagid from userinfo ;
json的key值对应的value不可为空:
select id,json_extract(jsontest,"$.tagid")
AS tagid from userinfo
where json_extract(jsontest,"$.tagid") is not null;