Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve tree data in MySQL, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Classification of 0. 0 tree data

When we design the mysql database, we will encounter a kind of tree-like data. For example, the company is divided into several departments, and each department is divided into several departments to form tree-like data. The tree-like data can be roughly divided into the following two categories according to the number of levels:

The classification features are fixed, the number of levels is fixed, and each level has its own meaning, such as group-branch-department-department, province-city-district and other variable quantity levels are not fixed, the first few levels may have a special meaning, but the whole is floating in a considerable range.

The advantage of the former is that because each level has its own meaning, the overall design of the database is more convenient, and the different superior nodes of a child node can be stored in the database. Also take a group as an example:

Node code Node name Node hierarchy Node code1 level ancestor code2 level ancestor cdoe010000 Company 21000000nullnull010300 Manufacturing Department 2010000010000null010400 quality Department before 2010000010000null010301 Engineering Manufacturing 3010300010000010303 Assembly Manufacturing 3010300010000010300

The table designed in this way is more redundant, but it is more efficient in various types of queries. When inserting, updating (including sub-organizations, due to the characteristics of business logic, the updates between organizations are generally transferred in parallel), and deleting (including sub-organizations), because of the large amount of redundant information, the query needed for data operation is also relatively simple. According to the situation, some redundant information is also considered to be deleted, such as the parent node code, deleting some designs will inevitably lead to the improvement of the efficiency or complexity of some queries, which needs to be balanced according to the actual situation.

There are two disadvantages:

One is that when there are a large number of levels, a large amount of redundant information needs to be stored. Of course, you can also consider saving solutions: 1) do not store fields such as n-level ancestor code, but it is not recommended to take advantage of the efficient query features brought by fixed-level design; 2) n-level storage uses id instead of code, which is mainly inconvenient when data migration or other tables are used.

Another disadvantage is that when the demand side gives the request, you will have a big headache when you need to reshuffle the current organization and change the hierarchy.

The advantages and disadvantages of the latter are contrary to the advantages and disadvantages of the former, the non-fixed hierarchical restrictions are very flexible, and the disadvantages are the inconvenience of query and data operation, which is also the focus of this article, that is, how to design non-fixed hierarchical tree data.

1 the design method of non-fixed hierarchical tree data-ancestor path

One of the simplest ways to design tree data is to add only parent id. However, this design method brings great inconvenience to query descendant nodes. As far as I know, there is no query method that does not loop through functions / stored procedures to get all descendant nodes or ancestor nodes of a node at once. (previously, we have found a complex sql for querying descendant nodes, which also takes advantage of the fact that the id of the ancestor node is greater than the id of the descendant node, but it is possible to update the node so that the id of the descendant node is greater than the id of the ancestor node, so it is not rigorous, so I will not elaborate on it here.)

One way to design non-fixed hierarchical tree data is to add ancestor paths (ancestor_path). For more information, please see the following table:

Id | Node name | parent id | ancestor path

-|-node2 | node2 | 0 | 3 | node1.1 | 1 | node1.2 | 1 | node1.2 | 1 | 5 | node1.1.1 | 3 | node1.1.1 | 3 | 3 | 8 | node1.2.1 | 4 | 1, 9 | node2.1.1 | 5 | 2, 5 |

In the actual design, you can also consider adding the redundant field of hierarchy, but I seldom use this field in my actual use.

In this way, after adding this field, all the ancestral node information of any node can be obtained through such a piece of data.

The setting of ancestral paths has the following characteristics:

If there is no root node of the parent node, the parent id defaults to '0century, and the ancestor path defaults to' 0Magne.'

For each additional child node, the ancestor path adds the parent id and','to the ancestor path of the parent node of the child node to be added. The table structure of the reference is as follows:

CREATE TABLE `tnode` (`ancestor_ id` int (11) NOT NULL AUTO_INCREMENT, `node_ name` varchar (50) NOT NULL, `p_ id` int (11) NOT NULL, `ancestor_ path` varchar (100) NOT NULL, PRIMARY KEY (`node_ id`) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

2 query of ancestral paths

There are mainly two kinds of queries for designed tree nodes, one is to query all descendant nodes of a node (the same meaning as querying the collection of all nodes whose ancestors are known nodes), which is also the most commonly used query; one is to query all the ancestor nodes of a node, which is not very common.

1. The reference example for querying all descendants of a node is as follows:

SELECT * FROM t_node WHERE ancestor_path LIKE CONCAT ((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?) wt),',%')

