mysql 递归查询下级

CREATE TABLE `ys_recommendation_code_user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`parent_user_id` int NOT NULL COMMENT '推荐者的用户id',
`user_id` int NOT NULL COMMENT '用户id',
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
PRIMARY KEY (`id`) ,
INDEX `idx_parent_user_id_user_id` (`parent_user_id`, `user_id`)
)
COMMENT='推荐码-用户关联表';

2.插入数据

insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('2','100','102','0000-00-00 00:00:00','0000-00-00 00:00:00');
insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('3','100','103','0000-00-00 00:00:00','0000-00-00 00:00:00');
insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('4','101','104','0000-00-00 00:00:00','0000-00-00 00:00:00');
insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('5','101','105','0000-00-00 00:00:00','0000-00-00 00:00:00');
insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('6','101','106','0000-00-00 00:00:00','0000-00-00 00:00:00');
insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('7','104','107','0000-00-00 00:00:00','0000-00-00 00:00:00');
insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('8','104','108','0000-00-00 00:00:00','0000-00-00 00:00:00');
insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('9','104','109','0000-00-00 00:00:00','0000-00-00 00:00:00');

3.查询

SELECT user_id FROM (
 SELECT t1.user_id,
 IF(FIND_IN_SET(parent_user_id, @pids) > 0, @pids := CONCAT(@pids, ',', user_id), 0) AS ischild
 FROM (
 SELECT user_id,parent_user_id FROM ys_recommendation_code_user t ORDER BY parent_user_id, user_id
 ) t1,
 (SELECT @pids := 这里是需要查询的user_id) t2
 ) t3 WHERE ischild != 0

4.查询结果 user_id = 100