MySql通过父节点查询所有子节点

实现方案

根据父节点id获取所有子节点的id(包括自己的id)

注意创建函数时修改mysql结束符。';'作为mysql的默认结束符,在函数中会影响函数的创建。

mysql> DELIMITER $$ 

• 函数

DROP FUNCATION  IF EXISTS getAllChildDir;
CREATE DEFINER=`root`@`%` FUNCTION `getAllChildDir`(rootId INT) RETURNS varchar(1000) CHARSET utf8
    COMMENT '获得某个子节点底下所有的子目录'
BEGIN
   DECLARE sTemp VARCHAR(1000);
   DECLARE sTempChd VARCHAR(1000);
   DECLARE result VARCHAR(1000);
   SET sTemp = '$';
   SET sTempChd = cast(rootId as CHAR);
   WHILE sTempChd is not null DO
     SET sTemp = concat(sTemp,',',sTempChd);
     SELECT group_concat(ID) INTO sTempChd FROM test where FIND_IN_SET(PARENT_ID,sTempChd)>0;
   END WHILE;
	 SET result = SUBSTRING(sTemp,3);
   RETURN result;
 END

• sql

SELECT concat(#{rootId},',',GROUP_CONCAT(a.id)) as ids FROM
    (SELECT id FROM
	(SELECT t1.id,
	IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild 
	FROM
            ( SELECT id, parent_id FROM test t ORDER BY parent_id, id ) t1,
            ( SELECT @pids := #{rootId} ) t2 
    ) t3 
WHERE	ischild != 0 ) a

• java实现-略

数据准备

• 创建脚本

DROP TABLE IF EXISTS `TEST`;
CREATE TABLE `TEST` (
    `ID` BIGINT ( 20 )  UNSIGNED  NOT NULL  AUTO_INCREMENT COMMENT '主键',
    `TYPE_LEVEL` INT ( 2 ) NULL DEFAULT NULL COMMENT '目录层级',
    `PARENT_ID` BIGINT ( 20 ) NULL DEFAULT NULL COMMENT '父类别ID，TYPE_LEVEL=1时，PARENT_ID=0',
    `TYPE_NAME` VARCHAR ( 128 ) DEFAULT NULL COMMENT '目录名称',
     PRIMARY KEY ( `ID` ) USING BTREE 
) ENGINE = INNODB  CHARACTER SET = UTF8 COLLATE = UTF8_GENERAL_CI COMMENT = '测试表' ;

• 插入数据

INSERT INTO `test` ( `TYPE_LEVEL`, `PARENT_ID`, `TYPE_NAME`) VALUES ( 0, 0, '测试');
INSERT INTO `test` ( `TYPE_LEVEL`, `PARENT_ID`, `TYPE_NAME`) VALUES ( 0, 0, '测试');
INSERT INTO `test` ( `TYPE_LEVEL`, `PARENT_ID`, `TYPE_NAME`) VALUES ( 0, 0, '测试');
INSERT INTO `test` ( `TYPE_LEVEL`, `PARENT_ID`, `TYPE_NAME`) VALUES ( 1, 1, '测试');
INSERT INTO `test` ( `TYPE_LEVEL`, `PARENT_ID`, `TYPE_NAME`) VALUES ( 1, 1, '测试');
INSERT INTO `test` ( `TYPE_LEVEL`, `PARENT_ID`, `TYPE_NAME`) VALUES ( 1, 1, '测试');
INSERT INTO `test` ( `TYPE_LEVEL`, `PARENT_ID`, `TYPE_NAME`) VALUES ( 1, 1, '测试');
INSERT INTO `test` ( `TYPE_LEVEL`, `PARENT_ID`, `TYPE_NAME`) VALUES ( 1, 2, '测试');