场景:根据商品根属性向下拿到从属他的商品
- 表结构:
CREATE TABLE `product` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL COMMENT '商品名称',
`code` varchar(255) NOT NULL COMMENT '商品编号',
`catalog_id` bigint(20) NOT NULL COMMENT '商品分类id',
`brand_id` bigint(20) NOT NULL COMMENT '商品品牌id',
`market_price` decimal(18,2) NOT NULL COMMENT '市场价',
`base_price` decimal(18,2) NOT NULL COMMENT '基本价',
`created_date` datetime NOT NULL COMMENT '创建时间',
`last_modified_date` datetime NOT NULL COMMENT '最后修改时间',
`image` varchar(255) DEFAULT NULL COMMENT '商品封面',
`keyword` varchar(255) DEFAULT NULL COMMENT '商品关键字',
`label` varchar(500) DEFAULT NULL COMMENT '商品标签',
PRIMARY KEY (`id`),
KEY `catalog_id` (`catalog_id`),
KEY `brand_id` (`brand_id`),
CONSTRAINT `product_ibfk_1` FOREIGN KEY (`catalog_id`) REFERENCES `catalog` (`id`),
CONSTRAINT `product_ibfk_2` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COMMENT='商品表';
-
实现思路:
GROUP_CONCAT(expr):进行分组拼接 -> (为了能够将拥有父节点的元素重复的元素进行分组)
FIND_IN_SET(str,strlist):会根据strlist里的所有值进行一个一个的查询,strlist的形式:(1,2,3,4,5,6) -
函数编写
CREATE DEFINER=`root`@`localhost` FUNCTION `getList`(`catalogId` int) RETURNS varchar(400) CHARSET utf8
BEGIN
DECLARE tempParentId VARCHAR(400);
DECLARE sTempReturn varchar(400);
SET tempParentId = CAST(catalogId AS CHAR); -- 这个CAST可以不需要,这只是起到强转类型的作用
SET sTempReturn = "";
WHILE tempParentId IS NOT NULL DO
SET sTempReturn = CONCAT(sTempReturn,",",tempParentId); -- 这句一定要写在上面,因为CONCAT如果拼接了一个Null值,那么结果也为null,这样造成无论多少次循环都是null
SELECT GROUP_CONCAT(id) INTO tempParentId FROM catalog WHERE FIND_IN_SET(p_id,(SELECT tempParentId));
END WHILE;
RETURN sTempReturn;
END
- 踩坑点:
CONCAT拼接NULL时,结果也为NULL
参考:
《MySQL中实现递归查询》