mysql json 数据结构查询。
创建表结构:
CREATE TABLE `test`.`test_json` (`id` INT NOT NULL AUTO_INCREMENT , `attrs` JSON NULL DEFAULT NULL , `type` JSON NULL DEFAULT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB COMMENT = '测试json数据查询';
插入测试数据:
INSERT INTO `test_json` (`attrs`, `type`) VALUES ( JSON_OBJECT("name","张三","age",20,"strage","20","data",JSON_ARRAY("新品","热销",1,"0")), JSON_ARRAY("新品","热销",1,"0") ) ; INSERT INTO `test_json` (`attrs`, `type`) VALUES ( JSON_OBJECT("name","李四","age",18,"strage","16","data",JSON_ARRAY("老品","滞销",2,"3")), JSON_ARRAY("老品","滞销",2,"3") ) ;
查询 type 值包含 “新品”的数据:
SELECT * FROM test_json WHERE JSON_CONTAINS(type, '"新品"');
注意:
SELECT * FROM test_json WHERE JSON_CONTAINS(type, '新品'); //会报错,错误:#3141 - Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.
查询 attrs 的 age 值为 20 的数据:
SELECT * FROM test_json WHERE JSON_CONTAINS(attrs, '20', '$.age');
查询 attrs 的 data 值包含 “新品”的数据:
SELECT * FROM `test`.`test_json` WHERE JSON_EXTRACT(attrs, '$.data') LIKE '%新品%';
查询 attrs 的 data 值精确包含 “新品”的数据:
SELECT * FROM test_json WHERE JSON_CONTAINS(attrs->'$.data', CAST('["新品"]' AS JSON));