Mysql stores tree structure through Adjacency List (adjacency list)

The following content introduces the process and solution of MYSQL using Adjacency List to store tree structure, and analyzes the stored diagram.

Today, let’s take a look at a more troublesome problem: how to store a tree structure in a database?

Relational databases like MySQL are more suitable for storing flat data like tables, but they are difficult to control when encountering deep data like tree structures.

For example, we need to store the company's personnel structure. The general hierarchical structure is as follows:

(Drawing a picture is not easy..)

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

1. Find out who Xiaotian’s direct supervisor is.

2. Check the direct employees under Lao Song’s management.

3. Check all of Xiaotian’s superiors.

4. Query all employees managed by Lao Wang.

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

CREATE TABLE Employees(
eid int,
ename VARCHAR(100),
position VARCHAR(100),
parent_id int
)

The recorded information is simple and crude, so now store this structure information:

OK, now let’s get into the answering part:

1. Query Xiaotian’s direct supervisor:

SELECT e2.eid,e2.ename FROM employees e1,employees e2 WHERE e1.parent_id=e2.eid AND e1.ename='小天';

2. Query the direct employees under Lao Song's management:

SELECT e1.eid,e1.ename FROM employees e1,employees e2 WHERE e1.parent_id=e2.eid AND e2.ename='老宋';

3. Check all of Xiaotian’s superiors.

There is definitely no way to check directly here. You can only use a loop to query in a loop. First check the direct supervisor, then check the direct supervisor of the direct supervisor, and so on. For such a troublesome thing, you still have to create a stored procedure first:

Open your eyes and watch carefully, the next step is the show operation:

CREATE DEFINER=`root`@`localhost` FUNCTION `getSuperiors`(`uid` int) RETURNS varchar(1000) CHARSET gb2312
BEGIN
  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 parent nodes of child nodes. Let's try it out.

Okay, the operation is complete.

Apparently, this. It is troublesome to get 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 IDs of all employees whose parent node is Lao Wang's ID, then add the employee's name to the result list, and then call a magical search function to perform a magical search:

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

See the amazing results:

Although I have figured it out, to be honest, it was not easy. . .

The advantage of this method is that it stores less information and is very convenient for checking direct superiors and direct subordinates. The disadvantage is that it is very difficult to perform multi-level queries. Therefore, when you only need to use the direct superior-subordinate relationship, this method is still a good choice and can save a lot of space. Other storage solutions will be introduced later. There is no absolute distinction between good and bad, they are just suitable for different occasions.