0树型数据的分类我们在设计mysql数据库的时候，会遇到一种树型数据。比如公司下分几个部门，每个部门下分几个部门，形成树状数据。关于树状数据，按照层数大致可以分为以下两类：department-department,province-city-districtEqualvariablenumberoflevels层数不固定，前几层可能有特殊含义，但整体在相当大的范围内浮动。前者的好处是，由于每一层都有自己的含义，数据库的整体设计更方便，可以将某个子节点的不同父节点存储在数据库中。同样以某组为例：节点代码节点名称节点层级父节点代码级别1祖先代码级别2祖先cdoe010000company11000000nullnull020000Company21000000nullnull010300Manufacturing2010000010000null010400Quality2010000010000null010301前工程制造3010300010000010300010303装配制造3010300010000010300这样设计的表冗余度更高，但在各类查询中效率更高。插入、更新时（包括子组织，由于业务逻辑特性，组织间更新一般为并行传输）、删除时（包括子组织），由于冗余信息量大，数据操作所需的查询为也比较简单。根据情况，也可以考虑删除一些冗余信息，比如对于节点代码，删除一些设计必然会导致一些查询的效率或复杂度的增加，需要根据实际情况进行取舍和权衡.缺点有两个：一是当层数较多时，需要存储大量的冗余信息。当然，也可以考虑一个节省方案：1）不存储n级祖先编码等字段，但是这样就不能使用固定级别设计带来的高效查询特性，不推荐；2）n级存储使用id而不是code，主要是数据迁移或者其他表使用时不方便。还有一个缺点就是，当需求方提出重新洗牌、改变层级的要求时，你会很头疼。后者的优缺点与前者正好相反。非固定级别的限制非常灵活，缺点是查询和数据操作不便。这也是本文的重点，即如何设计一个非固定层级的树数据。1非固定层次树数据的设计方法--ancestorpath树数据最简单的设计方法是只增加parentid。但是这种设计方式给后代节点的查询带来了很大的不便。据我所知，没有不通过函数/存储过程循环获取一个节点的所有后代节点或者是祖先节点的查询方法。（之前找了一个比较复杂的后代节点的sql查询，也是利用了祖先节点的id大于后代节点id的特性，但是可以更新节点，使得后代的idnode大于祖先节点的id，所以不严谨，这个就不详细说了）非固定层次树数据的一种设计方法是：增加祖先路径（ancestor_path），详见下表：节点名称|家长编号|祖先路径---|---|---|---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，在实际设计中，也可以考虑加入level这个冗余字段，但是我在实际使用过程中很少用到这个字段。这样，加上这个字段后，就可以通过这么一条数据，得到任意一个节点的所有祖先节点信息。祖先路径的设置有以下特点：对于没有父节点的根节点，父id默认为'0'，祖先路径默认为'0'；每次添加子节点时，祖先路径为要添加的子节点，在父节点的祖先路径中添加parentid和'??,'；引用表结构如下：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是pairid？查询某个节点的所有后代节点的方法一，也可以使用如下方法：SELECT*FROMt_nodeWHEREancestor_pathLIKECONCAT('%,',?,',%')查询方法二更简洁。但是考虑到查询方式只使用了右模糊查询，可以使用索引，所以推荐使用方式一进行查询。需要注意的是，以上两种方法找到的节点集不包含子节点。如果需要包含该节点的信息，还需要加上...ORnode_id=?2.查询一个节点的所有祖先节点SELECT*FROMt_nodeWHEREnode_idREGEXPCONCAT('^(',REPLACE((SELECT*FROM(SELECTancestor_pathFROMt_nodeWHEREnode_id=?)wt),',','|'),'0)$')的效率上述方法中查询祖先节点确实要求不高，但是考虑到查询本身用不到，所以暂时使用。3祖先路径的插入、更新和删除分别分为插入、更新和删除：1.InsertINSERTINTOt_node(node_name,p_id,ancestor_path)VALUE('node?',?,CONCAT((SELECT*FROM(SELECTancestor_pathFROMt_nodeWHEREnode_id=?)wt),?,','))三个？在sql中是要添加到父节点的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_path(LIKECONCAT(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
