CN112860692B - 一种数据库表结构转换方法、装置及其电子设备 - Google Patents

Abstract

本发明公开了一种数据库表结构转换方法、装置及其电子设备，首先对数据库表进行分页查询得到结果集，结果集包含数据信息以及一个空的子节点列表；然后对结果集进行初次循环遍历，得到所有节点归集为节点集合，并得到父节点列表；接着对结果集进行再次循环遍历，再次得到每个节点的父节点，并将节点添加至父节点的子节点列表中；最后将父节点添加至父节点列表。本发明避免了递归查询，大大提高了查询效率。

Description

一种数据库表结构转换方法、装置及其电子设备

技术领域

本发明涉及数据库构建方法领域，具体是一种数据库表结构转换方法、装置及其电子设备。

背景技术

计算机程序设计过程中，常常需要采用树型结构来表征某些数据的关联关系，如企业上下级部门、菜单结构、商品分类等等，通常而言，这些树型结构需要借助数据库完成持久化。然而目前的各种数据库，都是以二维表的形式记录存储数据信息，因此是不能直接将树型结构的数据直接存入数据库的，需要设计合适的数据库表结构及其对应的增、删、改、查方法来实现数据库中存储树型结构。

目前，树型结构的数据库表结构的优秀设计有很多，但这些数据库表遇到数据库的条目超过10万条的情况时，需要通过分页并采用递归的方式进行查询，并且在分页查询的结果集中，需要标明每个节点的选中状态(半选、全选、未选中)，这种情况下如果用递归的方式进行查询，会造成计算机资源不足的问题。

发明内容

本发明的目的是提供一种数据库表结构转换方法、装置及其电子设备，以解决现有技术数据库中通过树型结构数据库表实现树型结构数据时需要采用递归方式分页查询的问题。

为了达到上述目的，本发明所采用的技术方案为：

一种数据库表结构转换方法，过程如下：

获取关系型数据库中数据库表的所有节点，在所述数据库表中对所述节点进行排序；对排序后的所述数据库表中的所述节点进行分页查询获得结果集，所述结果集被配置为数组形式，所述结果集包括多个节点对象，所述节点对象至少包括每个所述节点的节点ID、父节点ID、节点名称的存储路径、节点ID的存储路径以及空的子节点列表；

对结果集进行初次循环遍历，得到所有节点以及每个节点的parent_id值，并将遍历得到的所有节点归集为节点集合，同时将遍历得到的各个节点的parent_id值归集为父节点列表；

对结果集进行再次循环遍历，再次得到每个节点的parent_id值，并基于再次得到的所有parent_id值在节点集合中进行查询，得到每个parent_id值对应的节点的父节点，并将parent_id值对应的节点添加至查询得到的父节点的子节点列表中；

将得到的所有父节点添加至父节点列表，由此得到树型结构数据。

所述的一种数据库表结构转换方法，通过关系型数据库程序构建数据库表，并通过关系型数据库程序进行查询得到结果集。

所述的一种数据库表结构转换方法，通过计算机编程语言实现节点的循环遍历。

所述的一种数据库表结构转换方法，数据库表关联关系型数据库中节点选中状态列表，所述节点选中状态列表记录所有节点的选中状态信息，所述结果集的数组还包含每个节点的选中状态信息。

所述的一种数据库表结构转换方法，基于所述节点中节点名称的存储路径在所述数据库表中对所述节点进行排序。

所述的一种数据库表结构转换方法，对所述节点进行排序时按节点名称的存储路径由小到大进行。

所述的一种数据库表结构转换方法，若基于再次得到的parent_id值在节点集合中进行查询时无法得到查询结果，则判断该parent_id值对应的节点是否在结果集中对应页的首部，若不在首部则该parent_id值对应的节点为脏数据，若在首部则以该parent_id值对应的节点所在页的上一页最后一个节点，作为该parent_id值对应的节点的父节点。

一种数据库表结构转换装置，包括：

第一获取单元，用于获取关系型数据库中数据库表的所有节点；

排序单元，用于在所述数据库表中对所述节点进行排序；

查询单元，用于对排序后的所述数据库表中的所述节点进行分页查询获得结果集，所述结果集被配置为数组形式，所述结果集包括多个节点对象，所述节点对象至少包括每个所述节点的节点ID、父节点ID、节点名称的存储路径、节点ID的存储路径以及空的子节点列表；

第一遍历单元，用于对结果集进行初次循环遍历，得到所有节点以及每个节点的parent_id值，并将遍历得到的所有节点归集为节点集合，同时将遍历得到的各个节点的parent_id值归集为父节点列表；

