MySQL树形查询
Oracle的Start with…Connect By可以实现树的递归查询,mysql与Oracle不同,没有prior关键字,mysql需要自己写函数或存储过程来实现树的递归查询。
数据库表
DROP TABLE IF EXISTS category;
CREATE TABLE category (
categoryId int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品类目id',
categoryName varchar(64) NOT NULL COMMENT '商品类目名称',
parentId int(10) unsigned DEFAULT NULL COMMENT '父级类目id',
status enum('delete','disabled','enable') NOT NULL DEFAULT 'enable'
COMMENT 'enabe-可用;disabled-不可用;delete-删除',
PRIMARY KEY (categoryId),
KEY parentId (parentId),
CONSTRAINT category_ibfk_1 FOREIGN KEY (parentId) REFERENCES category (categoryId)
ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=888889 DEFAULT CHARSET=utf8;
– 插入数据
INSERT INTO `category` VALUES ('59', '食品生鲜', null, 'enable');
INSERT INTO `category` VALUES ('75', '茗茶', '59', 'enable');
INSERT INTO `category` VALUES ('76', '美妆个护', null, 'enable');
INSERT INTO `category` VALUES ('77', '洗发护发', '76', 'enable');
INSERT INTO `category` VALUES ('79', '面膜', '78', 'enable');
INSERT INTO `category` VALUES ('81', '沐浴', '82', 'enable');
INSERT INTO `category` VALUES ('82', '身体护理', '76', 'enable');
INSERT INTO `category` VALUES ('83', '颈部', '82', 'enable');
INSERT INTO `category` VALUES ('84', '手足护品', '82', 'enable');
INSERT INTO `category` VALUES ('87', '牙膏/牙粉', '80', 'enable');
INSERT INTO `category` VALUES ('90', '香水彩妆', '76', 'enable');
INSERT INTO `category` VALUES ('91', '底妆', '90', 'enable');
INSERT INTO `category` VALUES ('92', '腮红', '90', 'enable');
INSERT INTO `category` VALUES ('100', '洗发', '77', 'enable');
INSERT INTO `category` VALUES ('101', '护发', '77', 'enable');
1. 根据分类ID查询包含该分类在内的所有父类ID
CREATE FUNCTION queryAllCategoryParentId(cId INT)
RETURNS VARCHAR(1000)
BEGIN
DECLARE sTemp VARCHAR(100);
DECLARE flag int(8);
SET sTemp='$';
SELECT count(1) INTO flag FROM category WHERE categoryId=cId;
IF flag = 1 then
WHILE cId IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',cId);
SELECT parentId INTO cId FROM category WHERE categoryId=cId;
END WHILE;
END IF;
RETURN sTemp;
END
2. 根据分类ID查询包含该分类在内的所有子类ID
CREATE FUNCTION getRecommendChildList(cId INT)
//cId为你要查询的节点。
RETURNS VARCHAR(1000)
BEGIN
DECLARE pTemp VARCHAR(1000);
DECLARE cTemp VARCHAR(1000);
SET pTemp = '$';
SET cTemp =cast(cId as CHAR);
WHILE cTemp is not null DO
SET pTemp = concat(pTemp,',',cTemp);
SELECT group_concat(categoryId) INTO cTemp FROM category
WHERE FIND_IN_SET(parentId,cTemp)>0;
END WHILE;
RETURN pTemp;
END