mysql 父子树_mysql – 递归循环 – 父/子树

我认为以下存储过程将产生您要求的结果.我设置了一个表,并用你问题中的数据填充它：

DROP TABLE IF EXISTS `parent_child`;

CREATE TABLE `parent_child` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',

`parent_id` INT(10) UNSIGNED NOT NULL,

`child_id` INT(10) UNSIGNED NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_parent_child_parent_id` (`parent_id`)

)

ENGINE=MyISAM

AUTO_INCREMENT=1

DEFAULT CHARSET=utf8

COLLATE=utf8_unicode_ci

COMMENT '';

INSERT INTO `parent_child`

(`id`,`parent_id`,`child_id`)

VALUES

('1','9','8'),

('2','8','17'),

('3','8','33'),

('4','8','18'),

('5','9','22'),

('6','22','11'),

('7','22','4'),

('8','3','5');

然后我创建了一个程序来逐步获取孩子,直到没有任何剩下来.

DROP PROCEDURE GetChildren;

DELIMITER //

CREATE PROCEDURE GetChildren(root_id INT)

BEGIN

SET @list = root_id;

SET @new_list = root_id;

SET @maxDepth = 4;

SET @depth = 0;

WHILE (@new_list <> "" AND @depth < @maxDepth) DO

SELECT @new_list as `new_list_before`,@list as `whole_list_before`;

SET @depth = @depth + 1;

SET @querystr = CONCAT("SELECT GROUP_CONCAT(`child_id`) as `children` INTO @c FROM `parent_child` WHERE `parent_id` in (?) AND (NOT (`child_id` IN (?)));");

PREPARE stmt1 FROM @querystr;

EXECUTE stmt1 USING @new_list,@list;

IF @c <> "" THEN

SET @list = CONCAT(@list,",",@c);

END IF;

SET @new_list = @c;

SELECT @new_list as `new_list`,@list as `whole_list`;

DEALLOCATE PREPARE stmt1;

END WHILE;

SELECT @list;

END //

DELIMITER ;

最后,这是如何调用root id为9：

CALL GetChildren(9);

这会产生：

@list:

9,8,22,17,33,18

作为参考,这是执行的选择之一：

SELECT GROUP_CONCAT(`child_id`) as `children`

FROM `parent_child`

WHERE `parent_id` in (9,8,22) AND (NOT `child_id` IN (9,8,22))

GROUP BY `parent_id`;