第二遍历单元，用于对结果集进行再次循环遍历，再次得到每个节点的parent_id值，并基于再次得到的所有parent_id值在节点集合中进行查询，得到每个parent_id值对应的节点的父节点，并将parent_id值对应的节点添加至查询得到的父节点的子节点列表中；

第一添加单元，用于将得到的所有父节点添加至父节点列表，由此得到树型结构数据。

一种电子设备，包括存储器、处理器以及存储在所述存储器上并可在所述处理器上运行的数据库表结构转换程序，所述数据库表结构转换被所述处理器执行时实现数据库表结构转换方法。

一种可读存储介质，可读存储介质上存储有数据库表结构转换程序，所述数据库表结构转换程序被处理器执行时实现所述的数据库表结构转换方法。

本发明采用的设计思路是将查询过程分2个步骤，先由关系型数据库只执行一次查询获得出符合条件的数据。再由计算机编程语言对数据进行加工处理，整理成树并计算各节点选中状态。

本发明中对数据库表结构进行了设计，理想中的树型结构应具备如下特征：数据存储冗余度小、直观性强；检索遍历过程简单高效；节点增、删、改、查操作高效。

本发明采用的继承关系驱动的数据库表结构中，设计2个字段{node_id，parent_id}，通过这2个字段直接记录了节点之间的继承关系，但此时该表的任何增、删、改、查都是低效的，这主要归根于频繁的递归操作，递归的过程中不断地访问数据库，每次数据库IO都会有时间开销。

因此，本发明在此数据库表新增加2个用于索引的字段{full_path，full_id_path}，full_path存储了节点名称的全路径，full_id_path则用于存储node_id组成的全路径。

该数据库表需要选中状态表，得到每个节点的选中状态(此时得到的节点状态只有选中和未选中2种状态，半选、全选状态需要由代码进行计算得出)。

本发明还对查询出的结果进行处理，通过循环遍历的方式将列表类型的查询结果组装成树状结构，并能剔除脏数据、计算节点选中状态。

本发明的数据库表中新增full_path，在查询时按full_path进行由小到大的排序后进行分页获取。通过这种方式来分页获取的列表数据有一个很重要的规律：“如果有2个平级的节点A和节点B。那么在列表中节点A和节点A下的所有子节点都会排在节点B之前。”这个规律为后续的将列表转化成树型结构工作和进行节点选中状态计算工作提供了极大的便利。

此外，因为full_path的直观性强，符合人的思维逻辑，也易于进行脏数据的排查清理工作和进一步的功能拓展工作。

字段full_id_path的引入是用于系统来计算该节点的选中状态，它就像一个索引字段，预先的把树型结构的信息整理好存储了下来，可以通过关系型数据库中的“LIKE”操作符，高效查询到该节点下所有子节点，避免进行递归查询，大大提高了查询效率。

由此，本发明方法中数据查询由关系型数据库程序完成、数据处理由另外的计算机编程语言完成。这种分步处理的方式，充分发挥了各自系统的优势，降低了代码复杂度、提升效率的同时还更易维护。

本发明能够在数据库中构建树型结构数据，并能够满足降低对计算机性能的要求，查询响应时间控制在300毫秒以内。本发明中，当数据条目超过10万条、需要进行分页查询时，分页的大小可以不受限制(如5000条一页)。并且本发明分页查询的结果集中，能够计算并标明树中各个节点的选中状态(半选、全选、未选中)。

与现有技术相比，本发明的优点为：

1)避免了递归查询，大大提高了查询效率。

2)每次返回的条目数量固定。

3)对于脏数据的适应和排查。

4)响应速度快，小于300毫秒。

附图说明

图1是本发明实施例方法流程图。

图2是本发明实施例数据库表结构转换装置结构框图。

图3是本发明实施例电子设备结构框图。

图4是本发明实施例树型结构示意图。

图5是本发明实施例中计算特殊节点流程框图。

图6是本发明实施例中计算节点选中状态流程框图。

具体实施方式

下面结合附图和实施例对本发明进一步说明。

如图1所示，本发明包括以下步骤：

(1)、获取关系型数据库中数据库表的所有节点，在所述数据库表中对所述节点进行排序；对排序后的所述数据库表中的所述节点进行分页查询获得结果集，所述结果集被配置为数组形式，所述结果集包括多个节点对象，所述节点对象至少包括每个所述节点的节点ID、父节点ID、节点名称的存储路径、节点ID的存储路径以及空的子节点列表；

(2)、对结果集进行初次循环遍历，得到所有节点以及每个节点的parent_id值，并将遍历得到的所有节点归集为节点集合，同时将遍历得到的各个节点的parent_id值归集为父节点列表；

