MySQL多层级结构-区域表使用树详解

1.1. 前言

前面我们大概介绍了一下树结构表的基本使用。在我们项目中有好几块有用到多层级的概念。下面我们哪大家都比较熟悉的区域表来做演示。
1.2. 表结构和数据

区域表基本结构,可能在你的项目中还有包含其他字段。这边我只展示我们关心的字段:

CREATE TABLE `area` ( `area_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '地区ID', `name` varchar(40) NOT NULL DEFAULT 'unkonw' COMMENT '地区名称', `area_code` varchar(10) NOT NULL DEFAULT 'unkonw' COMMENT '地区编码', `pid` int(11) DEFAULT NULL COMMENT '父id', `left_num` mediumint(8) unsigned NOT NULL COMMENT '节点左值', `right_num` mediumint(8) unsigned NOT NULL COMMENT '节点右值', PRIMARY KEY (`area_id`), KEY `idx$area$pid` (`pid`), KEY `idx$area$left_num` (`left_num`), KEY `idx$area$right_num` (`right_num`))

区域表数据: area
导入到test表

mysql/' target='_blank'>mysql -uroot -proot test < area.sql

1.1. 区域表的基本操作

查看 '广州' 的相关信息

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 | 花都区  | 440114  | 2148 |   2884 |   2885 ||  2158 | 番禺区  | 440113  | 2148 |   2886 |   2887 ||  2157 | 黄埔区  | 440112  | 2148 |   2888 |   2889 ||  2156 | 白云区  | 440111  | 2148 |   2890 |   2891 ||  2154 | 天河区  | 440106  | 2148 |   2892 |   2893 ||  2153 | 海珠区  | 440105  | 2148 |   2894 |   2895 ||  2152 | 越秀区  | 440104  | 2148 |   2896 |   2897 ||  2151 | 荔湾区  | 440103  | 2148 |   2898 |   2899 ||  2150 | 东山区  | 230406  | 2148 |   2900 |   2901 ||  2149 | 其它区  | 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 |  花都区  |   1 ||  2158 |  番禺区  |   1 ||  2157 |  黄埔区  |   1 ||  2156 |  白云区  |   1 ||  2154 |  天河区  |   1 ||  2153 |  海珠区  |   1 ||  2152 |  越秀区  |   1 ||  2151 |  荔湾区  |   1 ||  2150 |  东山区  |   1 ||  2149 |  其它区  |   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; -- 插入 '南沙区' 信息INSERT INTO areaSELECT NULL, '南沙区', '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 | 南沙区  | 440115  | 2148 |   2880 |   2881 ||  2161 | 从化市  | 440184  | 2148 |   2882 |   2883 ||  2160 | 增城市  | 440183  | 2148 |   2884 |   2885 ||  2159 | 花都区  | 440114  | 2148 |   2886 |   2887 ||  2158 | 番禺区  | 440113  | 2148 |   2888 |   2889 ||  2157 | 黄埔区  | 440112  | 2148 |   2890 |   2891 ||  2156 | 白云区  | 440111  | 2148 |   2892 |   2893 ||  2154 | 天河区  | 440106  | 2148 |   2894 |   2895 ||  2153 | 海珠区  | 440105  | 2148 |   2896 |   2897 ||  2152 | 越秀区  | 440104  | 2148 |   2898 |   2899 ||  2151 | 荔湾区  | 440103  | 2148 |   2900 |   2901 ||  2150 | 东山区  | 230406  | 2148 |   2902 |   2903 ||  2149 | 其它区  | 440189  | 2148 |   2904 |   2905 |+---------+-----------+-----------+------+----------+-----------+