mysql5.7之JSON数据类型
1、json对象
1.1、方法
- 使用对象操作的方法进行查询:
字段->'$.json属性'
- 使用函数进行查询:
json_extract(字段, '$.json属性')
- 获取JSON数组/对象长度:
JSON_LENGTH()
1.2、数据
CREATE TABLE `test` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID', `goods_sn` varchar(25) NOT NULL DEFAULT '' COMMENT '商品编码', `desc_attr` json NOT NULL COMMENT '描述属性', PRIMARY KEY (`id`) ) ENGINE=InnoDB COMMENT='TEST'; INSERT INTO `test`.`test`(`id`, `goods_sn`, `desc_attr`) VALUES (1, 'A0001', '{\"tag\": [\"GRS\", \"GOTS\"], \"size\": \"M\", \"color\": \"红色\", \"material\": \"尼龙\"}'); INSERT INTO `test`.`test`(`id`, `goods_sn`, `desc_attr`) VALUES (2, 'A0002', '{\"tag\": [\"GRS\", \"GOTS\", \"MTD\"], \"size\": \"LA\", \"color\": \"黄色\", \"material\": \"纯棉\"}'); { "tag": ["GRS", "GOTS"], "size": "M", "color": "红色", "material": "尼龙" }
1.3、查询
-- 查询面料不为空的商品 select * from test where desc_attr->'$.material' is not null; select * from test where JSON_EXTRACT(desc_attr, '$.material') is not null; -- 查询面料为纯棉的商品 select * from test where desc_attr->'$.material'='纯棉'; select * from test where JSON_EXTRACT(desc_attr, '$.material')='纯棉'; -- 查询标签数量大于2的商品 select * from test where JSON_LENGTH(desc_attr->'$.tag')>2;
2、json数组
2.1、方法
- 对象操作方式查询:
字段->'$[0].属性'
- 使用函数查询:
JSON_CONTAINS(字段,JSON_OBJECT('json属性', '内容'))
- 获取JSON数组/对象长度:
JSON_LENGTH()
2.2、数据
CREATE TABLE `test2` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID', `goods_sn` varchar(25) NOT NULL DEFAULT '' COMMENT '商品编码', `desc_attrs` json NOT NULL COMMENT '描述属性,多个', PRIMARY KEY (`id`) ) ENGINE=InnoDB COMMENT='TEST2'; INSERT INTO `test`.`test2`(`id`, `goods_sn`, `desc_attrs`) VALUES (1, 'A0001', '[{\"tag\": [\"GRS\", \"GOTS\"], \"size\": \"M\", \"color\": \"红色\", \"material\": \"尼龙\"}, {\"tag\": [\"GRS\", \"GOTS\", \"MTD\"], \"size\": \"LA\", \"color\": \"黄色\", \"material\": \"纯棉\"}]'); INSERT INTO `test`.`test2`(`id`, `goods_sn`, `desc_attrs`) VALUES (2, 'A0002', '[{\"tag\": [\"GRS\", \"GOTS\"], \"size\": \"M\", \"color\": \"红色\", \"material\": \"尼龙\"}, {\"tag\": [\"GRS\", \"GOTS\", \"MTD\"], \"link\": \"xxx\", \"size\": \"LA\", \"color\": \"黄色\", \"material\": \"纯棉\"}]'); INSERT INTO `test`.`test2`(`id`, `goods_sn`, `desc_attrs`) VALUES (3, 'A0003', '[]'); [ { "tag":["GRS", "GOTS"], "size":"M", "color":"红色", "material":"尼龙" }, { "tag":["GRS", "GOTS", "MTD"], "size":"LA", "color":"黄色", "material":"纯棉" } ]
2.3、查询
-- 查询描述属性不为空的商品 select * from test2 where JSON_LENGTH(desc_attrs) > 0; -- 查询第1项存在颜色属性的商品 select * from test2 where desc_attrs->'$[0].color' is not null; -- 查询任意项存在链接属性的商品 select * from test2 where desc_attrs->'$[*].link' is not null; -- 查询任意项存在链接等于xxx属性的商品 select * from test2 where JSON_CONTAINS(desc_attrs,JSON_OBJECT('link', 'xxx')); 注意 copy-- [{"link":"xxx"}] select desc_attrs->'$[*].link' from test2 where id=2; -- 查询结果为`["xxx"]` -- 返回每一项的link,所以是个数组
为JSON格式的数据添加索引
* 在Mysql 5.7.8中,MySQL支持由RFC 7159定义的本地JSON数据类型,它支持对JSON(javascript对象标记)文档中的数据进行有效访问.
* MySQL会对DML JSON数据自动验证。无效的DML JSON数据操作会产生错误.
* 优化的存储格式。存储在JSON列中的JSON文档转换为一种内部格式,允许对Json元素进行快速读取访问.
* MySQL Json类型支持通过虚拟列方式建立索引,从而增加查询性能提升.
创建Json索引表 json_key [ name 为虚拟列, virtual 表明不占用磁盘空间 ]
[ GENERATED ALWAYS 与 VIRTUAL可以不写 ]
指定获取json中的name key
CREATE TABLE json_key( -> uid BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT, -> data JSON NOT NULL, -> name VARCHAR(32) GENERATED ALWAYS AS (json_extract(data, '$.name')) VIRTUAL -> )ENGINE=INNODB CHARSET=utf8mb4;
创建虚拟列name索引
alter table users add key (name);
插入数据带 data中name key [ 插入数据时需要显示指定非虚拟列 ]
INSERT INTO json_key(uid, data) SELECT NULL, JSON_OBJECT('name', 'tom', 'sex', 'male', 'age', '26');
插入数据不带 data中name key
INSERT INTO json_key(uid, data) SELECT NULL, JSON_OBJECT('sex', 'female', 'age', '29');
通过json方法查询
explain select * from json_key where json_extract(data, '$.name') = 'tom'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: json_key partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: Using where
通过虚拟列查询
explain select * from json_key where name = 'tom'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ref possible_keys: name key: name key_len: 1023 ref: const rows: 1 filtered: 100.00 Extra: NULL
使用汇总:
[数组]
-- 查询第一个标签为测量的记录
select tags, JSON_EXTRACT(tags, '$[0]') as first_tag from data where JSON_EXTRACT(tags, '$[0]') = '测量'; select tags, JSON_EXTRACT(tags, '$[0]') as first_tag from data where tags->'$[0]' = '测量'; -- 查询该字段所有内容 select tags, JSON_EXTRACT(tags, '$[*]') as all_tag from data ; -- 字段中元素个数 select tags, JSON_EXTRACT(tags, '$[0]') as first_tag, JSON_LENGTH(tags) from data ; -- 元素中包含某个元素 select * from ai_volc_template_data where JSON_CONTAINS(tags, '"测量"'); -- 注意需要引号