(3)、对结果集进行再次循环遍历，再次得到每个节点的parent_id值，并基于再次得到的所有parent_id值在节点集合中进行查询，得到每个parent_id值对应的节点的父节点，并将parent_id值对应的节点添加至查询得到的父节点的子节点列表中；

(4)、将步骤(3)得到的所有父节点添加至步骤(2)得到的父节点列表，由此得到树型结构数据。

在一个实施例中，获取关系型数据库中数据库表的所有节点之前：

对关系型数据库中的数据库表建立继承关系。

当关系型数据库中不存在数据库表时，在关系型数据库中建立继承关系驱动的数据库表。

对数据库表建立继承关系或在关系型数据库中建立继承关系驱动的数据库表后，以关系型数据库中的数据作为节点，所述数据库表中记录有所有节点，并在数据库表中构建字段node_id、字段parent_id、字段full_path、字段full_id_path用于记录数据信息，其中字段node_id记录节点id，字段parent_id记录节点的父节点id，字段full_path记录节点名称的全路径，字段full_id_path记录节点id的全路径。

如图2所示，本发明还提供了一种数据库表结构转换装置，包括：

第一获取单元，用于获取关系型数据库中数据库表的所有节点；

排序单元，用在所述数据库表中对所述节点进行排序；

查询单元，用于对排序后的所述数据库表中的所述节点进行分页查询获得结果集，所述结果集被配置为数组形式，所述结果集包括多个节点对象，所述节点对象至少包括每个所述节点的节点ID、父节点ID、节点名称的存储路径、节点ID的存储路径以及空的子节点列表；

第一遍历单元，用于对结果集进行初次循环遍历，得到所有节点以及每个节点的parent_id值，并将遍历得到的所有节点归集为节点集合，同时将遍历得到的各个节点的parent_id值归集为父节点列表；

第二遍历单元，用于对结果集进行再次循环遍历，再次得到每个节点的parent_id值，并基于再次得到的所有parent_id值在节点集合中进行查询，得到每个parent_id值对应的节点的父节点，并将parent_id值对应的节点添加至查询得到的父节点的子节点列表中；

第一添加单元，用于将得到的所有父节点添加至父节点列表，由此得到树型结构数据。

如图3所示，本发明还提供了一种电子设备，包括存储器、处理器以及存储在所述存储器上并可在所述处理器上运行的数据库表结构转换程序，所述数据库表结构转换被所述处理器执行时实现数据库表结构转换方法。

本发明还提供了一种可读存储介质，可读存储介质上存储有数据库表结构转换程序，所述数据库表结构转换程序被处理器执行时实现所述的数据库表结构转换方法。

实施例：

本实施例以Mysql数据库和计算机编程语言Java为例，对本发明方法中步骤(2)-步骤(5)进行进一步说明。

第一步、数据库查询：

在Mysql数据库中使用SQL进行操作，对full_path进行排序，并用“LIMIT”操作符进行分页查询，程序如下：

SELECT t.node_id AS nodeId,

t.node_name AS nodeName,

t.parent_id, AS parentId

t.full_path AS fullPath,

t.full_id_path AS fullIdPath,

IF(rel.rel_id IS NULL,0,1)AS garnted//是否选中FROM tree_tablet

LEFT JOIN relation_table rel ON t.node_id＝rel.node_id ANDrel.project_id＝1

ORDER BY t.full_path,t.node_idLIMIT 1，6//分页

查询出的结果集在程序中将会被处理包装成一个数组，每个节点包含nodeId、nodeName、parentId、fullPath、fullIdPath、granted、selected数据信息以及一个空的子节点列表subNodeList。

第二步、通过程序处理成树：

利用java.util.ArrayList和java.util.HashMap这个2个集合,可以在2次循环内完成树型结构的处理。

将第一步中获取的结果集命名为rawList。

第一遍对rawList的循环：创建一个新ArrayList命名为topList作为父节点列表，并创建一个HashMap集合命名为map作为节点集合。在循环遍历rawList的过程中，将rawList中的每一个节点都添加进map，节点的nodeI_d作为key，节点对象作为value；同时判断节点的parent_Id的值，将parent_Id的值为null的节点对象添加进topList。当第一遍循环结束后，得到了一个map集合和一个树的最顶层节点的父节点列表topList。

第二遍对rawList的循环是开始建立节点和节点之间的联系：遍历rawList中的节点，取出节点的parent_Id的值作为key在map中进行查询，查询到的节点就是该parent_Id值对应的节点的父节点，并将所有的父节点添加至父节点列表topList。

