MySQL中怎么实现树状数据
本篇文章为大家展示了MySQL中怎么实现树状数据，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
0 树状数据的分类
我们在mysql数据库设计的时候，会遇到一种树状的数据。如公司下面分开数个部门，部门下面又各自分开数个科室，以此形成树状的数据。关于树状的数据，按层级数大致可分为一下两类：
前者的优点在于，由于每一层级均有各自含义，数据库的整体设计更为方便，可将某一子节点的不同上级节点均存储在数据库中，同样以某集团为例：
这样设计的表格冗余较多，但在各种类型查询的时候效率较高.在插入，更新(含子机构,由于业务逻辑特点,机构之间的更新一般是平行转移)，删除(含子机构)的时候，由于冗余信息较多，数据操作时所需进行的查询获得也较简单。根据情况,部分冗余信息也考虑删去，如父级节点code，删去一些设计必然会导致部分查询的效率或复杂度提升，这个就需要根据实际情况来取舍平衡了。
缺点有两个：
一个是当层级数量较多的时候,需要存储大量的冗余信息.当然也可以考虑节约方案：1)不存储像n级祖先code这样的字段，但这样就无法利用固定层级设计带来的高效查询特性，是不建议这么做的；2)n级存储不使用code而改用id，这样做主要是在数据迁移或者他表利用的时候不方便。
另一个缺点是，当需求方给出要求，需要对当前机构重新洗牌，变更层级数的时候，你会非常头疼。
后者的优缺点则与前者的优缺点恰好相反，非固定的层级限制非常灵活，而缺点就是查询及数据操作上两方面的不便，这也是本文所要讲述的重点，即如何设计非固定层级的树状数据。
1 非固定层级树状数据的设计方式--祖先路径
树状数据最简单的一种设计方式是,只增加父级id。但这种设计方式给查询后代节点带来了极大的不便，据我所知，尚没有一种不通过函数/存储过程这样循环遍历的查询方式，来一次获取某个节点的所有后代节点或是祖先节点。(此前找到过一个较复杂的查询后代节点的sql，利用的也是祖先节点的id大于后代节点id的特性，但有可能存在通过更新节点使后代节点id大于祖先节点id，所以也不严谨，在此不进行详述)
对于非固定层级树状数据的一种设计方式是：增加祖先路径(ancestor_path)，具体可参考下表：
id | 节点名称 | 父id | 祖先路径
---|---|---|---1|node1|0|0,2|node2|0|0,3|node1.1|1|0,1,4|node1.2|1|0,1,5|node2.1|2|0,2,6|node1.1.1|3|0,1,3,7|node1.1.2|3|0,1,3,8|node1.2.1|4|0,1,4,9|node2.1.1|5|0,2,5,
实际设计时，还可考虑加入层级这个冗余字段，但我在实际使用的过程中很少用到这个字段。
这样，在加了这个字段之后，任意节点的所有祖先节点信息就都可通过这样一条数据全部获取。
祖先路径的设定具有以下特点：
没有父节点的根节点，父id默认为'0'，祖先路径默认为'0,'；
每增加的一个子节点,祖先路径都是在要增加的子节点的父节点的祖先路径上增加父id和','；参考的表结构如下：
CREATETABLE`t_node`(`node_id`int(11)NOTNULLAUTO_INCREMENT,`node_name`varchar(50)NOTNULL,`p_id`int(11)NOTNULL,`ancestor_path`varchar(100)NOTNULL,PRIMARYKEY(`node_id`))ENGINE=InnoDBAUTO_INCREMENT=10DEFAULTCHARSET=utf8;
2 祖先路径的查询
设计的树节点的查询，主要有两种，一种是查询某个节点的所有后代节点(与查询祖先节点为某个已知节点的所有节点集合是一个意思)，这种也是最常用的一种查询；一种是查询某个节点的所有祖先节点，这种不太常用。
1. 查询某个节点的所有后代节点 参考示例如下：
SELECT*FROMt_nodeWHEREancestor_pathLIKECONCAT((SELECT*FROM(SELECTancestor_pathFROMt_nodeWHEREnode_id=?)wt),?,',%')
以上sql即是对id为？的某个节点的所有后代节点的查询方式一，还可使用以下方式：
SELECT*FROMt_nodeWHEREancestor_pathLIKECONCAT('%,',?,',%')
查询方式二的方式更加简洁。但考虑到查询方式一只用到了右模糊查询，可以使用索引，所以还是建议使用方式一进行查询。
需要注意的是以上两种方式查到的节点集合都不包含子节点，如果需要包含该节点的信息，还需要加上
...ORnode_id=?
2. 查询某个节点的所有祖先节点
SELECT*FROMt_nodeWHEREnode_idREGEXPCONCAT('^(',REPLACE((SELECT*FROM(SELECTancestor_pathFROMt_nodeWHEREnode_id=?)wt),',','|'),'0)$')
以上方式查询祖先节点的效率确实不是很高，但考虑到该查询本身并不用，便姑且用之了。
3 祖先路径的插入,更新和删除
分别分插入，更新和删除来讲：
1. 插入
INSERTINTOt_node(node_name,p_id,ancestor_path)VALUE('node?',?,CONCAT((SELECT*FROM(SELECTancestor_pathFROMt_nodeWHEREnode_id=?)wt),?,','))
sql中的3个?均为要加入父节点的id。
2. 更新(含子节点)
如果更新的时候，父节点的位置没有变化,则不必考虑太多；
如果需要更新所在父节点，相比于最简单的树节点设计模式，增加祖先路径的方式除了在更新当前节点本身的父id外，还需要修改对应的祖先路径,这个步骤通过存储过程实现，是一种比较简单的方式，在此不再详述。仅对不使用存储过程的方式进行描述。
UPDATEt_nodeSETp_id=?_pWHEREnode_id=?_n;UPDATEt_nodeSETancestor_path=CONCAT((SELECT*FROM(SELECTancestor_pathFROMt_nodeWHEREnode_id=?_p)wt2),?_p,',',SUBSTR(ancestor_path,LENGTH(@PPath)+1))WHEREancestor_pathLIKECONCAT((SELECT*FROM(SELECT@ppath:=ancestor_pathFROMt_nodeWHEREnode_id=?_n)wt),?_n,',%')ORnode_id=?_n;
其中?_n表示要修改的节点的id，?_p表示要修改的节点的新父节点的id。
注：使用该sql一定要先更新子节点的祖先路径，再更新本节点的祖先路径，如果是使用存储过程的话就可以无视这一点了。
3. 删除(含子节点)
DELETEFROMt_nodeWHEREancestor_pathLIKECONCAT((SELECT*FROM(SELECTancestor_pathFROMt_nodeWHEREnode_id=?)wt),?,',%')
删除的核心在于where，和获取所有后代节点的where可以说是完全一样的。
同样要主要先删除所有后代节点，再删除本节点；
4 祖先路径的重置
有可能你此前的某个数据库表格没有使用过祖先路径，但已经积累了一定量的数据，或者之前使用了祖先路径，但由于某种原因导致祖先路径的一些数据更新错误。因为祖先路径本质上是一个冗余字段，所以还是可以通过父id的方式将之还原重置。
以下为机构表的一个重置存储过程,供以参考：
CREATEDEFINER=`root`@`localhost`PROCEDURE`p_reset_organ_path`(OUTresultMarkvarchar(50))BEGIN/*使用前的说明:1.本存储过程非客户使用,且自己人使用频率同样较低,故过程更方便调试,但效率不是很高;2.如果执行SELECT*FROMt_organWHEREorgan_id<parent_organ_id(即父机构产生于子机构之后)后的数据为空,则可以考虑使用分段模式(速度会快一些).3.如果2中所述数据不为空,使用分段会使该id对应的机构及其子机构的ancestor_path不正确.结果为partfail.*/DECLAREintACountINT(11)DEFAULT0;DECLAREintPCountINT(11)DEFAULT0;DECLAREintPIndexINT(11)DEFAULT0;DECLAREintPOrganIdINT(11)DEFAULT0;DECLAREstrPPathVARCHAR(100)DEFAULT'';DECLAREintLoopDoneINT(11)DEFAULT0;DECLAREintRCountINT(11)DEFAULT0;DECLAREintRIndexINT(11)DEFAULT0;DECLAREintROrganIdINT(11)DEFAULT0;DROPTABLEIFEXISTStmp_aOrganIdList;CREATETEMPORARYTABLEtmp_aOrganIdList(rowidINT(11)auto_incrementPRIMARYKEY,organ_idINT(11),p_organ_idINT(11));DROPTABLEIFEXISTStmp_pOrganIdList;CREATETEMPORARYTABLEtmp_pOrganIdList(rowidINT(11)auto_incrementPRIMARYKEY,organ_idINT(11));/**/DROPTABLEIFEXISTStmp_cOrganIdList;CREATETEMPORARYTABLEtmp_cOrganIdList(rowidINT(11)auto_incrementPRIMARYKEY,organ_idINT(11));DROPTABLEIFEXISTStmp_rOrganIdList;CREATETEMPORARYTABLEtmp_rOrganIdList(rowidINT(11)auto_incrementPRIMARYKEY,organ_idINT(11),p_organ_idINT(11),ancestor_pathVARCHAR(100));INSERTINTOtmp_aOrganIdList(organ_id,p_organ_id)(SELECTorgan_id,parent_organ_idFROMt_organ);--测试的时候limit:LIMIT0,100INSERTINTOtmp_pOrganIdList(organ_id)VALUES(0);INSERTINTOtmp_rOrganIdList(organ_id,p_organ_id,ancestor_path)VALUES(0,-1,'');WHILE((SELECTCOUNT(1)FROMtmp_aOrganIdList)>0ANDintLoopDone=0)DO--持续循环,当没有organId数据为止(如果中间机构中断,则可能陷入死循环)SELECTCOUNT(1)FROMtmp_pOrganIdListINTOintPCount;--当前父机构id的缓存区SETintPIndex=0;WHILEintPIndex<=intPCountDO--对每个当前查询到的父id进行对应操作SELECTorgan_idFROMtmp_pOrganIdListLIMITintPIndex,1INTOintPOrganId;SELECTancestor_pathFROMtmp_rOrganIdListWHEREorgan_id=intPOrganIdINTOstrPPath;INSERTINTOtmp_cOrganIdList(organ_id)(SELECTorgan_idFROMtmp_aOrganIdListWHEREp_organ_id=intPOrganId);--次级机构id的缓存区--SELECTCOUNT(1)FROMtmp_pOrganIdListINTOintDelCount;INSERTINTOtmp_rOrganIdList(organ_id,p_organ_id,ancestor_path)(SELECTorgan_id,intPOrganId,CONCAT(strPPath,intPOrganId,',')FROMtmp_aOrganIdListWHEREp_organ_id=intPOrganId);DELETEFROMtmp_aOrganIdListWHEREp_organ_id=intPOrganId;SETintPIndex=intPIndex+1;ENDWHILE;DELETEFROMtmp_pOrganIdList;IF(SELECTCOUNT(1)FROMtmp_cOrganIdList)>0THENINSERTINTOtmp_pOrganIdList(organ_id)(SELECTorgan_idFROMtmp_cOrganIdList);DELETEFROMtmp_cOrganIdList;ELSESETintLoopDone=1;ENDIF;--SELECT*FROMtmp_pOrganIdList;--SELECTCOUNT(1)FROMtmp_aOrganIdList;--SELECTintLoopDone;ENDWHILE;--SELECT*FROMtmp_rOrganIdList;--想要查看测试的结果,请看此表SELECTCOUNT(1)FROMtmp_rOrganIdListINTOintRCount;WHILEintRIndex<=intRCountDOSELECTorgan_id,ancestor_pathFROMtmp_rOrganIdListLIMITintRIndex,1INTOintROrganId,strPPath;UPDATEt_organSETancestor_path=strPPathWHEREorgan_id=intROrganId;SETintRIndex=intRIndex+1;ENDWHILE;IF(SELECTCOUNT(1)FROMtmp_aOrganIdList)=0THENSETresultMark='perfect';ELSESETresultMark='partfail';ENDIF;END
上述内容就是MySQL中怎么实现树状数据，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
