MySQL树递归查询

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