CREATE TABLE `area` ( `area_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '地區(qū)ID', `name` varchar(40) NOT NULL DEFAULT 'unkonw' COMMENT '地區(qū)名稱', `area_code` varchar(10) NOT NULL DEFAULT 'unkonw' COMMENT '地區(qū)編碼', `pid` int(11) DEFAULT NULL COMMENT '父id', `left_num` mediumint(8) unsigned NOT NULL COMMENT '節(jié)點(diǎn)左值', `right_num` mediumint(8) unsigned NOT NULL COMMENT '節(jié)點(diǎn)右值', PRIMARY KEY (`area_id`), KEY `idx$area$pid` (`pid`), KEY `idx$area$left_num` (`left_num`), KEY `idx$area$right_num` (`right_num`))

SELECT * FROM area WHERE name LIKE '%廣州%';+---------+-----------+-----------+------+----------+-----------+| area_id | name   | area_code | pid | left_num | right_num |+---------+-----------+-----------+------+----------+-----------+|  2148 | 廣州市  | 440100  | 2147 |   2879 |   2904 |+---------+-----------+-----------+------+----------+-----------+

SELECT c.* FROM area AS p, area AS cWHERE 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 | 廣州市  | 440100  | 2147 |   2879 |   2904 ||  2161 | 從化市  | 440184  | 2148 |   2880 |   2881 ||  2160 | 增城市  | 440183  | 2148 |   2882 |   2883 ||  2159 | 花都區(qū)  | 440114  | 2148 |   2884 |   2885 ||  2158 | 番禺區(qū)  | 440113  | 2148 |   2886 |   2887 ||  2157 | 黃埔區(qū)  | 440112  | 2148 |   2888 |   2889 ||  2156 | 白云區(qū)  | 440111  | 2148 |   2890 |   2891 ||  2154 | 天河區(qū)  | 440106  | 2148 |   2892 |   2893 ||  2153 | 海珠區(qū)  | 440105  | 2148 |   2894 |   2895 ||  2152 | 越秀區(qū)  | 440104  | 2148 |   2896 |   2897 ||  2151 | 荔灣區(qū)  | 440103  | 2148 |   2898 |   2899 ||  2150 | 東山區(qū)  | 230406  | 2148 |   2900 |   2901 ||  2149 | 其它區(qū)  | 440189  | 2148 |   2902 |   2903 |+---------+-----------+-----------+------+----------+-----------+

SELECT sub_child.area_id, (COUNT(sub_parent.name) - 1) AS depth, CONCAT(REPEAT(' ', (COUNT(sub_parent.name) - 1)), sub_child.name) AS nameFROM ( SELECT child.*  FROM area 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 child WHERE child.left_num BETWEEN parent.left_num AND parent.right_num  AND parent.area_id = 2148) AS sub_parentWHERE sub_child.left_num BETWEEN sub_parent.left_num AND sub_parent.right_numGROUP BY sub_child.area_idORDER BY sub_child.left_num;+---------+-------------+-------+| area_id | name    | depth |+---------+-------------+-------+|  2148 | 廣州市   |   0 ||  2161 |  從化市  |   1 ||  2160 |  增城市  |   1 ||  2159 |  花都區(qū)  |   1 ||  2158 |  番禺區(qū)  |   1 ||  2157 |  黃埔區(qū)  |   1 ||  2156 |  白云區(qū)  |   1 ||  2154 |  天河區(qū)  |   1 ||  2153 |  海珠區(qū)  |   1 ||  2152 |  越秀區(qū)  |   1 ||  2151 |  荔灣區(qū)  |   1 ||  2150 |  東山區(qū)  |   1 ||  2149 |  其它區(qū)  |   1 |+---------+-------------+-------+

SELECT p.* FROM area AS p, area AS cWHERE 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 | 廣東省  | 440000  |  0 |   2580 |   2905 ||  2148 | 廣州市  | 440100  | 2147 |   2879 |   2904 ||  3611 | 中國   | 100000  |  -1 |    1 |   7218 |+---------+-----------+-----------+------+----------+-----------+

-- 更新左右值UPDATE area SET left_num = left_num + 2 WHERE left_num > 2879;UPDATE area SET right_num = right_num + 2 WHERE right_num > 2879; -- 插入 '南沙區(qū)' 信息INSERT INTO areaSELECT NULL, '南沙區(qū)', '440115', 2148, left_num + 1, left_num + 2FROM area WHERE area_id = 2148; -- 查看是否滿足要求SELECT c.* FROM area AS p, area AS cWHERE 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 | 廣州市  | 440100  | 2147 |   2879 |   2906 ||  3612 | 南沙區(qū)  | 440115  | 2148 |   2880 |   2881 ||  2161 | 從化市  | 440184  | 2148 |   2882 |   2883 ||  2160 | 增城市  | 440183  | 2148 |   2884 |   2885 ||  2159 | 花都區(qū)  | 440114  | 2148 |   2886 |   2887 ||  2158 | 番禺區(qū)  | 440113  | 2148 |   2888 |   2889 ||  2157 | 黃埔區(qū)  | 440112  | 2148 |   2890 |   2891 ||  2156 | 白云區(qū)  | 440111  | 2148 |   2892 |   2893 ||  2154 | 天河區(qū)  | 440106  | 2148 |   2894 |   2895 ||  2153 | 海珠區(qū)  | 440105  | 2148 |   2896 |   2897 ||  2152 | 越秀區(qū)  | 440104  | 2148 |   2898 |   2899 ||  2151 | 荔灣區(qū)  | 440103  | 2148 |   2900 |   2901 ||  2150 | 東山區(qū)  | 230406  | 2148 |   2902 |   2903 ||  2149 | 其它區(qū)  | 440189  | 2148 |   2904 |   2905 |+---------+-----------+-----------+------+----------+-----------+