CN104063413A - MYSQL (my structured query language) database tree structure and quick retrieval method thereof - Google Patents
MYSQL (my structured query language) database tree structure and quick retrieval method thereof Download PDFInfo
- Publication number
- CN104063413A CN104063413A CN201310557218.2A CN201310557218A CN104063413A CN 104063413 A CN104063413 A CN 104063413A CN 201310557218 A CN201310557218 A CN 201310557218A CN 104063413 A CN104063413 A CN 104063413A
- Authority
- CN
- China
- Prior art keywords
- node
- information
- tree
- database
- father
- 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
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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2246—Trees, e.g. B+trees
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Software Systems (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to the technical field of database structure design, in particular to an MYSQL (my structured query language) database tree structure and a quick retrieval method thereof. Each node of the MYSQL database tree structure comprises information of a direct parent node thereof, and a root node has no parent node; each node comprises information of all parent nodes, the information of each parent node is distinguished by a special character, and the information of the all parent nodes of the root node is the node. During the operation of a database, the node information is queried through a like condition and the parent node information is queried through an in condition. By the quick retrieval method, the existing problem of the database loading efficiency is solved; the quick retrieval method can be applied to the design of the MYSQL database tree structure.
Description
Technical field
The present invention relates to database structure design field, be specifically related to the tree-shaped structure of a kind of MYSQL database and method for quickly retrieving thereof.
Background technology
When MYSQL (Relational DBMS is developed by Sweden MySQL AB company) database design tree structure, be all by the method for the direct father node of nodes records, finally form tree structure.But in database manipulation process, this structure meeting is cumbersome and efficiency is very low, as shown in Figure 1, one tree has five layers, if want to inquire about the fullpath of the 3rd layer of a certain node (node-3-2), operating process is the direct father node (node-2-1) of first location node (node-3-2), relocates the father node (root) to node (node-2-1).Therefore need database manipulation 2 times.In like manner, if want to inquire about all nodes of the 3rd layer of a certain node (node-3-2), need first inquire about its direct child node, then inquire about the child node of direct child node.Therefore, if always have N layer, check the fullpath of K layer (K<N), need database manipulation N-1 time; If inquire about all child nodes, need to operate N-K time.This has increased the weight of the load of database greatly, and efficiency is also very low.
Summary of the invention
One of technical matters that the present invention solves is to provide a kind of MYSQL database tree-shaped structure, only needs a database manipulation can complete the father node path of certain node of search or obtain its all child node information.
Two of the technical matters that the present invention solves is to provide a kind of MYSQL database tree-shaped structure method for quickly retrieving, only needs a database manipulation can complete the father node path of certain node of search or obtain its all child node information.
The technical scheme that the present invention one of solves the problems of the technologies described above is:
Each node of described tree structure comprises its direct father node information, and the father node of root node is empty; Each node comprises the information of all father nodes, and the information exchange of each father node is crossed special character and distinguished, and all father node information of root node are this node.
Described special character is ", ".
Two the technical scheme that the present invention solves the problems of the technologies described above is:
In database manipulation process, by like condition query nodal information, by condition query father node information.
By like condition query nodal information, completing statement can be:
Select*from tree where code like ' % parameter, % '; The table name of tree representation data wherein; The id information of the node that Parametric Representation need to be inquired about.
By in condition query father node information, completing statement can be:
Select*from sets where id in (parameter); The table name of tree representation data wherein; The father node id information of the node that Parametric Representation need to be inquired about.
Structure of the present invention and method can greatly improve the efficiency of database, the operation of minimizing database, the response time of greatly improving application system simultaneously.
Accompanying drawing explanation
Below in conjunction with accompanying drawing, the present invention is further described:
Fig. 1 first has tree structure node structure figure;
Fig. 2 is database design structural drawing of the present invention;
Fig. 3 is the tree-shaped structure node structural drawing of database of the present invention.
Embodiment
As shown in Figure 2,3, the present invention sets the database design of assembling structure, need comprise following two kinds of important informations:
1st, each node must comprise its direct father node information, the father node of root node be empty;
2nd, each node must comprise the information of all father nodes, and the information of each father node can be distinguished by special character, and all father node information of root node are this node.If the attribute of the root of root is (1, ' 1 '), the attribute of straight its child node node-2-1 is (2, ' 1,2 '), ' 1 in attribute wherein, 2 ' is respectively the id attribute (1) of root node and the id attribute (2) of child node node-2-1, by special character ", ", distinguish again.
3rd,, in database manipulation process, can pass through like condition query nodal information.All child node information as query node, can complete by following statement:
Select*from tree where code like ' % parameter, % '; The table name of tree representation data wherein; The id information of the node that Parametric Representation need to be inquired about.
4th,, in database manipulation process, can pass through in condition query father node information.The father node information completing as query node, can complete by following statement:
Select*from sets where id in (parameter); The table name of tree representation data wherein; The parents_id information of the node that Parametric Representation need to be inquired about.
Lower mask body is example by the use of education cloud shared platform.
Comprise the steps:
The 1st step, cloud shared platform support user to create the directory information of tree structure.
The 2nd step, need to obtain node node-3-2 (5, ' 1,2,5 ' as user) all child nodes time, can complete by statement under platform:
Select*from tree where parents_id like ' %5, % '.
The 3rd step, need to obtain node node-3-2 (5, ' 1,2,5 ' as user) all father nodes time, can complete by following SQL statement:
Select from tree where id in (' 1,2,5 ').
Claims (5)
1. the tree-shaped structure of MYSQL database, is characterized in that: each node of described tree structure comprises its direct father node information, and the father node of root node is empty; Each node comprises the information of all father nodes, and the information exchange of each father node is crossed special character and distinguished, and all father node information of root node are this node.
2. the tree-shaped structure of MYSQL database according to claim 1, is characterized in that: described special character is ", ".
3. the tree-shaped structure method for quickly retrieving of MYSQL database claimed in claim 1, is characterized in that: in database manipulation process, by like condition query nodal information, by condition query father node information.
4. method for quickly retrieving according to claim 3, is characterized in that: by like condition query nodal information, completing statement can be:
Select*from tree where code like ' % parameter, % '; The table name of tree representation data wherein; The id information of the node that Parametric Representation need to be inquired about.
5. according to the method for quickly retrieving described in claim 3 or 4, it is characterized in that: by condition query father node information, completing statement can be:
Select*from sets where id in (parameter); The table name of tree representation data wherein; The father node id information of the node that Parametric Representation need to be inquired about.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201310557218.2A CN104063413A (en) | 2013-11-11 | 2013-11-11 | MYSQL (my structured query language) database tree structure and quick retrieval method thereof |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201310557218.2A CN104063413A (en) | 2013-11-11 | 2013-11-11 | MYSQL (my structured query language) database tree structure and quick retrieval method thereof |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN104063413A true CN104063413A (en) | 2014-09-24 |
Family
ID=51551128
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201310557218.2A Pending CN104063413A (en) | 2013-11-11 | 2013-11-11 | MYSQL (my structured query language) database tree structure and quick retrieval method thereof |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN104063413A (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105701205A (en) * | 2016-01-12 | 2016-06-22 | 北京中交兴路车联网科技有限公司 | Affiliation based data system and method |
| CN106446086A (en) * | 2016-09-09 | 2017-02-22 | 中国南方电网有限责任公司电网技术研究中心 | Tree structure operation method and system for cloud computing environment |
| CN107231394A (en) * | 2016-03-25 | 2017-10-03 | 阿里巴巴集团控股有限公司 | A kind of building method of data source address distribution tree and the method for replicate data |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2007061430A1 (en) * | 2005-11-22 | 2007-05-31 | Bea Systems, Inc. | System and method for translating sql queries into xml query language |
| CN101330657A (en) * | 2008-07-15 | 2008-12-24 | 华通科技有限公司 | Address list system and implementing method thereof |
| CN101408895A (en) * | 2008-11-03 | 2009-04-15 | 金蝶软件（中国）有限公司 | Method, apparatus and system for constructing node relation tree according to node aggregation |
| CN101789028A (en) * | 2010-03-19 | 2010-07-28 | 苏州广达友讯技术有限公司 | Search engine for geographical position and constructing method thereof |
| CN103106198A (en) * | 2011-11-09 | 2013-05-15 | 金蝶软件（中国）有限公司 | Tree structure implementation method and tree structure implementation device |
-
2013
- 2013-11-11 CN CN201310557218.2A patent/CN104063413A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2007061430A1 (en) * | 2005-11-22 | 2007-05-31 | Bea Systems, Inc. | System and method for translating sql queries into xml query language |
| CN101330657A (en) * | 2008-07-15 | 2008-12-24 | 华通科技有限公司 | Address list system and implementing method thereof |
| CN101408895A (en) * | 2008-11-03 | 2009-04-15 | 金蝶软件（中国）有限公司 | Method, apparatus and system for constructing node relation tree according to node aggregation |
| CN101789028A (en) * | 2010-03-19 | 2010-07-28 | 苏州广达友讯技术有限公司 | Search engine for geographical position and constructing method thereof |
| CN103106198A (en) * | 2011-11-09 | 2013-05-15 | 金蝶软件（中国）有限公司 | Tree structure implementation method and tree structure implementation device |
Non-Patent Citations (1)
| Title |
|---|
| （美）GAVIN POWELL（沈洁 等译），: ""数据库入门经典"", 《数据库入门经典》 * |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105701205A (en) * | 2016-01-12 | 2016-06-22 | 北京中交兴路车联网科技有限公司 | Affiliation based data system and method |
| CN107231394A (en) * | 2016-03-25 | 2017-10-03 | 阿里巴巴集团控股有限公司 | A kind of building method of data source address distribution tree and the method for replicate data |
| CN107231394B (en) * | 2016-03-25 | 2020-08-11 | 阿里巴巴集团控股有限公司 | Construction method of data source address distribution tree and method for copying data |
| CN106446086A (en) * | 2016-09-09 | 2017-02-22 | 中国南方电网有限责任公司电网技术研究中心 | Tree structure operation method and system for cloud computing environment |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN106227800B (en) | Storage method and management system for highly-associated big data | |
| CN104123374B (en) | The method and device of aggregate query in distributed data base | |
| US20160092527A1 (en) | Data processing apparatus and data mapping method thereof | |
| CN107491561B (en) | Ontology-based urban traffic heterogeneous data integration system and method | |
| Chauhan | A review on various aspects of MongoDB databases | |
| CN104809190B (en) | A kind of database access method of tree structure data | |
| US9607042B2 (en) | Systems and methods for optimizing database queries | |
| US20150120775A1 (en) | Answering relational database queries using graph exploration | |
| CN103390015B (en) | Based on mass data stored in association method and the search method of unified index | |
| CN110019555B (en) | Relation data semantical modeling method | |
| CN105849726A (en) | Generic indexing for efficiently supporting ad-hoc query over hierarchically marked-up data | |
| US11893026B2 (en) | Advanced multiprovider optimization | |
| CN104573022A (en) | Data query method and device for HBase | |
| CN104216961A (en) | Method and device for data processing | |
| CN103123650A (en) | Extensible markup language (XML) data bank full-text indexing method based on integer mapping | |
| JP5090481B2 (en) | Data modeling method, apparatus and program | |
| CN107491476A (en) | A kind of data model translation and query analysis method suitable for a variety of big data management systems | |
| US10776352B2 (en) | Generic query language for data stores | |
| CN102819600B (en) | Keyword search methodology towards relational database of power production management system | |
| CN103092992A (en) | Vector data preorder quadtree coding and indexing method based on Key / Value type NoSQL (Not only SQL) | |
| CN103177046B (en) | A kind of data processing method based on row storage data base and equipment | |
| US9116932B2 (en) | System and method of querying data | |
| CN104063413A (en) | MYSQL (my structured query language) database tree structure and quick retrieval method thereof | |
| KR101955376B1 (en) | Processing method for a relational query in distributed stream processing engine based on shared-nothing architecture, recording medium and device for performing the method | |
| CN103942267A (en) | Method for setting up tree-shaped data structure applied to online education system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20140924 |