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

Simple example of HTML text formatting (detailed explanation)

1. Text formatting: This example demonstrates how...

MySQL Series Database Design Three Paradigm Tutorial Examples

Table of contents 1. Knowledge description of the...

MySQL 5.7.18 winx64 installation and configuration method graphic tutorial

The installation of compressed packages has chang...

Implementation of building custom images with Dockerfile

Table of contents Preface Introduction to Dockerf...

Install redis and MySQL on CentOS

1|0MySQL (MariaDB) 1|11. Description MariaDB data...

7 cool dynamic website designs for inspiration

In the field of design, there are different desig...

Introduction to the use of MySQL pt-slave-restart tool

Table of contents When setting up a MySQL master-...

Management of xinetd-based services installed with RPM packages in Linux

Table of contents Preface 1. Startup management b...

Solve the problem of inconsistency between mysql time and system time in docker

Recently, when I installed MySQL in Docker, I fou...

The most common declaration merge in TS (interface merge)

Table of contents 1. Merge interface 1.1 Non-func...

Convert psd cut image to div+css format

PSD to div css web page cutting example Step 1: F...

JavaScript offsetParent case study

1. Definition of offsetParent: offsetParent is th...