Is the above sql for id? The query method 1 for all descendants of a node can also be used in the following ways:

SELECT * FROM t_node WHERE ancestor_path LIKE CONCAT ('%,',?,',%')

The second way of query is more concise. However, considering that the query mode one only uses the right fuzzy query, you can use the index, so it is recommended to use the first method for query.

It should be noted that the node sets found in the above two methods do not contain child nodes. If you need to include the information of this node, you also need to add

... OR node_id=?

two。 Query all ancestor nodes of a node

SELECT * FROM t_node WHERE node_id REGEXP CONCAT ('^ (', REPLACE ((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)) Wt),'|'),'0) $')

It is true that the efficiency of querying ancestor nodes in the above way is not very high, but considering that the query itself is not used, we will use it for a while.

3 insertion, update and deletion of ancestral paths

Insert, update and delete respectively:

1. insert

INSERT INTO t_node (node_name,p_id,ancestor_path) VALUE ('node?',?, CONCAT ((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?) wt),?,'))

Three of the sql? Both are id to be added to the parent node.

two。 Update (with child nodes)

If the location of the parent node does not change during the update, you don't have to think too much about it.

If you need to update the parent node, compared with the simplest tree node design pattern, the way to increase the ancestor path is not only to update the parent id of the current node itself, but also to modify the corresponding ancestor path. This step is implemented through stored procedures, which is a relatively simple way, which will not be described in detail here. Only describe ways in which stored procedures are not used.

UPDATE t_node SET p_id=?_p WHERE node_id=?_n; UPDATE t_node SET ancestor_path=CONCAT ((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?_p) wt2),? _ p ancestor_path,LENGTH (@ PPath) + 1)) WHERE ancestor_path LIKE CONCAT ((SELECT * FROM (SELECT @ ppath:=ancestor_path FROM t_node WHERE node_id=?_n) wt),? _ n SELECT%') OR node_id=?_n

Where? _ n represents the id,?_p of the node to be modified and the id of the new parent node of the node to be modified.

Note: to use this sql, you must first update the ancestor path of the child node, and then update the ancestor path of this node, which can be ignored if you are using stored procedures.

3. Delete (with child nodes)

DELETE FROM t_node WHERE ancestor_path LIKE CONCAT ((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?) wt),',%')

The core of deletion is where, which is exactly the same as getting the where of all descendant nodes.

Similarly, delete all descendant nodes first, and then delete this node.

4 reset of ancestral paths

It is possible that one of your previous database tables did not use the ancestor path, but has accumulated a certain amount of data, or used the ancestor path before, but for some reason caused some data update errors of the ancestor path. Because the ancestor path is essentially a redundant field, it can still be restored and reset as the parent id.

The following is a reset stored procedure for the mechanism table for your reference:

CREATE DEFINER= `root` @ `localhost` PROCEDURE `paired organizer _ path` (OUT resultMark varchar (50)) BEGIN / * instructions before use: 1. This stored procedure is not used by customers, and its frequency is also low, so it is more convenient to debug, but the efficiency is not very high. 2. If you execute SELECT * FROM t_organ WHERE organ_id0 AND intLoopDone=0) DO-continuous loop, when there is no organId data (if the intermediary is interrupted, it may fall into an endless loop) SELECT COUNT (1) FROM tmp_pOrganIdList INTO intPCount;-- current parent id cache SET intPIndex=0; WHILE intPIndex0 THEN INSERT INTO tmp_pOrganIdList (organ_id) (SELECT organ_id FROM tmp_cOrganIdList) DELETE FROM tmp_cOrganIdList; ELSE SET intLoopDone=1; END IF;-- SELECT * FROM tmp_pOrganIdList;-- SELECT COUNT (1) FROM tmp_aOrganIdList;-- SELECT intLoopDone; END WHILE;-- SELECT * FROM tmp_rOrganIdList;-- to view the test results, please look at this table SELECT COUNT (1) FROM tmp_rOrganIdList INTO intRCount; WHILE intRIndex

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.