Mysql stores the tree structure through the Adjacency List (adjacent table), and the adjacency Tree Structure

Mysql stores the tree structure through the Adjacency List (adjacent table), and the adjacency Tree Structure

The following describes the process and solution for MYSQL to store the tree structure through the Adjacency List (adjacent table), and analyzes the stored legends.

Today, let's look at a headache. How can we store the tree structure in the database?

A relational database like mysql is suitable for storing flat data similar to tables. However, it is difficult to handle a deep user like a tree structure.

For example, there is a structure for storing the company's personnel. The general hierarchy is as follows:

 

(It is not easy to draw a picture ..)

So how to store this structure? Obtain the following information:

1. query the direct supervisor of Xiaotian.

2. query employees directly under the old song administration.

3. query all the superiors of Xiaotian.

4. query all employees managed by Lao Wang.

Solution 1: (Adjacency List) only stores information about the parent node of the current node.

Create table Employees (
Eid int,
Ename VARCHAR (100 ),
Position VARCHAR (100 ),
Parent_id int
)

The record information is simple and crude, so store the structure information:

Okay. Now we start to answer the question:

1. query the direct supervisor of Xiaotian:

SELECT e2.eid, e2.ename FROM employees e1, employees e2 WHERE e1.parent _ id = e2.eid AND e1.ename = 'day ';

2. query employees directly under the management of lausong:

SELECT e1.eid, e1.ename FROM employees e1, employees e2 WHERE e1.parent _ id = e2.eid AND e2.ename = 'lause ';

3. query all the superiors of Xiaotian.

There is definitely no way to directly query it here. You can only query it cyclically. First, you can check your superiors directly, and then you can check the superiors directly. In this case, you have to create a stored procedure first:

With a wide eye, you can see the following operations:

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 be used to query all the parent nodes of the child node.

 

Okay. Cool operation is complete.

Obviously, this is the case. It is difficult to obtain all the parent nodes of a child node ..

4. query all employees managed by Lao Wang.

The idea is as follows: first obtain the employee id whose parent node is Lao Wang id, add the employee name to the result list, and call a magic lookup function to perform a magic lookup:

CREATE DEFINER=`root`@`localhost` FUNCTION `getSubordinate`(`uid` int) RETURNS varchar(2000) 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 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 amazing results:

Although it was made, it is really not easy to tell the truth...

The advantage of this method is that it stores less information and is convenient for direct superiors and direct subordinates. The disadvantage is that multi-level queries are difficult. So when you only need to use the direct upper-lower-level relationship, this method is good and can save a lot of space. Other storage solutions will be introduced in the future. There are no advantages or disadvantages, but they are applicable in different scenarios.