mysql根据父节点id获取所有子节点

DROP TABLE IF EXISTS `city`;

CREATE TABLE `city` (
  `i_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(11) DEFAULT NULL,
  `c_name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `status` char(1) COLLATE utf8_bin DEFAULT '0',
  PRIMARY KEY (`i_id`)
) ENGINE=InnoDB AUTO_INCREMENT=247 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `city` */

insert  into `city`(`i_id`,`p_id`,`c_name`,`status`) values 

(1,0,'北京','0'),

(2,1,'朝阳区','0'),

(3,1,'海淀区','0'),

(4,1,'石景山区','0'),

(5,4,'永乐小区','0'),

(6,4,'N号楼','0'),

(7,5,'朝阳公园','0'),

(8,2,'幸福溪里','0'),

(9,2,'国贸中心','0');

创建存储过程

/*!50003 DROP PROCEDURE IF EXISTS  `getAllNode` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllNode`(in id int)
BEGIN
	
	CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst(id int primary key auto_increment,i_id int,p_id int,c_name varchar(50));
	DELETE FROM tmpLst;
	CALL getChildList(id,0);
	
	select tmpLst.*,city.* from tmpLst,city where tmpLst.i_id=city.i_id ;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `getChildList` */

/*!50003 DROP PROCEDURE IF EXISTS  `getChildList` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `getChildList`(IN id INT,IN parent_id INT)
BEGIN
	
	DECLARE b INT;
	-- 遍历数据结束标志
	DECLARE done INT DEFAULT true;
	-- 定义一个游标
	DECLARE cur1 CURSOR FOR SELECT i_id FROM city WHERE p_id=id;
	-- 将结束标志绑定到游标
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = false;
	SET @@max_sp_recursion_depth = 10;
	insert into tmpLst (i_id,p_id)VALUES(id,parent_id); 
	OPEN cur1;
	FETCH cur1 INTO b;
	WHILE done DO
		CALL getChildList(b,id);
		FETCH cur1 INTO b;
	END WHILE;
	CLOSE cur1;
	
	
    END */$$
DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

元数据

运行CALL getAllNode(2);