MySQL多层级结构-区域表使用树详解
1.1.前言
前面我们大概介绍了一下树结构表的基本使用。在我们项目中有好几块有用到多层级的概念。下面我们哪大家都比较熟悉的区域表来做演示。
1.2.表结构和数据
区域表基本结构,可能在你的项目中还有包含其他字段。这边我只展示我们关心的字段:
CREATETABLE`area`( `area_id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'地区ID', `name`varchar(40)NOTNULLDEFAULT'unkonw'COMMENT'地区名称', `area_code`varchar(10)NOTNULLDEFAULT'unkonw'COMMENT'地区编码', `pid`int(11)DEFAULTNULLCOMMENT'父id', `left_num`mediumint(8)unsignedNOTNULLCOMMENT'节点左值', `right_num`mediumint(8)unsignedNOTNULLCOMMENT'节点右值', PRIMARYKEY(`area_id`), KEY`idx$area$pid`(`pid`), KEY`idx$area$left_num`(`left_num`), KEY`idx$area$right_num`(`right_num`) )
区域表数据:area
导入到test表
mysql-uroot-proottest<area.sql
1.1.区域表的基本操作
查看'广州'的相关信息
SELECT*FROMareaWHEREnameLIKE'%广州%'; +---------+-----------+-----------+------+----------+-----------+ |area_id|name|area_code|pid|left_num|right_num| +---------+-----------+-----------+------+----------+-----------+ |2148|广州市|440100|2147|2879|2904| +---------+-----------+-----------+------+----------+-----------+
查看'广州'所有孩子
SELECTc.* FROMareaASp,areaASc WHEREc.left_numBETWEENp.left_numANDp.right_num ANDp.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| +---------+-----------+-----------+------+----------+-----------+
查看'广州'所有孩子和深度并显示层级关系
SELECTsub_child.area_id,
(COUNT(sub_parent.name)-1)ASdepth,
CONCAT(REPEAT('',(COUNT(sub_parent.name)-1)),sub_child.name)ASname
FROM(
SELECTchild.*
FROMareaASparent,areaASchild
WHEREchild.left_numBETWEENparent.left_numANDparent.right_num
ANDparent.area_id=2148
)ASsub_child,(
SELECTchild.*
FROMareaASparent,areaASchild
WHEREchild.left_numBETWEENparent.left_numANDparent.right_num
ANDparent.area_id=2148
)ASsub_parent
WHEREsub_child.left_numBETWEENsub_parent.left_numANDsub_parent.right_num
GROUPBYsub_child.area_id
ORDERBYsub_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|
+---------+-------------+-------+
显示'广州'的直系祖先(包括自己)
SELECTp.* FROMareaASp,areaASc WHEREc.left_numBETWEENp.left_numANDp.right_num ANDc.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| +---------+-----------+-----------+------+----------+-----------+
向'广州'插入一个地区'南沙区'
--更新左右值 UPDATEareaSETleft_num=left_num+2WHEREleft_num>2879; UPDATEareaSETright_num=right_num+2WHEREright_num>2879; --插入'南沙区'信息 INSERTINTOarea SELECTNULL,'南沙区','440115',2148,left_num+1,left_num+2 FROMareaWHEREarea_id=2148; --查看是否满足要求 SELECTc.* FROMareaASp,areaASc WHEREc.left_numBETWEENp.left_numANDp.right_num ANDp.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| +---------+-----------+-----------+------+----------+-----------+