CN103853773A - Searching method of tree data structure of Mysql database - Google Patents
Searching method of tree data structure of Mysql database Download PDFInfo
- Publication number
- CN103853773A CN103853773A CN201210511993.XA CN201210511993A CN103853773A CN 103853773 A CN103853773 A CN 103853773A CN 201210511993 A CN201210511993 A CN 201210511993A CN 103853773 A CN103853773 A CN 103853773A
- Authority
- CN
- China
- Prior art keywords
- node
- data structure
- path
- field
- nodes
- 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.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2246—Trees, e.g. B+trees
Abstract
The invention relates to a searching method of a tree data structure of a Mysql database. The searching method comprises the following steps of adding a path field on the basis of a standard tree data structure table, wherein the path field records the path of each node, and the content of the path field contains the node and all nodes of the previous level which are spliced through characters; obtaining a new tree data structure table; when the data query is executed, matching the node according to the to-be-queried path field of the node, and finding all nodes under the node; additionally adding a layer field, and enabling the layer field to record the layer of each node; step 3, when the data query is executed, matching the node according to the to-be-queried layer field of the node, and finding all nodes of one layer, or the number of nodes of one layer. The method aims at solving the technical problem of providing a simple and effective researching method of the tree data structure of the Mysql database, so the readability, maintainability and portability of an sql (structural queried language) sentence are improved.
Description
Technical field
The present invention relates to Mysql database, belong to database retrieval field, specifically refer to the search method of tree form data structure under a kind of Mysql database.
Background technology
Fig. 1 is that the tree-like structure of Mysql database represents form schematic diagram.As shown in Figure 2, the field of showing in figure is id to its tree structure standard knots composition, name, and fid, wherein fid is father node id.There are two kinds for the search method of this kind of table: the 1st kind of method is: first inquire all child nodes by Parent_ID, then inquire about successively all downstream sites under child node.Circulation successively.The 2nd kind of method is: by definition storing process.Utilize moving down of vernier to search all nodes.
Above two kinds of equal defectiveness of way, the 1st kind of method can not disposable taking-up target data, needs repeatedly accessing database, and efficiency is extremely low; The 2nd kind of method readable poor, be difficult to maintenance, portable ability.
In view of this, the inventor furthers investigate for the defect of prior art, and has this case to produce.
Summary of the invention
Technical matters to be solved by this invention is to provide the search method of tree form data structure under a kind of simple and effective Mysql database, strengthens readability, maintainability, the portable ability of sql statement.
The present invention solves the problems of the technologies described above by the following technical solutions:
One of technical scheme:
Under Mysql database, a search method for tree form data structure, comprises the steps:
Step 1: on the basis of the tree form data structure table of standard, increase a path field, the path of the each node of this path field record, this path field contents is: all nodes of this node and higher level splice by character; Obtain a new tree form data structure table;
Step 2: in the time that executing data is inquired about, carry out matched node according to the path field of the node that will inquire about, find all nodes under this node.
Two of technical scheme:
Under Mysql database, a search method for tree form data structure, comprises the steps:
Step 1: on the basis of the tree form data structure table of standard, increase a path field, the path of the each node of this path field record, this path field contents is: all nodes of this node and higher level splice by character; Obtain a new tree form data structure table;
Step 2: increase again a layer field on the basis of described new tree form data structure table, the level of the each node of this layer field record;
Step 3: in the time that executing data is inquired about, carry out matched node according to the layer field of the node that will inquire about, find all nodes of a certain level, or the node number of a certain level.
The invention has the advantages that: in database, defined a tables of data, in table, stored tree data (as: institutional framework, map of website).While inquiring about under some nodes all child nodes, by creating a special path field, improve recall precision.And while inquiring about all child nodes of a certain level under some nodes, realize by creating a level field.The present invention is simply effective, strengthens readability, maintainability, the portable ability of sql statement.
Brief description of the drawings
The invention will be further described in conjunction with the embodiments with reference to the accompanying drawings.
Fig. 1 is that the tree-like structure of Mysql database represents form schematic diagram.
Fig. 2 is the tree-like construction standard structural drawing of Mysql database.
Fig. 3 is the tree structure schematic diagram of first embodiment of the invention.
Fig. 4 is the Query Result schematic diagram of first embodiment of the invention.
Fig. 5 is the tree structure schematic diagram of second embodiment of the invention.
Fig. 6 is the Query Result schematic diagram of second embodiment of the invention.
Fig. 7 is the inquiry number result schematic diagram of second embodiment of the invention.
Embodiment
The first embodiment:
Under Mysql database, a search method for tree form data structure, comprises the steps:
Step 1: increase a path field on the basis of the tree form data structure table of standard, as shown in Figure 3, the path of the each node of this path field record, this path field contents is: all nodes of this node and higher level splice by character, Figure 3 shows that and splices with colon; Obtain a new tree form data structure table;
Step 2: in the time that executing data is inquired about, if want the lower all nodes of inquiry " Heilungkiang ", as long as carry out matched node according to the path field of " Heilungkiang " this node, find all nodes under this node.
Sql example: the lower all nodes of inquiry " Heilungkiang "
Select?*?from?location?where?path?like?‘1:5%’；
The result obtaining as shown in Figure 4.
The second embodiment:
Under Mysql database, a search method for tree form data structure, comprises the steps:
Step 1: on the basis of the tree form data structure table of standard, increase a path field, the path of the each node of this path field record, this path field contents is: all nodes of this node and higher level splice by character; Obtain a new tree form data structure table;
Step 2: on the basis of described new tree form data structure table, increase again a layer field, as shown in Figure 5, the level of the each node of this layer field record;
Step 3: in the time that executing data is inquired about, carry out matched node according to the layer field of the node that will inquire about, find all nodes of a certain level, or the node number of a certain level.
Sql example: the node of the lower all third level of inquiry " administrative organization "
Select?*?from?location?where?path?like?‘1:%’ and?layer=3?；
The result obtaining as shown in Figure 6.
Sql example: the node number of the lower all third level of inquiry " administrative organization "
Select?count(id)?from?location?where?path?like?‘1:%’ and?layer=3?；
The result obtaining as shown in Figure 7.
The invention provides the search method of tree form data structure under a kind of simple and effective Mysql database, strengthen readability, maintainability, the portable ability of sql statement, improved recall precision.
The foregoing is only better enforcement use-case of the present invention, be not intended to limit protection scope of the present invention.Within the spirit and principles in the present invention all, any amendment of doing, be equal to and replace and improvement etc., within all should being included in protection scope of the present invention.
Claims (2)
1. a search method for tree form data structure under Mysql database, is characterized in that: comprise the steps:
Step 1: on the basis of the tree form data structure table of standard, increase a path field, the path of the each node of this path field record, this path field contents is: all nodes of this node and higher level splice by character; Obtain a new tree form data structure table;
Step 2: in the time that executing data is inquired about, carry out matched node according to the path field of the node that will inquire about, find all nodes under this node.
2. a search method for tree form data structure under Mysql database, is characterized in that: comprise the steps:
Step 1: on the basis of the tree form data structure table of standard, increase a path field, the path of the each node of this path field record, this path field contents is: all nodes of this node and higher level splice by character; Obtain a new tree form data structure table;
Step 2: increase again a layer field on the basis of described new tree form data structure table, the level of the each node of this layer field record;
Step 3: in the time that executing data is inquired about, carry out matched node according to the layer field of the node that will inquire about, find all nodes of a certain level, or the node number of a certain level.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210511993.XA CN103853773A (en) | 2012-12-04 | 2012-12-04 | Searching method of tree data structure of Mysql database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210511993.XA CN103853773A (en) | 2012-12-04 | 2012-12-04 | Searching method of tree data structure of Mysql database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN103853773A true CN103853773A (en) | 2014-06-11 |
Family
ID=50861439
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201210511993.XA Pending CN103853773A (en) | 2012-12-04 | 2012-12-04 | Searching method of tree data structure of Mysql database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103853773A (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107807932A (en) * | 2016-09-08 | 2018-03-16 | 腾讯科技（深圳）有限公司 | A kind of hierarchical data management method and system based on path enumeration |
| CN108681603A (en) * | 2018-05-22 | 2018-10-19 | 福建天泉教育科技有限公司 | The method of fast search tree structure data, storage medium in database |
| CN112632065A (en) * | 2020-12-18 | 2021-04-09 | 北京锐安科技有限公司 | Data storage method and device, storage medium and server |
| CN113886433A (en) * | 2021-10-01 | 2022-01-04 | 浙江大学 | Hierarchical structure area retrieval method |
| CN115645905A (en) * | 2022-10-21 | 2023-01-31 | 圣名科技(广州)有限责任公司 | Method and device for cursor display, electronic equipment and storage medium |
| CN115840751A (en) * | 2023-02-21 | 2023-03-24 | 山东经伟晟睿数据技术有限公司 | Novel tree data coding method |
| CN116680450A (en) * | 2023-06-06 | 2023-09-01 | 智研汇(上海)科技有限公司 | Tree data structure and paging loading method thereof |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1561496A (en) * | 2001-09-28 | 2005-01-05 | 甲骨文国际公司 | An efficient index structure to access hierarchical data in a relational database system |
| US20080071769A1 (en) * | 2006-08-23 | 2008-03-20 | Govindarajan Jagannathan | Efficient Search Result Update Mechanism |
| US20110093486A1 (en) * | 2009-10-15 | 2011-04-21 | Institute For Information Industry | Data query method, data query system and computer readable and writable recording medium |
| CN102033954A (en) * | 2010-12-24 | 2011-04-27 | 东北大学 | Full text retrieval inquiry index method for extensible markup language document in relational database |
| CN102467521A (en) * | 2010-11-08 | 2012-05-23 | 北大方正集团有限公司 | Easily-extensible multi-level classification search method and system |
-
2012
- 2012-12-04 CN CN201210511993.XA patent/CN103853773A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1561496A (en) * | 2001-09-28 | 2005-01-05 | 甲骨文国际公司 | An efficient index structure to access hierarchical data in a relational database system |
| US20080071769A1 (en) * | 2006-08-23 | 2008-03-20 | Govindarajan Jagannathan | Efficient Search Result Update Mechanism |
| US20110093486A1 (en) * | 2009-10-15 | 2011-04-21 | Institute For Information Industry | Data query method, data query system and computer readable and writable recording medium |
| CN102467521A (en) * | 2010-11-08 | 2012-05-23 | 北大方正集团有限公司 | Easily-extensible multi-level classification search method and system |
| CN102033954A (en) * | 2010-12-24 | 2011-04-27 | 东北大学 | Full text retrieval inquiry index method for extensible markup language document in relational database |
Cited By (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107807932A (en) * | 2016-09-08 | 2018-03-16 | 腾讯科技（深圳）有限公司 | A kind of hierarchical data management method and system based on path enumeration |
| CN108681603A (en) * | 2018-05-22 | 2018-10-19 | 福建天泉教育科技有限公司 | The method of fast search tree structure data, storage medium in database |
| CN108681603B (en) * | 2018-05-22 | 2022-04-01 | 福建天泉教育科技有限公司 | Method for rapidly searching tree structure data in database and storage medium |
| CN112632065A (en) * | 2020-12-18 | 2021-04-09 | 北京锐安科技有限公司 | Data storage method and device, storage medium and server |
| WO2022127114A1 (en) * | 2020-12-18 | 2022-06-23 | 北京锐安科技有限公司 | Data storage method and apparatus, and storage medium and server |
| CN113886433A (en) * | 2021-10-01 | 2022-01-04 | 浙江大学 | Hierarchical structure area retrieval method |
| CN115645905A (en) * | 2022-10-21 | 2023-01-31 | 圣名科技(广州)有限责任公司 | Method and device for cursor display, electronic equipment and storage medium |
| CN115840751A (en) * | 2023-02-21 | 2023-03-24 | 山东经伟晟睿数据技术有限公司 | Novel tree data coding method |
| CN116680450A (en) * | 2023-06-06 | 2023-09-01 | 智研汇(上海)科技有限公司 | Tree data structure and paging loading method thereof |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN103853773A (en) | Searching method of tree data structure of Mysql database | |
| US8527451B2 (en) | Business semantic network build | |
| Aboutorabiª et al. | Performance evaluation of SQL and MongoDB databases for big e-commerce data | |
| CN102982076B (en) | Based on the various dimensions content mask method in semantic label storehouse | |
| Colazzo et al. | RDF analytics: lenses over semantic graphs | |
| CN107038207A (en) | A kind of data query method, data processing method and device | |
| CN103631909B (en) | System and method for combined processing of large-scale structured and unstructured data | |
| CN106407303A (en) | Data storage method and apparatus, and data query method and apparatus | |
| Sharma et al. | Performance analysis of RDBMS and no SQL databases: PostgreSQL, MongoDB and Neo4j | |
| CN103123650B (en) | A kind of XML data storehouse full-text index method mapped based on integer | |
| CN110941612A (en) | Autonomous data lake construction system and method based on associated data | |
| CN103390015A (en) | Mass data united storage method based on unified indexing and search method | |
| CN103412925A (en) | System and method for integrated searching of structured data and unstructured data | |
| CN104216961A (en) | Method and device for data processing | |
| CN102819600B (en) | Keyword search methodology towards relational database of power production management system | |
| CN104346466A (en) | Method and device of adding new attribute data in database | |
| CN103455335A (en) | Multilevel classification Web implementation method | |
| CN104391941A (en) | Method for rapidly establishing full-text retrieval tool for common files | |
| CN101937433A (en) | Real-time searching method of product | |
| CN106503040A (en) | It is suitable for KV data bases and its creation method of SQL query method | |
| CN101963993B (en) | Method for fast searching database sheet table record | |
| CN103365868A (en) | Data processing method and data processing system | |
| CN105069101A (en) | Distributed index construction and search method | |
| Schwade et al. | A semantic data lake for harmonizing data from cross-platform digital workspaces using ontology-based data access | |
| Haque et al. | Distributed RDF triple store using hbase and hive |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WD01 | Invention patent application deemed withdrawn after publication | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20140611 |