新建一张关系表:
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;