Query all descendants of a person in a MySQL family table

We often place some tree structures in a table, such as genealogy and menus. The biggest problem with this type of table is that when we need to query all the sub-records of a certain record, it is difficult to use a simple SQL statement. We can write a specific process to do this. Here is an example I wrote:

The tree structure table stores genealogy, which is simple and has the following structure:

Family Tree table

 Create   Table 'People '(
'Id' Int ( 11 ) Not   Null ,
'Name' Varchar ( 50 )Null   Default   Null ,
'Pid' Int ( 11 ) Not   Null   Default   '  0  ' ,
 Primary   Key ('Id ')
);

The process of querying all future generations is as follows:

Query descendant Process

 Create Definer = 'Root '@' % ' Procedure 'Getchildren '( In 'Parentid' Int )
Language SQL
 Not Deterministic
 Contains SQL
SQL Security definer
Comment ' Get all future generations of a person  ' 
 Begin 
# Temporary tables for storing results
 Drop   Table   If   Exists Children;
 Create   Temporary   Table Children Select   0 Plevel, P. *   From 'Others' P Where ID = Parentid;
# Temporary tables that store intermediate results
 Drop   Table   If   Exists TEM;
 Create   Temporary   Table TEM Select ID From 'Others' limit 0 ;

# Step-by-step child population Filling
 Set   @ Plevel  =  1 ;
Repeat
# Clearing the last data
 Truncate   Table TEM;
# Add the descendant ID of the current level to the temporary table
 Insert   Into TEM Select P. ID From 'Others' P, children C
 Where P. PID = C. ID And C. plevel = ( @ Plevel  -  1 );
# Insert the child data of the current level into the result temporary table
 Insert   Into Children Select   @ Plevel Plevel, P. *   From 'Others' P, TEM t
Where P. ID = T. ID;
 Set   @ Plevel  =  @ Plevel  +  1 ;
Until Not   Exists ( Select   *   From TEM) Or   @ Plevel  >   10 
 End Repeat;

# Adjust the table structure and delete temporary columns and unnecessary data
 Alter   Table Children Drop   Column Plevel;
 Delete   From Children Where ID = Parentid;

# Returned results
Select   *   From Children;
 End 

Description: The above method is only suitable for scenarios where tables are relatively small and performance requirements are not high, such as storing menus on a certain interface or blocking relationships between departments with a small number of users. If the table is large and the performance overhead is large, it may be very slow. In this case, it is best to create a redundant table and store a record for all the parent-child relationships, such a simple SQL statement can query all the results.