Mysql Multi-level structure-area table using tree detailed _mysql

1.1. Foreword

We have probably introduced the basic use of the tree structure table before. There are several concepts in our project that are useful to many levels. Below we are all familiar with the regional table to do the demo.
1.2. Table structure and data

The basic structure of the area table may include other fields in your project. This way I only show the fields we care about:

CREATE TABLE ' area ' (
 ' area_id ' int (one) not NULL Auto_increment COMMENT ' region ID ',
 ' name ' varchar ' NOT NULL Defaul  T ' unkonw ' COMMENT ' area name ',
 ' area_code ' varchar (+) not NULL default ' unkonw ' COMMENT ' region code ',
 ' pid ' int (one) default Null COMMENT ' parent ID ',
 ' Left_num ' mediumint (8) unsigned not NULL COMMENT ' node left value ',
 ' Right_num ' mediumint (8) unsigned N OT NULL COMMENT ' node right value ',
 PRIMARY key (' area_id '),
 key ' Idx$area$pid ' (' pid '),
 key ' Idx$area$left_num ' (' Left_num '),
 KEY ' idx$area$right_num ' (' Right_num ')

Regional table data: Area
Import to test table

Mysql-uroot-proot Test < Area.sql

1.1. The basic operation of the regional table

See ' Guangzhou ' For information

SELECT * from area WHERE name is like '% Guangzhou ';
+---------+-----------+-----------+------+----------+-----------+
| area_id | name   | area_code | pid | left_ num | Right_num |
+---------+-----------+-----------+------+----------+-----------+
|  2148 | Guangzhou  | 440100 |  2147   | 2879 |   2904 |
+---------+-----------+-----------+------+----------+-----------+

View ' Guangzhou ' all children

SELECT c.* from area as P, area as C WHERE c.left_num BETWEEN P.left_num and p.right_num and p.area_id = 2148; +---------+-----------+-----------+------+----------+-----------+
| area_id | name | Area_code | PID | Left_num |
Right_num |  +---------+-----------+-----------+------+----------+-----------+
| 2148 | Guangzhou | 440100 |   2147 |   2879 |  2904 | | 2161 | Conghua | 440184 |   2148 |   2880 |  2881 | | 2160 | Zengcheng | 440183 |   2148 |   2882 |  2883 | | 2159 | Huadou District | 440114 |   2148 |   2884 |  2885 | | 2158 | Fanyu District | 440113 |   2148 |   2886 |  2887 | | 2157 | Huangpu District | 440112 |   2148 |   2888 |  2889 | | 2156 | Baiyun District | 440111 |   2148 |   2890 |  2891 | | 2154 | Tianhe District | 440106 |   2148 |   2892 |  2893 | | 2153 | Haizhu District | 440105 |   2148 |   2894 |  2895 | | 2152 | Yuexiu District | 440104 |   2148 |   2896 |  2897 | | 2151 | Liwan District | 440103 |   2148 |   2898 |  2899 | | 2150 | Dongshan District | 230406 |   2148 |   2900 |  2901 | | 2149 | Other areas | 440189 |   2148 |   2902 |
2903 | +---------+-----------+-----------+------+----------+-----------+ 

View all children and depth in ' Guangzhou ' and show hierarchical relationships

SELECT sub_child.area_id, (Count (sub_parent.name)-1) as depth, CONCAT (REPEAT ("", (count (sub_parent.name)-1)), sub_ Child.name as name from (SELECT child.* to as parent, area as child WHERE Child.left_num BETWEEN Parent.left_ Num and parent.right_num and parent.area_id = 2148) as Sub_child, (SELECT child.* from area as parent, area as Ch ILD where Child.left_num BETWEEN parent.left_num and parent.right_num and parent.area_id = 2148) as Sub_parent where s Ub_child.left_num BETWEEN Sub_parent.left_num and Sub_parent.right_num GROUP by sub_child.area_id
Child.left_num; +---------+-------------+-------+
| area_id | name |
Depth |  +---------+-------------+-------+
| 2148 |   Guangzhou |  0 | |  2161 |   Conghua |  1 | |  2160 |   Zengcheng |  1 | |  2159 |   Huadou District |  1 | |  2158 |   Fanyu District |  1 | |  2157 |   Huangpu District |  1 | |  2156 |   Baiyun District |  1 | |  2154 |   Tianhe District |  1 | |  2153 |   Haizhu District |  1 | |  2152 |   Yuexiu District |  1 | |  2151 |   Liwan District |  1 | |  2150 |   Dongshan District | 1 |
|  2149 |   Other areas |
1 | +---------+-------------+-------+

Direct ancestors showing ' Guangzhou ' (including themselves)

SELECT p.* 
from area as P, area as C
WHERE c.left_num BETWEEN p.left_num and p.right_num and
 c.area_id = 2148;
+---------+-----------+-----------+------+----------+-----------+
| area_id | Name   | area_code | pid | left_num | Right_num
| +---------+-----------+-----------+------+----------+-----------+
|  2147 | Guangdong Province  | 440000  |  0 |   2580 |   2905 |
|  2148 | Guangzhou  | 440100 |  2147   | 2879 |   2904 |
|  3611 | China   | 100000  |  -1 |    1 |   7218 |
+---------+-----------+-----------+------+----------+-----------+

To ' Guangzhou ' insert a region ' South Sandy area '

--Update the left and right values update area SET left_num = left_num + 2 WHERE left_num > 2879;
 
UPDATE area SET right_num = right_num + 2 WHERE right_num > 2879; --Insert ' Nansha ' information insert into the area SELECT NULL, ' Nansha ', ' 440115 ', 2148, Left_num + 1, Left_num + 2 from the area WHERE area_id = 2
 
148; --See if the request is satisfied SELECT c.* from the area as P, the area as C WHERE c.left_num BETWEEN P.left_num and p.right_num and p.area_id = 214
8; +---------+-----------+-----------+------+----------+-----------+
| area_id | name | Area_code | PID | Left_num |
Right_num |  +---------+-----------+-----------+------+----------+-----------+
| 2148 | Guangzhou | 440100 |   2147 |   2879 |  2906 | | 3612 | South Sandy Area | 440115 |   2148 |   2880 |  2881 | | 2161 | Conghua | 440184 |   2148 |   2882 |  2883 | | 2160 | Zengcheng | 440183 |   2148 |   2884 |  2885 | | 2159 | Huadou District | 440114 |   2148 |   2886 |  2887 | | 2158 | Fanyu District | 440113 |   2148 |   2888 |  2889 | | 2157 | Huangpu District | 440112 |   2148 |   2890 |  2891 | | 2156 | Baiyun District | 440111 |   2148 | 2892|  2893 | | 2154 | Tianhe District | 440106 |   2148 |   2894 |  2895 | | 2153 | Haizhu District | 440105 |   2148 |   2896 |  2897 | | 2152 | Yuexiu District | 440104 |   2148 |   2898 |  2899 | | 2151 | Liwan District | 440103 |   2148 |   2900 |  2901 | | 2150 | Dongshan District | 230406 |   2148 |   2902 |  2903 | | 2149 | Other areas | 440189 |   2148 |   2904 |
2905 | +---------+-----------+-----------+------+----------+-----------+