MySQL实现树的遍历

经常在一个表中有父子关系的两个字段，比如empno与manager，这种结构中需要用到树的遍历。在Oracle中可以使用connect by简单解决问题，参见http://blog.csdn.net/wzy0623/archive/2007/06/18/1656345.aspx，但MySQL5.1中还不支持（据说已纳入to do中），要自己写过程或函数来实现。

一、建立测试表和数据：

DROPTABLEIFEXISTS`channel`;

CREATETABLE`channel`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`cname`varchar(200)DEFAULTNULL,
`parent_id`int(11)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=MyISAMAUTO_INCREMENT=19DEFAULTCHARSET=utf8;

/*Dataforthetable`channel`*/

insertinto`channel`(`id`,`cname`,`parent_id`)
values(13,'首页',-1),
(14,'TV580',-1),
(15,'生活580',-1),
(16,'左上幻灯片',13),
(17,'帮忙',14),
(18,'栏目简介',17);

二、利用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：

DELIMITER$$

USE`db1`$$


--从某节点向下遍历子节点
--递归生成临时表数据
DROPPROCEDUREIFEXISTS`createChildLst`$$

CREATEPROCEDURE`createChildLst`(INrootIdINT,INnDepthINT)
BEGIN
DECLAREdoneINTDEFAULT0;
DECLAREbINT;
DECLAREcur1CURSORFORSELECTidFROMchannelWHEREparent_id=rootId;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
SETmax_sp_recursion_depth=12;

INSERTINTOtmpLstVALUES(NULL,rootId,nDepth);

OPENcur1;

FETCHcur1INTOb;
WHILEdone=0DO
CALLcreateChildLst(b,nDepth+1);
FETCHcur1INTOb;
ENDWHILE;

CLOSEcur1;
END$$


--从某节点向上追溯根节点
--递归生成临时表数据
DROPPROCEDUREIFEXISTS`createParentLst`$$

CREATEPROCEDURE`createParentLst`(INrootIdINT,INnDepthINT)
BEGIN
DECLAREdoneINTDEFAULT0;
DECLAREbINT;
DECLAREcur1CURSORFORSELECTparent_idFROMchannelWHEREid=rootId;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
SETmax_sp_recursion_depth=12;

INSERTINTOtmpLstVALUES(NULL,rootId,nDepth);

OPENcur1;

FETCHcur1INTOb;
WHILEdone=0DO
CALLcreateParentLst(b,nDepth+1);
FETCHcur1INTOb;
ENDWHILE;

CLOSEcur1;
END$$


--实现类似OracleSYS_CONNECT_BY_PATH的功能
--递归过程输出某节点id路径
DROPPROCEDUREIFEXISTS`createPathLst`$$

CREATEPROCEDURE`createPathLst`(INnidINT,INdelimitVARCHAR(10),INOUTpathstrVARCHAR(1000))
BEGIN
DECLAREdoneINTDEFAULT0;
DECLAREparentidINTDEFAULT0;
DECLAREcur1CURSORFOR
SELECTt.parent_id,CONCAT(CAST(t.parent_idASCHAR),delimit,pathstr)
FROMchannelAStWHEREt.id=nid;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
SETmax_sp_recursion_depth=12;

OPENcur1;

FETCHcur1INTOparentid,pathstr;
WHILEdone=0DO
CALLcreatePathLst(parentid,delimit,pathstr);
FETCHcur1INTOparentid,pathstr;
ENDWHILE;

CLOSEcur1;
END$$


--递归过程输出某节点name路径
DROPPROCEDUREIFEXISTS`createPathnameLst`$$

CREATEPROCEDURE`createPathnameLst`(INnidINT,INdelimitVARCHAR(10),INOUTpathstrVARCHAR(1000))
BEGIN
DECLAREdoneINTDEFAULT0;
DECLAREparentidINTDEFAULT0;
DECLAREcur1CURSORFOR
SELECTt.parent_id,CONCAT(t.cname,delimit,pathstr)
FROMchannelAStWHEREt.id=nid;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
SETmax_sp_recursion_depth=12;

OPENcur1;

FETCHcur1INTOparentid,pathstr;
WHILEdone=0DO
CALLcreatePathnameLst(parentid,delimit,pathstr);
FETCHcur1INTOparentid,pathstr;
ENDWHILE;

CLOSEcur1;
END$$


--调用函数输出id路径
DROPFUNCTIONIFEXISTS`fn_tree_path`$$

CREATEFUNCTION`fn_tree_path`(nidINT,delimitVARCHAR(10))RETURNSVARCHAR(2000)CHARSETutf8
BEGIN
DECLAREpathidVARCHAR(1000);

SET@pathid=CAST(nidASCHAR);
CALLcreatePathLst(nid,delimit,@pathid);

RETURN@pathid;
END$$


--调用函数输出name路径
DROPFUNCTIONIFEXISTS`fn_tree_pathname`$$

CREATEFUNCTION`fn_tree_pathname`(nidINT,delimitVARCHAR(10))RETURNSVARCHAR(2000)CHARSETutf8
BEGIN
DECLAREpathidVARCHAR(1000);

SET@pathid='';
CALLcreatePathnameLst(nid,delimit,@pathid);

RETURN@pathid;
END$$


--调用过程输出子节点
DROPPROCEDUREIFEXISTS`showChildLst`$$

CREATEPROCEDURE`showChildLst`(INrootIdINT)
BEGIN
DROPTEMPORARYTABLEIFEXISTStmpLst;
CREATETEMPORARYTABLEIFNOTEXISTStmpLst
(snoINTPRIMARYKEYAUTO_INCREMENT,idINT,depthINT);

CALLcreateChildLst(rootId,0);

SELECTchannel.id,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname)NAME,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/')path,fn_tree_pathname(channel.id,'/')pathname
FROMtmpLst,channelWHEREtmpLst.id=channel.idORDERBYtmpLst.sno;
END$$

--调用过程输出父节点
DROPPROCEDUREIFEXISTS`showParentLst`$$

CREATEPROCEDURE`showParentLst`(INrootIdINT)
BEGIN
DROPTEMPORARYTABLEIFEXISTStmpLst;
CREATETEMPORARYTABLEIFNOTEXISTStmpLst
(snoINTPRIMARYKEYAUTO_INCREMENT,idINT,depthINT);

CALLcreateParentLst(rootId,0);

SELECTchannel.id,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname)NAME,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/')path,fn_tree_pathname(channel.id,'/')pathname
FROMtmpLst,channelWHEREtmpLst.id=channel.idORDERBYtmpLst.sno;
END$$


DELIMITER;

三、测试

CALLshowChildLst(-1);
CALLshowChildLst(13);
CALLshowChildLst(14);
CALLshowChildLst(17);
CALLshowChildLst(18);

CALLshowParentLst(-1);
CALLshowParentLst(13);
CALLshowParentLst(14);
CALLshowParentLst(17);
CALLshowParentLst(18);

四、遗留问题
1. 因为mysql对动态游标的支持不够，所以要想做成通用的过程或函数比较困难，可以利用两个临时表来转换（同时去掉了递归调用）参考http://jan.kneschke.de/projects/mysql/sp/sp_tree.sql，是个相对通用的实现。
2. 目前来看无论哪种实现，效率都不太好，希望mysql自己能实现oracle 的connect by 功能，应该会比较优化。

参考：MySQL中进行树状所有子节点的查询