Mysql怎么实现向上递归查找父节点并返回树结构(mysql,开发技术)

这篇“Mysql怎么实现向上递归查找父节点并返回树结构”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“Mysql怎么实现向上递归查找父节点并返回树结构”文章吧。

通过mysql 8.0以下版本实现，一个人多角色id,一个角色对应某个节点menu_id，根节点的父节点存储为NULL, 向上递归查找父节点并返回树结构。如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根 ；如果既有叶子又有根则显示叶子与根。如果 传入角色ID 5，15，25，26，则只查找5，15的所有父节点，因为25，26无根节点

需求：通过mysql 8.0以下版本实现，一个人多角色id,一个角色对应某个节点menu_id，根节点的父节点存储为NULL, 向上递归查找父节点并返回树结构。

如果只有叶子，剔除掉; 如果只有根，只显示一个秃顶的根 ；如果既有叶子又有根则显示叶子与根。测试数据：

如果 传入角色ID【auth_id】: 5，15，25，26，则只查找5，15的所有父节点，因为25，26无根节点

测试数据：

SETNAMESutf8mb4;SETFOREIGN_KEY_CHECKS=0;--------------------------------TablestructureforMenu------------------------------DROPTABLEIFEXISTS`Menu`;CREATETABLE`Menu`(`menu_id`varchar(255)COLLATEutf8mb4_binNOTNULLDEFAULT'0',`sup_menu`varchar(255)COLLATEutf8mb4_binDEFAULTNULL,`auth_id`varchar(255)COLLATEutf8mb4_binDEFAULTNULL,PRIMARYKEY(`menu_id`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_bin;--------------------------------RecordsofMenu------------------------------BEGIN;INSERTINTO`Menu`VALUES('1',NULL,'1');INSERTINTO`Menu`VALUES('11',NULL,'11');INSERTINTO`Menu`VALUES('12','11','12');INSERTINTO`Menu`VALUES('13','11','13');INSERTINTO`Menu`VALUES('14','12','14');INSERTINTO`Menu`VALUES('15','12','15');INSERTINTO`Menu`VALUES('16','13','16');INSERTINTO`Menu`VALUES('17','13','17');INSERTINTO`Menu`VALUES('2','1','2');INSERTINTO`Menu`VALUES('22','21','26');INSERTINTO`Menu`VALUES('25','22','25');INSERTINTO`Menu`VALUES('3','1','3');INSERTINTO`Menu`VALUES('4','2','4');INSERTINTO`Menu`VALUES('5','2','5');INSERTINTO`Menu`VALUES('6','3','6');INSERTINTO`Menu`VALUES('7','3','7');COMMIT;SETFOREIGN_KEY_CHECKS=1;

　方法一：纯存储过程实现

--纯存储过程实现DELIMITER//--如果只有叶子，剔除掉;如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示DROPPROCEDUREifEXISTSquery_menu_by_authid;CREATEPROCEDUREquery_menu_by_authid(INroleIdsvarchar(1000))BEGIN--用于判断是否结束循环declaredoneintdefault0;--用于存储结果集declaremenuidbigint;declaretemp_menu_idsVARCHAR(3000);declaretemp_sup_menusVARCHAR(3000);declarereturn_menu_idsVARCHAR(3000);--定义游标declareidCurcursorforselectmenu_idfromMenuwhereFIND_IN_SET(auth_id,roleIds);--定义设置循环结束标识done值怎么改变的逻辑declarecontinuehandlerfornotFOUNDsetdone=1;openidCur;FETCHidCurINTOmenuid;--临时变量存储menu_id集合SETtemp_menu_ids='';--返回存储menu_id集合SETreturn_menu_ids='';WHILEdone<>1DO--只查找单个auth_id相关的menu_id--通过authid,查找出menu_id,sup_menuisnullSELECTGROUP_CONCAT(T2._menu_id)ast_menu_id,GROUP_CONCAT(T2._sup_menu)ast_sup_menuintotemp_menu_ids,temp_sup_menusFROM(SELECT--保存当前节点。（从叶节点往根节点找，@r保存当前到哪个位置了）。@r初始为要找的节点。--_menu_id当前节点DISTINCT@ras_menu_id,(SELECTCASEWHENsup_menuISNULLTHEN@r:='NULL'ELSE@r:=sup_menuENDFROMMenuWHERE_menu_id=Menu.menu_id)AS_sup_menu,--保存当前的Level@l:=@l+1ASlevelFROM(SELECT@r:=menuid,@l:=0)vars,MenuAStemp--如果该节点没有父节点，则会被置为0WHERE@r<>0ORDERBY@lDESC)T2INNERJOINMenuT1ONT2._menu_id=T1.menu_idORDERBYT2.levelDESC;--满足必须要有根节点NULL字符，则表明有根，否则不拼接给返回值IFFIND_IN_SET('NULL',temp_sup_menus)>0THENSETreturn_menu_ids=CONCAT(temp_menu_ids,',',return_menu_ids);ENDIF;FETCHidCurINTOmenuid;ENDWHILE;CLOSEidCur;--返回指定menu_id的数据集合selectMenu.menu_id,Menu.sup_menu,Menu.auth_idFROMMenuWHEREFIND_IN_SET(menu_id,return_menu_ids)ORDERBYMenu.menu_id*1ASC;END;//DELIMITER;CALLquery_menu_by_authid('5,15,25,26');CALLquery_menu_by_authid('5,17');CALLquery_menu_by_authid('5,11');

方法二：函数+存储过程实现

--函数+存储过程实现--根据叶子节点查找所有父节点及其本身节点。如果只有叶子，剔除掉;如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示.DROPFUNCTIONIFEXISTS`getParentList`;CREATEFUNCTION`getParentList`(in_menu_idvarchar(255))RETURNSvarchar(3000)BEGINDECLAREsTempVARCHAR(3000);DECLAREsTempParVARCHAR(3000);SETsTemp='';SETsTempPar=in_menu_id;--循环递归WHILEsTempParisnotnullDO--判断是否是第一个，不加的话第一个会为空IFsTemp!=''THENSETsTemp=concat(sTemp,',',sTempPar);ELSESETsTemp=sTempPar;ENDIF;SETsTemp=concat(sTemp,',',sTempPar);SELECTgroup_concat(sup_menu)INTOsTempParFROMMenuwheresup_menu<>menu_idandFIND_IN_SET(menu_id,sTempPar)>0;ENDWHILE;RETURNsTemp;END;DELIMITER//--如果只有叶子，剔除掉;如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示DROPPROCEDUREifEXISTSselect_menu_by_authids;CREATEPROCEDUREselect_menu_by_authids(INroleIdsvarchar(3000))BEGIN--用于判断是否结束循环declaredoneintdefault0;--用于存储结果集declaremenuidvarchar(255);declareset_menu_idsVARCHAR(3000);--检查是否单叶子节点单叶子节点sup_menuisnotnull--sup_menu是否为nulldeclare_sup_menuintdefault-1;--定义游标declareidCurcursorforselectmenu_idfromMenuwhereFIND_IN_SET(auth_id,roleIds);--定义设置循环结束标识done值怎么改变的逻辑declarecontinuehandlerfornotFOUNDsetdone=1;OPENidCur;FETCHidCurINTOmenuid;--临时变量存储menu_id集合SETset_menu_ids='';WHILEdone<>1DOSELECTsup_menuINTO_sup_menuFROMMenuWHEREFIND_IN_SET(menu_id,getParentList(menuid))ORDERBYsup_menuASCLIMIT1;--查找指定角色对应的menu_id，sup_menuisnull则说明有根，则进行拼接IF_sup_menuisNULLTHENSELECTCONCAT(set_menu_ids,GROUP_CONCAT(menu_id),',')INTOset_menu_idsFROMMenuwhereFIND_IN_SET(menu_id,getParentList(menuid));ENDIF;FETCHidCurINTOmenuid;ENDWHILE;CLOSEidCur;--返回指定menu_id的数据集合SELECTMenu.menu_id,Menu.sup_menu,Menu.auth_idFROMMenuWHEREFIND_IN_SET(menu_id,set_menu_ids)ORDERBYMenu.menu_id*1ASC;END;//DELIMITER;CALLselect_menu_by_authids('5,15,25,26');CALLselect_menu_by_authids('5,17');CALLselect_menu_by_authids('5,11');

方法三：纯函数实现

--根据叶子节点查找所有父节点及其本身节点。如果只有叶子，剔除掉;如果只有根，只显示一个秃顶的根；如果既有叶子又有根则显示.DROPFUNCTIONIFEXISTS`getParentLists`;--参数1角色id字符串逗号隔开；参数2角色id个数CREATEFUNCTION`getParentLists`(in_roleIdsvarchar(1000),count_roleIdsINT)RETURNSVARCHAR(3000)BEGIN--临时存放通过单个角色查找的单个menu_idDECLAREsMenu_id_by_roleIdVARCHAR(1000);--临时存放通过单个角色查找的多个menu_idDECLAREsMenu_ids_by_roleIdVARCHAR(1000);--临时存放通过多个角色查找的多个menu_idDECLAREsMenu_ids_by_roleIdsVARCHAR(1000);--函数返回的menu_id集合DECLAREsReturn_menu_idsVARCHAR(3000);--当前角色DECLAREcurrent_roleId_rowsINTDEFAULT0;SETsMenu_id_by_roleId='';SETsMenu_ids_by_roleIds='';SETsReturn_menu_ids='';--循环多角色WHILEcurrent_roleId_rows<count_roleIdsDO--依次按角色取1条menu_idSELECTmenu_idINTOsMenu_id_by_roleIdFROMMenuWHEREFIND_IN_SET(auth_id,in_roleIds)ORDERBYmenu_idDESCLIMITcurrent_roleId_rows,1;SETsMenu_ids_by_roleId=sMenu_id_by_roleId;WHILEsMenu_ids_by_roleIdISNOTNULLDO--判断是否是第一个，不加的话第一个会为空IFsMenu_ids_by_roleIds!=''THENSETsMenu_ids_by_roleIds=CONCAT(sMenu_ids_by_roleIds,',',sMenu_ids_by_roleId);ELSESETsMenu_ids_by_roleIds=sMenu_ids_by_roleId;ENDIF;--通过角色id拼接所有的父节点，重点拼接根节点，根节点置为字符NULL，用于后面判断是否有根SELECTGROUP_CONCAT(CASEWHENsup_menuISNULLTHEN'NULL'ELSEsup_menuEND)INTOsMenu_ids_by_roleIdFROMMenuWHEREFIND_IN_SET(menu_id,sMenu_ids_by_roleId)>0;ENDWHILE;SETcurrent_roleId_rows=current_roleId_rows+1;--满足必须要有根节点NULL字符，则表明有根，否则不拼接给返回值IFFIND_IN_SET('NULL',sMenu_ids_by_roleIds)>0THENSETsReturn_menu_ids=CONCAT(sReturn_menu_ids,',',sMenu_ids_by_roleIds);ENDIF;--清空通过单个角色查到的多个menu_id,避免重复拼接SETsMenu_ids_by_roleIds='';ENDWHILE;RETURNsReturn_menu_ids;END;SELECTMenu.menu_id,Menu.sup_menu,Menu.auth_idFROMMenuWHEREFIND_IN_SET(menu_id,getParentLists('15,25,5,26',4))ORDERBYMenu.menu_id+0ASC;SELECTMenu.menu_id,Menu.sup_menu,Menu.auth_idFROMMenuWHEREFIND_IN_SET(menu_id,getParentLists('17,5',2))ORDERBYMenu.menu_id*1ASC;SELECTMenu.menu_id,Menu.sup_menu,Menu.auth_idFROMMenuWHEREFIND_IN_SET(menu_id,getParentLists('11,5',2))ORDERBYMenu.menu_id*2ASC;

以上就是关于“Mysql怎么实现向上递归查找父节点并返回树结构”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注亿速云行业资讯频道。