mySql实现树形查询的函数存储过程例子

mySql实现树形查询的函数存储过程例子由于mySql没有类似oracle的connect by，而实际业务中又会存在这样的需求，这里搞一个树形查询实现的例子，大家执行写自己想买的存储过程。

1、准备测试表

DROP TABLE IF EXISTS `test_channel`;

CREATE TABLE `test_channel` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`cname` varchar(200) DEFAULT NULL,

`parent_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

2、准备一点测试数据

INSERT INTO `test_channel` VALUES (1, '一级', -1);

INSERT INTO `test_channel` VALUES (2, '二级1', 1);

INSERT INTO `test_channel` VALUES (3, '二级2', 1);

INSERT INTO `test_channel` VALUES (4, '二级3', 1);

INSERT INTO `test_channel` VALUES (5, '二级4', 1);

INSERT INTO `test_channel` VALUES (6, '二级5', 1);

INSERT INTO `test_channel` VALUES (7, '三级11', 2);

INSERT INTO `test_channel` VALUES (8, '三级12', 2);

INSERT INTO `test_channel` VALUES (9, '三级13', 2);

INSERT INTO `test_channel` VALUES (10, '三级21', 3);

INSERT INTO `test_channel` VALUES (11, '三级22', 3);

INSERT INTO `test_channel` VALUES (12, '三级31', 4);

INSERT INTO `test_channel` VALUES (13, '三级32', 4);

INSERT INTO `test_channel` VALUES (14, '三级41', 5);

INSERT INTO `test_channel` VALUES (15, '三级42', 5);

INSERT INTO `test_channel` VALUES (16, '三级51', 6);

INSERT INTO `test_channel` VALUES (17, '三级52', 6);

3、下面就是相应函数和过程，注意按照顺序执行，中间有互相引用

a、从某节点向上追溯根节点，递归生成临时表数据

-- ----------------------------

-- Procedure structure for pro_cre_parentlist

-- ----------------------------

DROP PROCEDURE IF EXISTS `pro_cre_parentlist`;

DELIMITER

CREATE DEFINER=`root`@`%` PROCEDURE `pro_cre_parentlist`(IN rootId INT,IN nDepth INT)

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE b INT;

DECLARE cur1 CURSOR FOR SELECT parent_id FROM test_channel WHERE

id=rootId;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET max_sp_recursion_depth=12;

INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);

OPEN cur1;

FETCH cur1 INTO b;

WHILE done=0 DO

CALL pro_cre_parentlist(b,nDepth+1);

FETCH cur1 INTO b;

END WHILE;

CLOSE cur1;

END

DELIMITER ;

b、实现类似Oracle SYS_CONNECT_BY_PATH的功能，递归过程输出某节点id路径-- ----------------------------

-- Procedure structure for pro_cre_pathlist

-- ----------------------------

DROP PROCEDURE IF EXISTS `pro_cre_pathlist`;

DELIMITER

CREATE DEFINER=`root`@`%` PROCEDURE `pro_cre_pathlist`(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE parentid INT DEFAULT 0;

DECLARE cur1 CURSOR FOR

SELECT t.parent_id,CONCAT(CAST(t.parent_id AS CHAR),delimit,pathstr)

FROM test_channel AS t WHERE t.id = nid;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET max_sp_recursion_depth=12;

OPEN cur1;

FETCH cur1 INTO parentid,pathstr;

WHILE done=0 DO

CALL pro_cre_pathlist(parentid,delimit,pathstr);

FETCH cur1 INTO parentid,pathstr;

END WHILE;

CLOSE cur1;

END

DELIMITER ;

c、递归过程输出某节点name路径

-- ----------------------------

-- Procedure structure for pro_cre_pnlist

-- ----------------------------

DROP PROCEDURE IF EXISTS `pro_cre_pnlist`;

DELIMITER

CREATE DEFINER=`root`@`%` PROCEDURE `pro_cre_pnlist`(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE parentid INT DEFAULT 0;

DECLARE cur1 CURSOR FOR

SELECT t.parent_id,CONCAT(https://www.360docs.net/doc/b38419182.html,ame,delimit,pathstr)

FROM test_channel AS t WHERE t.id = nid;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET max_sp_recursion_depth=12;

OPEN cur1;

FETCH cur1 INTO parentid,pathstr;

WHILE done=0 DO

CALL pro_cre_pnlist(parentid,delimit,pathstr);

FETCH cur1 INTO parentid,pathstr;

END WHILE;

CLOSE cur1;

END

DELIMITER ;

d、调用函数输出id路径

-- ----------------------------

-- Function structure for fn_tree_path

-- ----------------------------

DROP FUNCTION IF EXISTS `fn_tree_path`;

DELIMITER

CREATE DEFINER=`root`@`%` FUNCTION `fn_tree_path`(nidINT,delimit VARCHAR(10)) RETURNS varchar(2000) CHARSET utf8

BEGIN

DECLARE pathidVARCHAR(1000);

SET @pathid=CAST(nid AS CHAR);

CALL pro_cre_pathlist(nid,delimit,@pathid);

RETURN @pathid;

END

DELIMITER ;

e、调用函数输出name路径

-- ----------------------------

-- Function structure for fn_tree_pathname

-- ----------------------------

DROP FUNCTION IF EXISTS `fn_tree_pathname`;

DELIMITER

CREATE DEFINER=`root`@`%` FUNCTION `fn_tree_pathname`(nidINT,delimit VARCHAR(10)) RETURNS varchar(2000) CHARSET utf8

BEGIN

DECLARE pathidVARCHAR(1000);

SET @pathid='';

CALL pro_cre_pnlist(nid,delimit,@pathid);

RETURN @pathid;

END

DELIMITER ;

f、调用过程输出子节点

-- ----------------------------

-- Procedure structure for pro_show_childLst

-- ----------------------------

DROP PROCEDURE IF EXISTS `pro_show_childLst`;

DELIMITER

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_show_childLst`(IN rootId INT)

BEGIN

DROP TEMPORARY TABLE IF EXISTS tmpLst;

CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst(sno INT PRIMARY KEY AUTO_INCREMENT,idINT,depth INT);

CALL pro_cre_childlist(rootId,0);

SELECT test_channel.id,CONCAT(SPACE(tmpLst.depth*2),'--

',test_https://www.360docs.net/doc/b38419182.html,ame)

NAME,test_channel.parent_id,tmpLst.depth,fn_tree_path(test_channel.id,'/') path,fn_tree_pathname(test_channel.id,'/') pathname

tmpLst.sno;

END

DELIMITER ;

g、调用过程输出父节点

-- ----------------------------

-- Procedure structure for pro_show_parentLst

-- ----------------------------

DROP PROCEDURE IF EXISTS `pro_show_parentLst`;

DELIMITER

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_show_parentLst`(IN rootId INT)

BEGIN

DROP TEMPORARY TABLE IF EXISTS tmpLst;

CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst (sno INT PRIMARY KEY AUTO_INCREMENT,idINT,depth INT);

CALL pro_cre_parentlist(rootId,0);

SELECT test_channel.id,CONCAT(SPACE(tmpLst.depth*2),'--

',test_https://www.360docs.net/doc/b38419182.html,ame)

NAME,test_channel.parent_id,tmpLst.depth,fn_tree_path(test_channel.id,'/') path,fn_tree_pathname(test_channel.id,'/') pathname

tmpLst.sno;

END

DELIMITER ;

注意：

f、g需要在命令行执行哦，否则会报错。

4、测试

CALL p_show_childLst(1);

其他测试类似