如果查询到父节点，就将该parent_Id值对应的节点添加入父节点的子节点列表subNodeList中。

如果未能查询到，两个原因会符合这个情况，一是该节点的parent_Id是脏数据；二是分页查询导致的。该parent_Id值对应的节点的父节点在上一页，因此将上一页中最后一个节点作为该parent_Id值对应的节点的父节点添加进父节点列表topList。

两遍循环结束后即在数据库中得到树型结构数据。topList就是最终得处理结果，将其传输给前端进行展示。

第三步、计算特殊节点选中状态：

在执行计算选中状态的方法前，由于分页的关系，有几个节点的选中状态需要先进行计算。以图4所示树型结构为例，当在第2页时，需要依次计算节点(12)(7)的选中状态。过程如下：

取出rawList的最后一个元素，即是节点(12)，然开始计算节点的状态如图5所示。

数据库查询节点(12)的选中状态，SQL如下：

SELECT COUNT(node.node_id)AS total,COUNT(rel.rel_id)AS selectedCountFROM tree_table node

LEFT JOIN relation_table rel ON node.node_id＝rel.node_id ANDrel.project_id＝1

WHERE node.project_id IS NULL

AND CONCAT(node.full_id_path,'/')LIKE'"+${节点(12)的fullIdPath}+"/％'"；

返回的结果中有2个值toltal和grantedCount，total代表节点(12)下所有子节点的个数，grantedCount代码节点(12)下所有被选中的子节点的个数。当total大于grantedCount时，节点(12)设为“半选”；当total等于grantedCount时，设为“全选”。其余情况设为“未选中”。

通过节点(12)的parentId从map中获取节点(7),此时如果当节点(12)的选中状态是“半选”,节点(7)必然是“半选”；除此情况外，依旧是查询数据库进行查询、计算。

继续重复此逻辑，获取节点(7)的parentId，尝试从map中获取节点(7)的父节点：节点(6)，但此时是获取不到的，因为节点(6)的数据在上一页。所以特殊节点的处理结束。

第四步、计算节点选中状态：

完成这些个特殊节点的处理，就可以开始对剩余节点的选中状态进行计算，处理逻辑如见图6所示：

1)从rawList中随机获取一个节点。

2)判断该节点的subNodeList是否为空。为空时代表该节点没有子节点。可以直接通过节点的granted值来计算出状态，只有“全选”和“未选中”2种状态。

3)如subNodeList不为空。就遍历所有子节点。

i如果存在子节点的状态是“未处理”，说明该节点现在还不能满足进行计算选中状态的条件。将该节点返还给rawList，不对该节点做任何修改。

ii每检查完一个子节点，就将这个子节点的选中状态记录下来。

iii当子节点全部遍历完成后，检查所有子节点的选中状态，如果子节点都为“全选”则该节点的选中状态为“全选”；如果子节点的选中状态都为“未选中”则该节点的选中状态为“未选中”；其余情况该节点的选中状态为“半选”。

4)该节点状态计算完成后，将将该节点移出rawList。

5)不断循环以上1-4步，直至rawList中的所有节点都被移出。

至此，所有节点的选择状态都已计算完毕。topList就是我们最终需要的树。

本实施例还可以通过数据库中SQL高效地更新full_path。第一步先执行如下SQL1更新full_path,此时如果树型结构的层级较多,会出现类似{parent_id}/node_name/node_name/node_name/node_name的情况。

SQL1

UPDATE tree_table l4

LEFT JOIN tree_table l3 ON l4.parent_id＝l3.node_id

LEFT JOIN tree_table l2 ON l3.parent_id＝l2.node_id

LEFT JOIN tree_table l1 ON l2.parent_id＝l1.node_id SET l4.full_path＝CONCAT_WS('/',CONCAT('{',l1.parent_id,'}'),l1.node_name,l2.node_name,l3.node_name,l4.node_name)；

第二步执行如下SQL2完成full_path的补充，如果结果集中仍带有{parent_id}/node_name/node_name/node_name/node_name的情况，就再次执行一遍SQL2，直至所有的full_path都更新完成。

SQL2：

UPDATE tree_table l4

LEFT JOIN tree_table l3 ON substring_index(substring_index(l4.full_path,'}/',1),'{',-1)＝l3.node_id

LEFT JOIN tree_table l2 ON l3.parent_id＝l2.node_id

LEFT JOIN tree_table l1 ON l2.parent_id＝l1.node_id SET l4.full_path＝CONCAT_WS('/',CONCAT('{',l1.parent_id,'}'),l1.node_name,l2.node_name,l3.node_name,substring_index(l4.full_path,'}/',-1))

