web的一些设计

MySQL树形结构设计

树形数据举例

表名t_manage_group,数据示例如下:

id pid path node_name node_type node_val
1 / 公司 2 2001
2 1 /1/ 王某公司 2 2002
3 2 /1/2 王XX负责人 3 3001
4 / 郫县打样店铺 2 2003
5 4 /4/ 李XX店长 3 3002
6 4 /4/ 值班人员1 3 3003
7 / 代理商负责 1
8 7 /7/ 代理商 2 2008
9 8 /7/8/ XX公司 1
10 9 /7/8/9/ 张XX业务员 2 2010

字段说明:

  • id:当前记录的id
  • pid:当前记录的上级id(parent id的缩写)。
  • path:当前记录的路径(由id通过斜杠分隔组成,如:/1/2/)
  • node_name: 当前记录的名称 。
  • node_type: 当前记录中node_val的类型:1,仅名称(不需引用其它表);2,公司(node_val是其它表的id);3,人员(node_val是其它表的id)。
  • node_val: 当前记录需要关联到其它表时,使用的id。
--查询某个节点的直接下级,pid=xx就是id=xx的直接下级
select * from t_manage_group where pid=xx;

--查询某个节点的所有下级
select * from t_manage_group where path like "/xx/xx/%";

--查询某个节点的所有上级
select * from t_manage_group where path like "%/xx/xx/";

--查询某个节点的直接下级,并且类型为业务员(node_type=2)
select * from t_manage_group where pid=xx and node_type=2;

为什么这样设计

  • 顶层节点没有父节点id,填什么呢?不填,留空。
  • 顶层节点的路径(path),填什么?填“/”。
  • 有了pid字段,为什么还要路径(path)字段?用path字段,是为了方便查找某个节点的,所有直接上级或所有直接下级。
  • 路径(path)字段,为什么设计成以”/“开始也以”/“结尾?举个例就明白了:如果不以”/“结尾,查找”/1/10”,结果”/1/1000”也会被命中,因为前面部分是一样的。
  • 这种树形结构设计单独用一张表,这样有什么好处?这样的好处是,要用树形时,加一张表,不用时去掉这张表,不影响其它功能。
  • 网上有很多树形结构,直接用id和pid来实现,没有用path,我加了path是为什么?为了直接使用path来找出上下级关系,数据库语句简单易懂,详见上在的sql查询示例。
本作品采用《CC 协议》,转载必须注明作者和本文链接
Luson
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 3

楼主可以看下 laravel-nestedset 挺好用的。

3年前 评论
Luson

我们公司的老系统,纯手工打造,没有框架

3年前 评论
Luson (作者) (楼主) 3年前
paco 3年前
youweiweb 3年前

树形结构,加path和不加path不仅仅是SQL语句的复杂程度。这个其实是时间和空间的权衡:加了path,空间上会多用一丢丢,但是时间上(避免了递归查询)会快很多;不加path节省一丢丢空间,但是查询则需要递归查询才能查到所有上级 or 所有下级

PS:或许会有人扯形如 path LIKE '%/10/20/' 无法命中索引,导致全表扫描,不一定会比递归快等等,我只能说,控制变量法分析问题嘛

树形数据量真的多到需要靠索引优化,我相信除了关系型DB,还有更好的DB选择的,对吧?

3年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!