"MySQL troubleshooter" How to store a tree structure in a database (scenario one, adjacency list)

Today, let's look at a more difficult question, how to store the tree structure in the database?

Relational databases like MySQL are better suited for storing flat data like tables, but it's hard to navigate people with depth like a tree structure.

Give me a chestnut: now there is a structure of people to store the company, the approximate hierarchy is as follows:

(It's not easy to draw a picture.) ）

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

1. Check with your immediate boss for a small day.

2. Query the immediate staff under the management of Lao song.

3. Check all the bosses of the small day.

4. Check all the employees that Lao Wang manages.

　　

Scenario One, (adjacency List) stores only the parent node information for the current node.

CREATE TABLE Employees (
Eid int,
ename VARCHAR (100),
Position VARCHAR (100),
parent_id int
)

Record information simple and rude, so now store this structure information:

　　

OK, now get to the answer link:

1. Query the direct boss of the small day:

SELECT E2.eid,e2.ename from Employees e1,employees E2 WHERE E1.parent_id=e2.eid and e1.ename= ' small Day ';

　　

2. Query the immediate staff under the management of Lao song:

SELECT E1.eid,e1.ename from Employees e1,employees E2 WHERE E1.parent_id=e2.eid and E2.ename= ' Lao song ';

　　

3. Check all the bosses of the small day.

Here must not directly check, can only be used to cycle the query, first check the direct boss, and then check direct boss Direct boss, in turn, the trouble, or the first to establish a stored procedure:

Open your eyes and look carefully, followed by the SAO operation Link:

 CREATE definer= ' root ' @ ' localhost ' FUNCTION ' getsuperiors ' (' uid ' int ) RETURNS varchar (1000) DEFAULT    ";    DECLARE stemp INTEGER DEFAULT uid;    DECLARE tmpname VARCHAR ( 20 >0) do SELECT parent_id to stemp from employees where Eid = stemp;        Select Ename to Tmpname from employees where Eid  = stemp; IF (stemp  >0 = concat (Tmpname, ', ' ,superiors);    END IF;        END while;    SET Superiors  = Left (superiors,character_length (superiors) -1 

This section of the stored procedure can query all the parent nodes of a child node to test

Okay, the SAO operation is complete.

Obviously, so. It is troublesome to get all the parent nodes of a child node.

4. Check all the employees that Lao Wang manages.

The idea is as follows: First get all the parents of the old king ID of the employee ID, and then add the employee name to the results list, in the call of a magical search function, can be a magical search:

CREATE definer= ' root ' @ ' localhost ' FUNCTION ' getsubordinate ' (' uid ' int) RETURNS varchar ($) CHARSET gb2312
BEGIN
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 was not null do

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 came out, but to tell the truth, it is not easy ...

The advantage of this method is that less information is stored, check direct boss and direct subordinate when very convenient, the drawback is multi-level query time is very laborious. So when only need to use direct and subordinate relations, with this method is still good, can save a lot of space. The following will also introduce other storage solutions, and there is no absolute advantages and disadvantages of the application of different occasions.

This article concludes, welcome to continue to pay attention to.

"MySQL troubleshooter" How to store a tree structure in a database (scenario one, adjacency list)