Looking for breakthrough ideas for innovation challenges? Try Patsnap Eureka!

MySQL tree data query method and system based on closure table

A data query and closure table technology, applied in the field of data query, can solve problems such as low efficiency and occupation, and achieve the effect of reducing the number of connections to MySQL and good portability

Pending Publication Date: 2022-01-07
SHANGHAI WANXIANG BLOCK CHAIN CO LTD
View PDF2 Cites 0 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

However, this patent requires multiple accesses to the database and occupies the database connection, which will lead to inefficiency

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • MySQL tree data query method and system based on closure table

Examples

Experimental program
Comparison scheme
Effect test

Embodiment

[0048] A kind of MySQL tree data query method based on closure table provided by the present invention comprises the following steps:

[0049] Step 1: Insert the tree data into the sys_menu table;

[0050] Said step 1 includes:

[0051] Step 1.1: Prepare data, define variables, and add child node 5 to the node with pid 4;

[0052] Step 1.2: Write sql, the specific form is: insertintosys_menu(id,pid) select5, pidfromsys_menu_relationwhereid=4

[0053] unionall

[0054] select4,4

[0055] The data of the final sys_menu table is as follows:

[0056] id pid 4 4 5 4

[0057] Step 2: Query all child nodes whose id is 4;

[0058] select *fromsys_menu_relationwherepid=4;

[0059] Step 3: Query all parent nodes whose id is 4;

[0060] select *fromsys_menu_relationwhereid=4

[0061]Step 4: Delete all child nodes with id 4, including yourself;

[0062] deletefromsys_menu_relationwhereidin(selectidfromsys_menu_relationwherepid=4)

[0063] Such as figu...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

PUM

No PUM Login to View More

Abstract

The invention provides a MySQL tree data query method and system based on a closure table. The MySQL tree data query method comprises the steps: 1, inserting tree data into a system menu sys_menu; 2, querying all child nodes with id of 4 through the system menu; 3, querying all father nodes with id of 4 through the system menu; and 4, deleting all child nodes with id of 4 through the system menu. According to the method, by using the redundant tree structure data relationship, the superior and the subordinate can be inquired directly by only one sql through the id or the pid , the number of times that the application is connected with the MySQL is reduced, meanwhile, the portability is better, databases such as the MySQL, the oracle and the sql server can be supported, and special grammar of the databases does not need to be used.

Description

technical field [0001] The invention relates to the technical field of data query, in particular to a MySQL tree data query method and system based on a closure table. Background technique [0002] As we all know, MySQL tree structure data retrieval is difficult, and other databases have more elegant solutions, such as Oracle, you can use startwith...connectby for tree query, and qlserver can use the with keyword. Currently, there are two mainstream solutions in MySQL, as shown in the following table: [0003] id pid 1 0 2 1 3 2 4 3 [0004] Type 1: In the application program, first query all child nodes through the process identifier pid, and then query all subordinate nodes under the child nodes in turn, and recurse in turn until no child nodes are found; Type 2: In MySQL, define Stored procedure, using the cursor down to find all nodes. Both of the above two methods have defects. The first method needs recursion, accesses the database...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

Application Information

Patent Timeline
no application Login to View More
IPC IPC(8): G06F16/22G06F16/245G06F16/28
CPCG06F16/2246G06F16/2282G06F16/245G06F16/284
Inventor 陈勇
Owner SHANGHAI WANXIANG BLOCK CHAIN CO LTD
Features
  • Generate Ideas
  • Intellectual Property
  • Life Sciences
  • Materials
  • Tech Scout
Why Patsnap Eureka
  • Unparalleled Data Quality
  • Higher Quality Content
  • 60% Fewer Hallucinations
Social media
Patsnap Eureka Blog
Learn More

Browse by: Latest US Patents, China's latest patents, Technical Efficacy Thesaurus, Application Domain, Technology Topic, Popular Technical Reports.

© 2025 PatSnap. All rights reserved.Legal|Privacy policy|Modern Slavery Act Transparency Statement|Sitemap|About US| Contact US: help@patsnap.com