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

This article mainly introduces how Mysql stores the tree structure through Adjacency List. It is very detailed and has a certain reference value. Interested friends must read it!

How do you store a tree structure in a database?

Relational databases like mysql are more suitable for storing flattened data similar to tables, but it is difficult to handle people with such a deep tree structure.

For example, Chestnut: now there is one to store the company's personnel structure, the approximate hierarchy is as follows:

It's not easy to draw a picture. )

So how do you store this structure? And to get the following information:

1. Inquire about Xiaotian's direct boss.

two。 Inquire about the employees directly under the management of Lao Song.

3. Inquire about all the bosses of Xiaotian.

4. Inquire all the employees managed by Lao Wang.

Scheme 1. (Adjacency List) stores only the parent node information of the current node.

CREATE TABLE Employees (

Eid int

Ename VARCHAR (100)

Position VARCHAR (100)

Parent_id int

)

Recording information is simple and rough, so now store this structural information:

Okay, now let's move on to the answer section:

1. Inquire about Xiaotian's direct boss:

SELECT e2.eidjournal e2.ename FROM employees E1 recording e2 WHERE e1.parent_id=e2.eid AND e1.enameplate 'Xiao Tian'

two。 Inquire about the employees directly under the management of Lao Song:

SELECT e1.eidpenery e1.ename FROM employees e1 recording e2 WHERE e1.parent_id=e2.eid AND e2.enamekeeper 'Lao Song'

3. Inquire about all the bosses of Xiaotian.

There is certainly no way to check directly here. We can only use a loop to make a circular query. First check the direct boss, and then check the direct boss of the direct boss, and cycle in turn. For such troublesome things, we still have to establish a stored procedure first:

Keep your eyes open and watch carefully, and then there is the coquettish operation:

CREATE DEFINER= `root` @ `localhost` FUNCTION `getSuperiors` (`uid` int) RETURNS varchar (1000) CHARSET gb2312BEGIN DECLARE superiors VARCHAR (1000) DEFAULT''; DECLARE sTemp INTEGER DEFAULT uid; DECLARE tmpName VARCHAR (20); WHILE (sTemp > 0) DO SELECT parent_id into sTemp FROM employees where eid = sTemp; SELECT ename into tmpName FROM employees where eid = sTemp; IF (sTemp > 0) THEN SET superiors = concat (tmpName,',',superiors); END IF; END WHILE; SET superiors = LEFT (superiors,CHARACTER_LENGTH (superiors)-1); RETURN superiors;END

This stored procedure can query all the parent nodes of the child node to try it out.

All right, the coquettish operation is complete.

Obviously, this way. It is troublesome to get all the parent nodes of the child node.

4. Inquire all the employees managed by Lao Wang.

The idea is as follows: first, get the id of all employees whose parent node is Lao Wang id, then add the employee name to the result list, and then call a magic search function to make a magic search:

CREATE DEFINER= `root` @ `localhost` FUNCTION `getSubarticate` (`uid` int) RETURNS varchar (2000) CHARSET gb2312BEGIN DECLARE str varchar (1000); DECLARE cid varchar (100); DECLARE result VARCHAR (1000); DECLARE tmpName VARCHAR (100); SET str ='$'; SET cid = CAST (uid as char (10)); WHILE cid is not null DO SET str = concat (str,',', cid); SELECT group_concat (eid) INTO cid FROM employees where FIND_IN_SET (parent_id,cid); END WHILE SELECT GROUP_CONCAT (ename) INTO result FROM employees WHERE FIND_IN_SET (parent_id,str); RETURN result; END

Look at the magical results:

Although it has been done, to tell you the truth, it is not easy.

The advantage of this method is that it stores less information, it is convenient to check direct superiors and direct subordinates, and the disadvantage is that multi-level query is very laborious. So when you only need to use the direct superior-subordinate relationship, it is good to use this method, and you can save a lot of space.

The above is all the content of the article "how Mysql stores the tree structure through Adjacency List". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.