"MySQL troubleshooter" How to store a tree structure in a database (scenario two Path enumeration)

Today, we introduce the second method of storing tree structure into database--Path enumeration method.

Or borrow the last one of the chestnuts, in order to facilitate the inspection, I took the picture and moved over.

There are still a few questions to answer:

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.

The Path enumeration path enumeration method, which records the paths of the root node to each child node.

Create the table first:

CREATE TABLE employees2 (eid int,ename varchar, Position varchar), Path VARCHAR ($))

Then insert the data:

Now let's answer the previous question:

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

What can be done easily in the previous solution is a bit cumbersome in this scenario because the path field needs to be string-processed, and the "/" + ID is the path value of the direct boss. Started a nasty operation again:

SELECT E1.eid,e1.ename from Employees2 e1,employees2 e2 WHERE e2.ename = ' small Day ' and E1.path = REPLACE (E2.path,concat ('/'), E2. Eid), ');

As if this operation is not enough sao,2333, the result is as follows:

　　

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

How to check the direct staff under the management? Then we need to use fuzzy query:

SELECT E2.eid,e2.ename from Employees2 e1,employees2 e2 WHERE e1.ename = ' Lao song ' and E2.path REGEXP CONCAT (E1.path, '/[0-9]{1, }$ ');

A regular match is used here to match all of the path compliance records, and the results are as follows:

　　

3. Check all the bosses of the small day.

SELECT E1.eid,e1.ename from Employees2 e1,employees2 E2 WHERE e2.ename= ' small Day ' and E2.path like Concat (E1.path, '/% ');

The advantages of this storage structure can be demonstrated here. If you don't look at efficiency, it's very convenient.

　　

4. Check all the employees that Lao Wang manages.

SELECT E2.eid,e2.ename from Employees2 e1,employees2 E2 WHERE e1.ename= ' Lao Wang ' and E2.path like Concat (E1.path, '/% ');

Look, it's easy to find out.

　　

Instead of writing a large stored procedure like before, it's simply rude.

Summary, the way to store the path in the multi-level query is very convenient, and in the query directly subordinate to a slightly more complex point. There is also a clear drawback, that is, the size of the path is specified, so theoretically it is not possible to carry out an infinite level of storage, the larger the path value is set, the more wasted space.

At this point, this article is complete, and then will introduce other methods, you are welcome to continue to pay attention!

"MySQL troubleshooter" How to store a tree structure in a database (scenario two Path enumeration)