Mysql stores tree structure through Adjacency List (adjacency list)

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.

You may also be interested in:
  • Mysql tree-structured database table design

<<:  Summary of 4 ways to add users to groups in Linux

>>:  Vue realizes price calendar effect

Recommend

Detailed examples of how to use the box-shadow property in CSS3

There are many attributes in CSS. Some attributes...

Docker beginners' first exploration of common commands practice records

Before officially using Docker, let's first f...

MySQL startup error InnoDB: Unable to lock/ibdata1 error

An error message appears when MySQL is started in...

Chrome monitors cookie changes and assigns values

The following code introduces Chrome's monito...

How to use the MySQL authorization command grant

The examples in this article run on MySQL 5.0 and...

Implementation of Vue single file component

I recently read about vue. I found a single-file ...

Use of SerialPort module in Node.js

Table of contents Purpose Module Installation Bas...

MySQL tutorial thoroughly understands stored procedures

Table of contents 1. Concepts related to stored p...

JavaScript to achieve the effect of clicking on the self-made menu

This article shares the specific code of JavaScri...

10 content-related principles to improve website performance

<br />English address: http://developer.yaho...

Deployment and Chinese translation of the docker visualization tool Portainer

#docker search #docker pull portainer 1. Download...

How to add Nginx proxy configuration to allow only internal IP access

location / { index index.jsp; proxy_next_upstream...

Detailed steps to configure my.ini for mysql5.7 and above

There is no data directory, my-default.ini and my...

A brief discussion on React native APP updates

Table of contents App Update Process Rough flow c...