CN108681603B - Method for rapidly searching tree structure data in database and storage medium - Google Patents
Method for rapidly searching tree structure data in database and storage medium Download PDFInfo
- Publication number
- CN108681603B CN108681603B CN201810494453.2A CN201810494453A CN108681603B CN 108681603 B CN108681603 B CN 108681603B CN 201810494453 A CN201810494453 A CN 201810494453A CN 108681603 B CN108681603 B CN 108681603B
- Authority
- CN
- China
- Prior art keywords
- node
- tree structure
- path
- nodes
- searching
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a method and a storage medium for rapidly searching tree structure data in a database, wherein the method comprises the following steps: storing the node path of each node corresponding to the tree structure to a database table; receiving a request for searching all nodes containing a node field under a node in the tree structure; and searching and acquiring the node containing the node field and the corresponding node path containing the node from the database table. The invention can realize quick search and obtain accurate results, and greatly reduce the operation complexity; the operation is only carried out based on the database table, no extra memory overhead exists, and the space complexity is obviously reduced; furthermore, support can be realized only by newly adding a field in the database table, and the method is simple and convenient to operate and high in practicability. The method is particularly suitable for fast and accurate search of large-data-volume tree structure data, so that the method has a wide application prospect and further improves the practicability of the method.
Description
Technical Field
The invention relates to the field of data retrieval, in particular to a method and a storage medium for rapidly searching tree structure data in a database.
Background
In application interaction of a tree structure, a function of fuzzy matching of the tree structure is ubiquitous. For example, the department organization structure of a company is tree-shaped from top to bottom, and the larger the company size is, the more hierarchical levels of the structure are, so that the user is allowed to input keywords to perform fuzzy search in the tree-shaped structure.
The traditional fuzzy search technology is a tree-based traversal algorithm, such as breadth-first or depth-first algorithm, but the application of the traditional fuzzy search technology has the following disadvantages:
1. in terms of spatial complexity, traversing the tree requires storing data in a tree structure in memory, which results in additional memory cost for modern applications because data of modern applications are stored in a database in a table structure. In the case of a large amount of data, such a cost can be said to be very high.
2. In time complexity, the time complexity of tree traversal is O (v + e), where v represents the number of nodes and e represents the number of connections of the nodes. For example, with the tree structure of FIG. 1, the temporal complexity of traversing this tree is O (5+ 4).
Therefore, there is a need for providing a brand new tree structure-based efficient search method that overcomes the above-mentioned disadvantages, and a computer-readable storage medium storing a program that implements the method.
Disclosure of Invention
The technical problem to be solved by the invention is as follows: a brand new method and storage medium for rapidly searching tree structure data in a database are provided, and complexity of search implementation in space and time can be reduced simultaneously.
In order to solve the technical problems, the invention adopts the technical scheme that:
the method for rapidly searching the tree structure data in the database comprises the following steps:
storing the node path of each node corresponding to the tree structure to a database table;
receiving a request for searching all nodes containing a node field under a node in the tree structure;
and searching and acquiring the node containing the node field and the corresponding node path containing the node from the database table.
The invention provides another technical scheme as follows:
a computer-readable storage medium, on which a computer program is stored, which, when invoked by a processor, is able to carry out the steps involved in the method for fast searching tree-structured data in a database as described above.
The invention has the beneficial effects that: the invention is different from the defects of higher space complexity and space complexity of the existing searching mode, and the node path storage is added corresponding to each node in the database table, so that when the fuzzy search corresponding to a target node is received, all the nodes of the structure tree are traversed based on the database table, whether each node contains a keyword or not is judged, and simultaneously whether the node path corresponding to each node contains the target node corresponding to the search request or not is judged, and the target node and the nodes meeting the search requirement in all the sub-nodes below the target node can be quickly and accurately obtained. The method is directly based on the table structure in the database to carry out retrieval and matching, and no extra memory cost is needed; meanwhile, in time complexity, the method is only limited by the number of nodes without considering the number of the nodes, and the searching efficiency can be obviously improved. For the development trend of larger and larger data volume at present, the method has obvious advantages in space and time, and therefore has wide application prospect.
Drawings
FIG. 1 is a schematic diagram of an organizational structure of a company;
FIG. 2 is a flowchart illustrating a method for rapidly searching tree-structured data in a database according to the present invention;
FIG. 3 is a schematic flow chart illustrating a process of obtaining a node path corresponding to each node according to the present invention;
FIG. 4 is a schematic diagram of the process of searching and matching based on database tables according to the present invention.
Detailed Description
In order to explain technical contents, achieved objects, and effects of the present invention in detail, the following description is made with reference to the accompanying drawings in combination with the embodiments.
The most key concept of the invention is as follows: in the database table, the storage of the node paths of the nodes is increased corresponding to each node, and when fuzzy search is carried out, the search result can be quickly and accurately obtained only by traversing all the nodes of the structure tree based on the database table and simultaneously judging whether each node contains a keyword and whether the corresponding node path contains a target node corresponding to the search request.
Referring to fig. 2, the present invention provides a method for fast searching tree structure data in a database, including:
storing the node path of each node corresponding to the tree structure to a database table;
receiving a request for searching all nodes containing a node field under a node in the tree structure;
and searching and acquiring the node containing the node field and the corresponding node path containing the node from the database table.
From the above description, the beneficial effects of the present invention are: the rapid search algorithm is provided, so that the time complexity can be reduced to O (v), and more efficient search is realized; the searching efficiency is far higher than that of the traditional algorithm, and no extra memory overhead (direct database query operation) exists.
Further, referring to fig. 3, the method further includes:
traversing each node in the database table;
if the currently traversed node is the root node, acquiring a node path only containing the current node, and associating the node path with the current node;
and if the currently traversed nodes are other nodes, acquiring a node path containing all father nodes and the current node, and associating the node path with the current node.
As can be seen from the above description, it only needs to spend the time of traversing each node, and does not need to consider the number of connections of each node; the result can be quickly and accurately obtained only by judging two parameters in the table based on the database table aiming at each node, and the searching process is simple and convenient and has high efficiency.
Further, referring to fig. 4, the obtaining of the node path including all the father nodes and the current node specifically includes:
acquiring all father nodes of a tree structure corresponding to a current node;
and sequencing all father nodes and the current node according to the sequence from father to son to obtain a node path.
According to the description, the node paths are stored corresponding to the hierarchical sequence of the tree structure, so that the method is more intuitive, is not easy to make mistakes, and is beneficial to further improving the judgment efficiency.
Further, the node path is composed of node IDs and separators, and the node IDs are separated by the separators.
As can be seen from the above description, the node path uses the node ID to represent the corresponding node, and the node IDs are separated by the separator, so that the node path is more clear and easy to read and understand.
Further, the searching and acquiring the node containing the node field from the database table and the corresponding node path containing the node specifically includes:
acquiring a node ID corresponding to the node in the request and the node field;
traversing all nodes in the tree structure, and judging whether the node name corresponding to the currently traversed node contains the node field or not and whether the node path corresponding to the currently traversed node contains the node ID or not; if so, acquiring a node name corresponding to the current node, and continuously traversing the next node;
and outputting the acquired node name as a search result.
It can be known from the above description that matching is directly performed in a database table based on simple characters such as node IDs and node fields, which can significantly improve the efficiency of searching and ensure the accuracy of searching at the same time.
Further, a field is added in the database table to store the node path corresponding to each node.
According to the description, the premise of realizing the method is that only one field is newly added corresponding to each node in the database table, so that the method does not generate extra memory cost, does not occupy too much database resources, and has strong practicability.
The invention provides another technical scheme as follows:
a computer-readable storage medium, on which a computer program is stored, which, when invoked by a processor, is able to carry out the steps involved in the method for fast searching tree-structured data in a database as described above.
From the above description, the beneficial effects of the present invention are: it should be understood by those skilled in the art that all or part of the processes in the above technical solutions may be implemented by instructing the related hardware through a computer program, where the program may be stored in a computer-readable storage medium, and when executed, the program may include the processes of the above methods.
Example one
Referring to fig. 1 to 4, the present embodiment provides a method for rapidly searching tree structure data in a database.
Firstly, adding a field in a database table for storing tree structure data, wherein the field is used for storing node paths of each node in the tree structure.
The tree-structured data is stored in the database instead of the memory in a table structure by default. Here, a table structure corresponding to one tree structure is simply referred to as a database table. The storage structure of the database table is shown in fig. 1, and includes field structures of ID (node ID), name (node name), parent (parent ID), and added field path (node path).
The node path corresponds to all tree nodes which are required to pass through from the root node to the current node in the tree structure, and the tree nodes comprise the current node and all father nodes thereof. For example, in the tree structure of fig. 1, the node route of the node "research and development 2" corresponds to the node "technology 2" that is a subordinate node that can be reached by passing through the node "technology" from the node "education and technology limited company". Since the corresponding nodes are generally represented by the node ids in the database table, the nodes in the preferred node path are also represented by the corresponding node ids, and the node path corresponding to the node "part 2 of research and development" is "-1-2-4-". Preferably, in order to make the expression of the node path more clear and understandable, the node ids are separated by separators, such as "-" or "/" or other separators.
Specifically, as shown in fig. 3, the manner of obtaining the node path corresponding to each node newly added in the database table may be implemented by the following steps:
(1) constructing a database table corresponding to a tree structure and in a conventional format;
(2) after the database table with the conventional format is constructed, adding a 'path' field in the table for storing the respective node path corresponding to each node;
(3) traversing all data in the database table (corresponding to all tree nodes of the tree structure); if the currently traversed node is the root node, executing (4); if the currently traversed node is other nodes (other nodes except the root node in the tree structure), executing (5);
(4) writing own id into a ' path ' field corresponding to the node traversed currently in the database table, and separating by using a separator, such as ' -1-;
(5) acquiring all parent node ids of the node corresponding to the tree structure traversed currently; i.e. all parent node ids this node has come to the root node; all the obtained parent node ids including the node's own id are then sorted according to the parent-to-child order, i.e., the hierarchical order from the root node to the node, and the node ids are separated by separators and then stored in a "path" field, e.g., -1-2-3-4- ".
Then, when the system receives a request for searching the tree structure to obtain all nodes containing a field of a designated node under the designated node. Specifically, the request corresponds to fuzzy search based on tree structure data, which is used to search all nodes containing a certain keyword in sub-nodes under a certain node (preferably including the node itself).
And searching and acquiring all nodes which contain the appointed node fields and contain the appointed nodes in the corresponding node paths from a database table corresponding to the tree structure according to the request.
That is, when searching for a match, it is necessary to match the "path" field of the node in addition to the node name field of each node. As shown in fig. 4, a specific matching process is as follows:
(1) acquiring a node id corresponding to the specified node in the request, namely an initial father node id needing to be searched, and a specified node field, namely a keyword corresponding to a 'name' field in a database table;
(2) traversing all data in a database table, namely all tree nodes in a tree structure, judging whether a node name corresponding to a currently traversed node contains the field of the specified node, namely whether a node name recorded under the field of 'name' corresponding to the current node contains a keyword, and judging whether a node path corresponding to the currently traversed node contains the id of the specified node, namely judging whether the content recorded under the field of 'path' contains '-id-'; if the judgment results are yes, judging that the matching is successful, marking the current node to meet the conditions, or directly acquiring the node name corresponding to the current node and continuously traversing the next node;
(3) and acquiring node names corresponding to all marked nodes as search results to be output, or directly outputting all the node names acquired in the previous step as the search results.
In a specific embodiment, the following search modes can be further adopted to further improve the search efficiency:
traversing all tree nodes in the tree structure, and acquiring all nodes of which node paths contain designated node ids; and based on all the acquired nodes, acquiring the nodes of which the corresponding node names contain the keywords or not as the search results.
Compared with the search method in the prior art, the fast search method used in this embodiment can reduce the time complexity from O (v + e) to O (v), where v represents the number of nodes and e represents the number of connections of the nodes. Corresponding to the search example of the tree structure of fig. 1, the time complexity of traversing this tree structure is reduced from O (5+4) to O (5). Obviously, the searching method of the embodiment is much higher than the traditional searching method, and is directly based on database storage and query, so that no extra memory overhead is generated, and the space complexity is greatly reduced.
Example two
Referring to fig. 1, the present embodiment corresponds to the first embodiment, and provides a specific application scenario.
Assuming that the organization architecture of a certain company is as shown in fig. 1, the structure of a database table corresponding to the organization architecture of the certain company, which is constructed based on the method of the first embodiment, is as shown in the first table below:
| id (node id) | name (node name) | parent (parent node id) | Path (node path) |
| 1 | Big education technology Co Ltd | 1 | -1- |
| 2 | Technical department | 1 | -1-2- |
| 3 | Research and development section 1 | 2 | -1-2-3- |
| 4 | Research and development 2 | 2 | -1-2-4- |
| 5 | Sales department | 1 | -1-5- |
Watch 1
Based on the database table structure, a specific search example using the search method of embodiment one is as follows:
1. search for all departments containing the keyword "big" under "big education technique Limited company
Judging the id of the large education technology limited company to be 1;
searching a database for a department with a field name containing ' big ' and a field path containing ' -1-;
and matching to obtain a result: "big education technology Co., Ltd".
2. Search for all departments containing "research and development" keywords under "big education technology Co., Ltd
Judging the id of the large education technology limited company to be 1;
searching a database for a department with a field name containing ' development ' and a field path containing ' -1-;
and matching to obtain a result: "part 1 of development" and "part 2 of development".
3. Search for departments under the "department of technology", including the "department" keyword
Judging that id of the technical department is 2;
searching a database for a department with a field name containing ' department ' and a field path containing ' -2-;
and matching to obtain a result: "technical department", "development 1 department", and "development 2 department".
4. Search for departments under the "department of technology", including the "2" keyword
Judging that id of the technical department is 2;
searching a database for a department with a field name containing ' 2 ' and a field path containing ' -2-;
and matching to obtain a result: "part 1 of development" and "part 2 of development".
EXAMPLE III
This embodiment corresponds to the first embodiment, and provides a computer-readable storage medium, on which a computer program is stored, where the computer program is capable of implementing all steps included in the method for quickly searching tree structure data in a database according to the first embodiment when the computer program is called by a processor. The specific steps are described in detail in the first embodiment, and are not repeated here.
In summary, the method and the storage medium for rapidly searching the tree structure data in the database provided by the invention can not only realize rapid search and obtain accurate results, but also greatly reduce the operation complexity; the operation is only carried out based on the database table, no extra memory overhead exists, and the space complexity is obviously reduced; furthermore, support can be realized only by newly adding a field in the database table, and the method is simple and convenient to operate and high in practicability. The method is particularly suitable for fast and accurate search of large-data-volume tree structure data, so that the method has a wide application prospect and further improves the practicability of the method.
The above description is only an embodiment of the present invention, and not intended to limit the scope of the present invention, and all equivalent changes made by using the contents of the present specification and the drawings, or applied directly or indirectly to the related technical fields, are included in the scope of the present invention.
Claims (5)
1. The method for rapidly searching tree structure data in the database is characterized by comprising the following steps:
storing the node path of each node corresponding to the tree structure to a database table;
receiving a request for searching all nodes containing a node field under a node in the tree structure;
searching and acquiring the node containing the node field and the corresponding node path containing the node from the database table;
the node path consists of node IDs and separators, and the node IDs are separated by the separators;
the searching and acquiring of the node containing the node field and the corresponding node path containing the node from the database table specifically includes:
acquiring a node ID corresponding to the node in the request and the node field;
traversing all nodes in the tree structure, judging whether the node name corresponding to the currently traversed node contains the node field, and simultaneously judging whether the node path corresponding to the currently traversed node contains the node ID; if so, acquiring a node name corresponding to the current node, and continuously traversing the next node;
and outputting the acquired node name as a search result.
2. The method for rapidly searching tree structured data in a database according to claim 1, further comprising:
traversing each node in the database table;
if the currently traversed node is the root node, acquiring a node path only containing the current node, and associating the node path with the current node;
and if the currently traversed nodes are other nodes, acquiring a node path containing all father nodes and the current node, and associating the node path with the current node.
3. The method for rapidly searching tree structure data in a database according to claim 2, wherein the obtaining of the node path including all parent nodes and the current node comprises:
acquiring all father nodes of a tree structure corresponding to a current node;
and sequencing all father nodes and the current node according to the sequence from father to son to obtain a node path.
4. A method for fast searching tree structured data in a database according to any of claims 1-3, wherein the node path corresponding to each node is stored by adding a field in the database table.
5. A computer-readable storage medium, on which a computer program is stored, which, when being invoked by a processor, is able to carry out the steps included in the method for fast searching tree-structured data in a database according to claims 1 to 4.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810494453.2A CN108681603B (en) | 2018-05-22 | 2018-05-22 | Method for rapidly searching tree structure data in database and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810494453.2A CN108681603B (en) | 2018-05-22 | 2018-05-22 | Method for rapidly searching tree structure data in database and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108681603A CN108681603A (en) | 2018-10-19 |
| CN108681603B true CN108681603B (en) | 2022-04-01 |
Family
ID=63807467
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810494453.2A Active CN108681603B (en) | 2018-05-22 | 2018-05-22 | Method for rapidly searching tree structure data in database and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108681603B (en) |
Families Citing this family (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110084564B (en) * | 2019-04-24 | 2021-07-02 | 李振 | ICE architecture for project data management |
| CN110704430A (en) * | 2019-09-17 | 2020-01-17 | 江苏苏宁物流有限公司 | Universal tree structure data query method and device |
| CN111767451B (en) * | 2020-01-15 | 2024-04-16 | 北京沃东天骏信息技术有限公司 | Searching method, electronic device and computer readable storage medium |
| CN112632065A (en) * | 2020-12-18 | 2021-04-09 | 北京锐安科技有限公司 | Data storage method and device, storage medium and server |
| CN112860692B (en) * | 2021-01-29 | 2023-07-25 | 城云科技（中国）有限公司 | Database table structure conversion method and device and electronic equipment thereof |
| CN115033657B (en) * | 2022-08-10 | 2023-01-31 | 广东美的暖通设备有限公司 | Inquiry method, device and equipment based on knowledge graph and storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102033954A (en) * | 2010-12-24 | 2011-04-27 | 东北大学 | Full text retrieval inquiry index method for extensible markup language document in relational database |
| CN103853773A (en) * | 2012-12-04 | 2014-06-11 | 厦门亿联网络技术股份有限公司 | Searching method of tree data structure of Mysql database |
| CN106446086A (en) * | 2016-09-09 | 2017-02-22 | 中国南方电网有限责任公司电网技术研究中心 | Tree structure operation method and system used for cloud computing environment |
| CN107679049A (en) * | 2016-08-02 | 2018-02-09 | 北京京东尚科信息技术有限公司 | Obtain the method, apparatus and system of the hop of tree structure data two |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7346609B2 (en) * | 2004-11-16 | 2008-03-18 | International Business Machines Corporation | Streaming XPath algorithm for XPath value index key generation |
| US8799315B2 (en) * | 2009-01-30 | 2014-08-05 | International Business Machines Corporation | Selective construction of data search result per search request specifying path information |
| CN102314492A (en) * | 2011-08-22 | 2012-01-11 | 百度在线网络技术（北京）有限公司 | Method and equipment for acquiring candidate document sections matched with target document section |
| CN102867059A (en) * | 2012-09-19 | 2013-01-09 | 浪潮(北京)电子信息产业有限公司 | Method and system for processing data in treelike structures |
-
2018
- 2018-05-22 CN CN201810494453.2A patent/CN108681603B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102033954A (en) * | 2010-12-24 | 2011-04-27 | 东北大学 | Full text retrieval inquiry index method for extensible markup language document in relational database |
| CN103853773A (en) * | 2012-12-04 | 2014-06-11 | 厦门亿联网络技术股份有限公司 | Searching method of tree data structure of Mysql database |
| CN107679049A (en) * | 2016-08-02 | 2018-02-09 | 北京京东尚科信息技术有限公司 | Obtain the method, apparatus and system of the hop of tree structure data two |
| CN106446086A (en) * | 2016-09-09 | 2017-02-22 | 中国南方电网有限责任公司电网技术研究中心 | Tree structure operation method and system used for cloud computing environment |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108681603A (en) | 2018-10-19 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108681603B (en) | Method for rapidly searching tree structure data in database and storage medium | |
| US10769142B2 (en) | Graph processing in database | |
| US20110078187A1 (en) | Semantic query by example | |
| US20120158736A1 (en) | Virtual r-tree mapped to an extendible-hash based file system | |
| CN106874426B (en) | RDF (resource description framework) streaming data keyword real-time searching method based on Storm | |
| US9753960B1 (en) | System, method, and computer program for dynamically generating a visual representation of a subset of a graph for display, based on search criteria | |
| JP2001014329A (en) | Database processing method and implementation device, and medium stored with the processing program | |
| JP5241738B2 (en) | Method and apparatus for building tree structure data from tables | |
| CN113590894A (en) | Dynamic and efficient remote sensing image metadata warehousing retrieval method | |
| CN109299101A (en) | Data retrieval method, device, server and storage medium | |
| US11573987B2 (en) | System for detecting data relationships based on sample data | |
| CN110990423B (en) | SQL statement execution method, device, equipment and storage medium | |
| Hamdi et al. | A pattern growth-based approach for mining spatiotemporal co-occurrence patterns | |
| CN111125216B (en) | Method and device for importing data into Phoenix | |
| CN108595588B (en) | Scientific data storage association method | |
| CN107315801B (en) | parallel discrete event simulation system initialization data storage method | |
| Flouris et al. | Issues in complex event processing systems | |
| CN113821508B (en) | Method and system for realizing array index | |
| US9378229B1 (en) | Index selection based on a compressed workload | |
| US9767411B2 (en) | Rule discovery system, method, apparatus, and program | |
| Li et al. | A novel approach for mining probabilistic frequent itemsets over uncertain data streams | |
| JP2020135530A (en) | Data management device, data search method and program | |
| KR20160001167A (en) | Method and Apparatus for moving data in DBMS | |
| US11341147B1 (en) | Finding dimensional correlation using hyperloglog | |
| CN113139389B (en) | Graph model semantic query expansion method and device based on dynamic optimization |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |