mysql递归查询(利用存储过程模拟oracle start with...connect by)

之前用oracle的时候，关于菜单的模块，往往使用start with....connect by，去递归查询  但是最近使用mysql的时候，发现没用类似的递归函数，咋办，自己动手丰衣足食，可以通过存储过程实现类似的功能。

先新建menu表，表结构如图所示:

首先NewProc定义临时表去存储查询结果的数据，并调用NewProc2执行查询过程，最后查询临时表数据就行了

NewProc:

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`(rootId int)
BEGIN
 		CREATE TABLE IF NOT EXISTS tmp_menu(tmp_menu_id int(11),pid int(11),name VARCHAR(255));
		TRUNCATE  TABLE tmp_menu;
		CALL NewProc2(rootId);
		SELECT * from tmp_menu;
END

 

NewProc2:

使用while 去循环子Id,然后递归调用。

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc2`(rootId int)
BEGIN
	DECLARE menu_id int(11);
	DECLARE done int (1) DEFAULT 0;
	DECLARE c_menu  CURSOR FOR select id from menu where pid = rootId;
	DECLARE CONTINUE HANDLER FOR NOT found set done = 1;
	set max_sp_recursion_depth = 10;
	OPEN c_menu;
	FETCH c_menu INTO menu_id;
	INSERT into tmp_menu(tmp_menu_id,pid,name) (select id,pid,name from menu where id = rootId);
	WHILE (done = 0) 
	DO
	 CALL NewProc2(menu_id);
	 FETCH c_menu INTO menu_id;
	END WHILE;
	CLOSE c_menu;
END

 

运行函数，输入节点id

得到期望返回结果:

 

 

调用的话，因为项目中使用的是mybatis，所以写下mybatis调用存储的方式：

statementType填写CALLABLE

xml写法如下：

    <select id="getMenu" statementType="CALLABLE" parameterType="long" resultType="map">
        call  NewProc(#{id})
    </select>

得到的返回结果：