Mysql实现父子递归(父查子，子查父)

新建一张关系表：

CREATE TABLE `ds_relation_folder` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  `parent_folder_id` int(11) NOT NULL COMMENT '父类文件夹Id',
  `child_folder_id` int(11) DEFAULT NULL COMMENT '子文件夹Id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `is_disable` tinyint(4) DEFAULT '0',
  `folder_layer` int(11) NOT NULL COMMENT '文件夹的层数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

父查子递归逻辑实现：

SELECT
	@parentIds,
	@lev:=@lev+1,
	(select @parentIds:= group_concat(id) from ds_relation_folder where find_in_set(parent_folder_id ,@parentIds))
FROM
	ds_relation_folder drf ,
	(select @parentIds:=6,@lev:=0) b
	where @parentIds is not null;

子查父递归逻辑实现：

select
        T2.child_folder_id
        from
        (
        select
        @r as _child_folder_id,
        (
        select
        @r := parent_folder_id
        from
        ds_relation_folder drf
        where
        child_folder_id = _child_folder_id) as parent_folder_id,
        @l := @l + 1 as tmp
        from
        (
        select
        @r := 11,
        @l := 0) vars,
        ds_relation_folder h
        where
        @r != 0) T1
        join ds_relation_folder T2 on
        T1._child_folder_id = T2.child_folder_id
        order by
        T1.tmp desc;