WHERE l4.full_path like'{％'；

在对node_id和parent_id建立起索引的前提下，该SQL执行效率非常高。

本发明所述的实施例仅仅是对本发明的优选实施方式进行的描述，并非对本发明构思和范围进行限定，在不脱离本发明设计思想的前提下，本领域中工程技术人员对本发明的技术方案作出的各种变型和改进，均应落入本发明的保护范围，本发明请求保护的技术内容，已经全部记载在权利要求书中。

Claims (8)

1.一种数据库表结构转换方法，其特征在于，过程如下：

获取关系型数据库中数据库表的所有节点，并在数据库表中构建字段node_id、字段parent_id、字段full_path、字段full_id_path用于记录数据信息，其中字段node_id记录节点id，字段parent_id记录节点的父节点id，字段full_path记录节点名称的全路径，字段full_id_path记录节点id的全路径；

在所述数据库表中，基于所述节点中节点名称的存储路径即字段full_path对应的全路径，由小到大对所述节点进行排序；

对排序后的所述数据库表中的所述节点进行分页查询获得结果集，分页查询时基于字段full_id_path计算每个节点的选中状态，并通过关系型数据库中的操作符基于选中状态计算结果查询每个节点下所有子节点；所述结果集被配置为数组形式，所述结果集包括多个节点对象，所述节点对象至少包括每个所述节点的节点ID、父节点ID、节点名称的存储路径、节点ID的存储路径以及空的子节点列表；

对结果集进行初次循环遍历，得到所有节点以及每个节点的parent_id值，并将遍历得到的所有节点归集为节点集合，同时将遍历得到的各个节点的parent_id值归集为父节点列表；

对结果集进行再次循环遍历，再次得到每个节点的parent_id值，并基于再次得到的所有parent_id值在节点集合中进行查询，得到每个parent_id值对应的节点的父节点，并将parent_id值对应的节点添加至查询得到的父节点的子节点列表中；

将得到的所有父节点添加至父节点列表，由此得到树型结构数据。

2.根据权利要求1所述的一种数据库表结构转换方法，其特征在于，通过关系型数据库程序构建数据库表，并通过关系型数据库程序进行查询得到结果集。

3.根据权利要求1所述的一种数据库表结构转换方法，其特征在于，通过计算机编程语言实现节点的循环遍历。

4.根据权利要求1所述的一种数据库表结构转换方法，其特征在于，数据库表关联关系型数据库中节点选中状态列表，所述节点选中状态列表记录所有节点的选中状态信息，所述结果集的数组还包含每个节点的选中状态信息。

5.根据权利要求1所述的一种数据库表结构转换方法，其特征在于，若基于再次得到的parent_id值在节点集合中进行查询时无法得到查询结果，则判断该parent_id值对应的节点是否在结果集中对应页的首部，若不在首部则该parent_id值对应的节点为脏数据，若在首部则以该parent_id值对应的节点所在页的上一页最后一个节点，作为该parent_id值对应的节点的父节点。

6.一种数据库表结构转换装置，其特征在于，所述装置包括：

第一获取单元，用于获取关系型数据库中数据库表的所有节点；

排序单元，用于在所述数据库表中对所述节点进行排序；

查询单元，用于对排序后的所述数据库表中的所述节点进行分页查询获得结果集，所述结果集被配置为数组形式，所述结果集包括多个节点对象，所述节点对象至少包括每个所述节点的节点ID、父节点ID、节点名称的存储路径、节点ID的存储路径以及空的子节点列表；

第一遍历单元，用于对结果集进行初次循环遍历，得到所有节点以及每个节点的parent_id值，并将遍历得到的所有节点归集为节点集合，同时将遍历得到的各个节点的parent_id值归集为父节点列表；

第二遍历单元，用于对结果集进行再次循环遍历，再次得到每个节点的parent_id值，并基于再次得到的所有parent_id值在节点集合中进行查询，得到每个parent_id值对应的节点的父节点，并将parent_id值对应的节点添加至查询得到的父节点的子节点列表中；

第一添加单元，用于将得到的所有父节点添加至父节点列表，由此得到树型结构数据。

7.一种电子设备，其特征在于，所述电子设备包括存储器、处理器以及存储在所述存储器上并可在所述处理器上运行的数据库表结构转换程序，所述数据库表结构转换被所述处理器执行时实现如权利要求1-5任一项所述的数据库表结构转换方法。

8.一种可读存储介质，其特征在于，所述可读存储介质上存储有数据库表结构转换程序，所述数据库表结构转换程序被处理器执行时实现如权利要求1-5任一项所述的数据库表结构转换方法。