“无限极”分类数据表设计的简单再总结

最新推荐文章于 2024-03-04 19:02:21 发布
最新推荐文章于 2024-03-04 19:02:21 发布
阅读量2.6k 收藏 1
点赞数
分类专栏： php
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zyu67/article/details/47355285
版权

前言：项目中又要用到一个四级分类数据表，之前我曾经在这方面按步就班玩过不少CRUD的操作，感觉工作内容有不少重复，有必要再总结一下，对新手可能会有点帮助，同时以备自己日后再用。

 

1、数据表设计

开门见山，Category表设计如下：

Category

数据表字段简单说明：

列名数据类型默认值备注
Idint 自增主键
Namevarchar(256) 分类类别名称
ParentIdint0父母分类Id
Depthint1深度，从1递增
Statusint0状态：0禁用，1启用
Priorityint0优先级，越大，同级显示的时候越靠前

说明：在设计实现这个数据表之前，我搜索参考并比较了一下其他无限层级设计方案，比如这一篇和这一篇，虽然本文最终使用了最常见的层级设计而没有采纳另外的几种方法，但是不可否认它们对开阔设计思路是很有启发的。

 

2、简单查询

（1）通常，在实际应用中简单查询某一级别可用（Status等于1）的分类非常简单：

1
2
3
4
5
6
7
8
SELECT [Id]
       ,[ Name ]
       ,[ParentId]
       ,[Depth]
       ,[Status]
       ,[Priority]
   FROM [Category](NOLOCK)
   WHERE Status=1 AND Depth=n --n>=1

最后按照优先级（Priority）字段逆序即可。

（2）当需要按照某一个Id查找它及它的所有子级或者父级成员，避开递归，直接写sql查询会比较难以下手，而且Sql Server2005之前的版本还需要用到临时表，处理起来不是那么直观。自从Sql Server2005/2008横空出世，利用With语句可用非常轻松地写出查询，下面贴两个开发中经常用到的查询存储过程（Sql Server2005/2008支持）：

a、按照某一个Id查询它及它的所有子级成员存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE PROCEDURE [dbo].[sp_GetChildCategories] (@Id int )
AS
BEGIN
WITH Record AS (
     SELECT
     Id,
     Name ,
     ParentId,
     Depth,
     Status,
     Priority
FROM
     Category(NOLOCK)
     WHERE Id=@Id
     UNION ALL
         SELECT
     a.Id Id,
     a. Name Name ,
     a.ParentId ParentId,
     a.Depth Depth,
     a.Status Status,
     a.Priority Priority
FROM
     Category(NOLOCK) a JOIN Record b
     ON a.ParentId=b.Id
)
 
SELECT
     Id,
     Name ,
     ParentId,
     Depth,
     Status,
     Priority
FROM
     Record
     WHERE Status=1
     ORDER BY Priority DESC
     
END

b、按照某一个Id查询它及它的所有父级成员存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE PROCEDURE [dbo].[sp_GetParentCategories] (@Id int )
AS
BEGIN
WITH Record AS (
     SELECT
     Id,
     Name ,
     ParentId,
     Depth,
     Status,
     Priority
FROM
     Category(NOLOCK)
     WHERE Id=@Id
     UNION ALL
     SELECT
     a.Id Id,
     a. Name Name ,
     a.ParentId ParentId,
     a.Depth Depth,
     a.Status Status,
     a.Priority Priority
FROM
     Category(NOLOCK) a JOIN Record b
     ON a.Id=b.ParentId
)
 
SELECT
     Id,
     Name ,
     ParentId,
     Depth,
     Status,
     Priority
FROM
     Record
     WHERE Status=1
     ORDER BY Priority DESC
     
END

分析上面两个存储过程，实际上，您也可以提取出下面的两段sql语句直接代替上面的查询存储过程：

c、按照某一个Id查询它及它的所有子级成员sql语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WITH Record AS (
     SELECT
     Id,
     Name ,
     ParentId,
     Depth,
     Status,
     Priority
FROM
     Category(NOLOCK)
     WHERE Id=@Id  --@Id是外部传入的参数
     UNION ALL
     SELECT
     a.Id Id,
     a. Name Name ,
     a.ParentId ParentId,
     a.Depth Depth,
     a.Status Status,
     a.Priority Priority
FROM
     Category(NOLOCK) a JOIN Record b
     ON a.ParentId=b.Id
)
 
SELECT
     Id,
     Name ,
     ParentId,
     Depth,
     Status,
     Priority
FROM
     Record
     WHERE Status=1
     ORDER BY Priority DESC

 

d、按照某一个Id查询它及它的所有父级成员sql语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WITH Record AS (
     SELECT
     Id,
     Name ,
     ParentId,
     Depth,
     Status,
     Priority
FROM
     Category(NOLOCK)
     WHERE Id=@Id  --@Id是外部传入的参数
     UNION ALL
     SELECT
     a.Id Id,
     a. Name Name ,
     a.ParentId ParentId,
     a.Depth Depth,
     a.Status Status,
     a.Priority Priority
FROM
     Category(NOLOCK) a JOIN Record b
     ON a.Id=b.ParentId --匹配关系
)
 
SELECT
     Id,
     Name ,
     ParentId,
     Depth,
     Status,
     Priority
FROM
     Record
     WHERE Status=1
     ORDER BY Priority DESC

参数@Id毫无疑问，是你需要在外部程序里传入的参数。选择存储过程或者直接使用sql语句看自己的喜好（个人倾向于写sql语句）。

 

3、项目实践经验之谈

在实际项目中，对于分类表，通常都会做相应的缓存（这种类型的数据通常说多也不多，说少也不少，但是相对比较稳定），总结一下我在web项目中的使用经验（经验之谈，请务必小心甄别取舍）：

（1）、一次性取出数据库中所有可用分类类别数据；

（2）、数据（Category表数据）转换成对应实体Category；

a、Category实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
using System;
 
/// <summary>
/// 分类实体
/// </summary>
[Serializable]
public class Category : BaseCategory //继承自BaseCategory
{
     public int Id { get ; set ; }
 
     public string Name { get ; set ; }
 
     public int ParentId { get ; set ; }
 
     public int Depth { get ; set ; }
 
     public int Status { get ; set ; }
 
     public int Priority { get ; set ; }
 
}

我们看到，Category实体继承自BaseCategory类，这个类我们定义如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
public abstract class BaseCategory : DotNet.Common.Model.PagerBase //PagerBase 分页基类
{
     /// <summary>
     /// 一级分类id
     /// </summary>
     public int FirstCategoryId { get ; set ; }
     /// <summary>
     /// 一级分类名
     /// </summary>
     public string FirstCategoryName { get ; set ; }
 
     /// <summary>
     /// 二级分类id
     /// </summary>
     public int SecondCategoryId { get ; set ; }
     /// <summary>
     /// 二级分类名
     /// </summary>
     public string SecondCategoryName { get ; set ; }
 
     /// <summary>
     /// 三级分类id
     /// </summary>
     public int ThirdCategoryId { get ; set ; }
     /// <summary>
     /// 三级分类名
     /// </summary>
     public string ThirdCategoryName { get ; set ; }
 
     /// <summary>
     /// 四级分类id
     /// </summary>
     public int ForthCategoryId { get ; set ; }
     /// <summary>
     /// 四级分类名
     /// </summary>
     public string ForthCategoryName { get ; set ; }
}

b、接着通过一定的方法或函数，对Category实体类再做一些处理，完善它的层级关系。比如通过递归函数，初始化一次，准备好这些有层级的数据实体：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
/// <summary>
/// 分类实用帮助类
/// </summary>
public class CategoryUtil
{
     /// <summary>
     /// 分层级的数据实体字典 key： Id  value：分类实体
     /// </summary>
     public static IDictionary< int , Category> DictCategories { get ; set ; }
 
     static CategoryUtil()
     {
         Init();
     }
 
     /// <summary>
     /// 根据品类类别构造一个适合查找的dictionary(1～4级品类ID和对应名称)
     /// </summary>
     private static void Init()
     {
         //DictProductTypes=//查库，一次取出所有可用分类数据 to do
         foreach (KeyValuePair< int , Category> kv in DictCategories)
         {
             Category model = kv.Value;
             switch (model.Depth)
             {
                 default :
                     break ;
                 case 1:
                     model.FirstCategoryId = model.Id;
                     model.FirstCategoryName = model.Name;
                     break ;
                 case 2:
                     model.SecondCategoryId = model.Id;
                     model.SecondCategoryName = model.Name;
                     break ;
                 case 3:
                     model.ThirdCategoryId = model.Id;
                     model.ThirdCategoryName = model.Name;
                     break ;
                 case 4:
                     model.ForthCategoryId = model.Id;
                     model.ForthCategoryName = model.Name;
                     break ;
 
             }
             InitCascadeCategory(model, model.ParentId, model.Depth);
         }
     }
 
     /// <summary>
     /// 初始化层级
     /// </summary>
     /// <param name="query"></param>
     /// <param name="parentId"></param>
     /// <param name="depth"></param>
     private static void InitCascadeCategory(Category query, int parentId, int depth)
     {
         if (depth < 2)
         {
             return ;
         }
         foreach (KeyValuePair< int , Category> kv in DictCategories)
         {
             Category model = kv.Value;
             if (parentId == model.Id && model.Depth == depth - 1)
             {
                 switch (depth)
                 {
                     default :
                         break ;
                     case 2:
                         query.FirstCategoryId = model.Id;
                         query.FirstCategoryName = model.Name;
                         break ;
                     case 3:
                         query.SecondCategoryId = model.Id;
                         query.SecondCategoryName = model.Name;
                         break ;
                     case 4:
                         query.ThirdCategoryId = model.Id;
                         query.ThirdCategoryName = model.Name;
                         break ;
                 }
                 InitCascadeCategory(query, model.ParentId, --depth); //递归
                 break ;
             }
         }
     }
 
}

然后进行第（3）步，进行缓存。

需要特别说明的是，BaseCategory类我们只多设计了8个属性，四个层级（目前为止开发中超过四个层级的我还没有遇到过），当然你可能会问，如果超过4个层级怎么办？曾经看到过有一种通用设计的思路，就是通过一个集合对象（或嵌套的集合对象）进行层级类别的存取，比如泛型Dictionary，LinkedList等等，我还没有尝试实现过，但是设计实现思路确实可以借鉴。

（3）、按照某种策略缓存数据，如每天或者每个月更新一次数据，等等。

（4）、直接查询操作缓存中的分类数据。

 

4、思考

（1）、数据表中Depth字段是不是必要的，是否多余？

（2）、查询时如何避免递归？

（3）、层级过多（比如超过20层级），有没有更好的设计和解决方法？

   … … … …

越想越感到问题多多，期待您的建议和意见。

===============================分割线分割线==============================

 update：根据心海巨澜在本文下面的留言，个人感觉是一个非常不错的解决方案，同时想到了一个和它关联紧密的附加问题。举例来说，一个产品表Product，假设产品信息基本字段包括自增长主键Id，产品名称Name，价格Price，生产日期CreateDate，还有就是产品所对应的分类信息，这个分类信息到底应该如何设计才能快速查询出某一分类下的产品（该分类及其子级分类所对应的所有产品），而且利于后台进行修改维护不至于轻易产生“脏”数据？我个人的经验是直接在数据表Product上面设置几个字段，如FirstCategoryId…ForthCategoryId等等，它们具体什么意思看名称就可以猜到了吧？！ 简单来说，就是适当冗余，查询非常直接，而且利于创建索引，不知您是怎么看待这个问题的。

数据表脚本下载：Category表

zyu67
关注
专栏目录
thinkphp5使用无限极分类
10-17
这样数据表中的每一项都可以通过pid关联到父级分类，实现层级结构。 2. **构建递归函数**：为了实现无限极分类，需要构建一个递归函数来处理每一级分类数据。这个递归函数会不断调用自身，直到没有子分类为止。 3...
关于数据库中的无限级别设计
sean20032000的专栏
03-30 996
<br />     今天一个网友在网上问我“无限极分类采用的什么方法”，一开始我还闷了，以为是什么新的技术，后来问清楚才知道是关于数据库设计方面的问题，害我一身⊙﹏⊙b汗，落伍了。<br /><br />       言归正传，平时这种无限极分类，基本是导航中需要用到的，大概有四种方法：<br /><br />         一、递归循环法：泰州seo一般都是在表里面建这么  编号，名称，父节点的编号 三个字段基本可以解决问题了，读取的时候使用递归就都出来了。这种方法的优点是：理论上可以无限
三级分销（栏目无限极分类）数据库设计
最新发布
sang521jia的博客
03-04 758
其中，别名T2的表示原本的数据，而T1中的plevel即是父代的级别（1：直接父代，2：二级父代，3：三级父代）如果plevel为0，代表是查询的节点自身。查询所有用户4的一级下线，查出来的cid列就是结果。查询用户10的所有父级，查出来的pid列就是结果。@r := 9 设置自己所要搜索子节点的id。@r := 1 为要查询的数据id。
PHP 无限级分类数据库设计及实现
u011415782的专栏
10-12 4628
最近复习算法，在此对无限级分类的实现方法稍作整理，当然也是参考了道友的经验，目测适合实际的项目应用，当然，也有不少公司的笔试题还会涉及到呢，有何问题，欢迎各位道友指摘 … 操作环境：Win10 使用语言：PHP 使用框架：ThinkPHP 3.2.3
mysql 文章 字段设计_如何进行文章分类和标签的数据库设计
weixin_30040925的博客
01-19 745
几乎在所有web项目中，都涉及文章分类和标签的设计，应该说这是一个比较常见、典型的案例。站长并不保证我的思路就是最好的，只是分享出来大家一起交流一下，互相促进与提高。我们假设的开发项目是一个博客系统，最核心的部分就是与文章相关的，那么我们今天讨论如何设计博客系统的文章分类和标签。1、首先，分类和标签都是要和具体的文章相关联的，当然也可能一些文章既没有分类也没有标签，这一点是大家在写查询的时候容易疏...
创建无限极分类树型结构的简单方法
12-19
在本文中，我们将探讨如何创建一个无限极分类的树型结构，这在许多信息系统和网站中都是常见的需求，比如文章分类、商品分类等。无限极分类允许我们构建一个灵活的层级结构，使得每个分类可以有任意多的子分类，而不...
php 无限极分类
01-20
复制代码 代码如下:<...添加分类 catalog_add($uid,$name) //$uid 父id //$name 分类名 流程:依据$uid,在此id下添加一个新子id 删除分类 catalog_del($uid)//参数 $uid 数要删除的分类 修改分类 catalo
PHP实现无限极分类生成分类树的方法
12-20
在PHP编程中，无限极分类是一种常见的数据组织方式，尤其在网站内容管理、商品分类、菜单构建等场景中广泛使用。无限极分类的核心思想是通过一个`pid`（parent id）字段来指代一个分类的父分类，以此形成层级结构。...
无限极二叉树，数据结构，经典算法
bing8181413的专栏
09-03 3234
利用多叉树实现Ext JS中的无限级树形菜单（一种构建多级有序树形结构JSON的方法）                                                      一、问题研究的背景和意义 目前在Web应用程序开发领域，Ext JS框架已经逐渐被广泛使用，它是富客户端开发中出类拔萃的框架之一。在Ext的UI控件中，树形控件无疑是最为常用的控件之一，它用来实
php+mysql实现的无限极分类
还不是【web】的博客
03-02 1193
首先数据库设计如下图 吧数据库的数据查出来，吧数据生成树结构 public function get_cat(){         //$where['pid'] = 0;         $where[] = '';         $categories = M('cat_level')->select();         $rs = $this->list_to_t
三种无限极分类
weixin_30535565的博客
08-23 146
网站大规模分类 比较三种分类的数据库设计 第一种：4个表 实现四级分类 一级表:ID1,IDName 二级表：ID2，IDname，FID（上一级id) 三级表：ID3，IDname，FID（上一级id) 四级表：ID4，IDname，FID（上一级id) 第二种：用树形结构表实现无限制级分类 表结构：id，fid（上一级id),idname 第三种：用编码的...
无限级分类表设计
weixin_42604188的博客
12-30 166
无限级分类表，也称为无限递归分类，是一种常用的数据库设计方式，用于描述一组具有层次结构的数据。它可以表示父子关系、上下级关系或者多级分类等。 要设计无限级分类表，需要建立一张表，表中至少包含以下几个字段： ID：用于唯一标识每一条记录 Name：表示分类的名称 ParentID：表示该记录的父记录的ID 例如，我们可以建立一张分类表，表中包含以下数据： ID Name ParentID ...
在数据库中存储一棵树，实现无限级分类
weixin_34239169的博客
04-10 1028
原文发表于我的博客： https://blog.kaciras.net/article/36 在一些系统中，对内容进行分类是必需的功能。比如电商就需要对商品做分类处理，以便于客户搜索；论坛也会分为很多板块；门户网站、也得对网站的内容做各种分类。 分类对于一个内容展示系统来说是不可缺少的，本博客也需要这么一个功能。众所周知，分类往往具有从属...
前同事留下的经典功能无限极分类
西北大粽子
04-06 139
Hello 大家好 我还是你们熟悉的大粽子！ 今天重点讲下前同事专门优化的功能，此功能实现后，后台删了不下十张表！ 无限级分类设计 无限级分类是从顶级节点开始，每一个节点可以有多个子节点(宽)，以及多层节点(高)； 解决方案 使用递归算法，也是使用频率最多的，大部分开源程序也是这么处理，不过一般都只用到四级分类。这种算法的数据库结构设计最为简单。category表中一个字段id，一个字段fid(父id)。这样可以根据WHERE id = fid来判断上一级内容，运用递归至最顶层。 分析：通过这种数
mysql多级分类设计
qq_40861391的博客
06-02 4338
在数据库设计中，经常会遇到需要存储多级分类信息的情况，如商品分类、地区分类等。本文将详细介绍如何在MySQL中设计和管理多级分类数据MPTT算法是一种用于处理树状结构数据的算法，其中MPTT代表Modified Preorder Tree Traversal（修改的先序遍历树遍历）。它通过为树中的每个节点分配一个预排序值来组织和表示树的结构。这种预排序值允许我们以一维的方式存储和操作树，同时保持树的层次结构和父子关系。
无限层级mysql数据表结构
Morgan Wang 的博客
10-26 1287
# 无限层级mysql数据表结构 # 创建表 -- auto-generated definition create table target_resource ( id int not null primary key, resource_id int null, resource_team_id int null ); # 创建关系表 ...
层次关系数据库表的设计,无限层次（转）
defweyft983563的博客
01-04 1469
数据层次的概念: 　　数据层次是表达数据的一种重要关系，在数据库的设计中，如：组织结构分解、工作任务分解、行政区划的分解等都是层次关系数据的典型实例。 　　表达层次关系的数据一般需要实现如下属性： 　　1.层次的最大级联层次数。如：中国－>湖南省->长沙市->雨花区，就是4层。 　　2.能反映同一层次之间的顺序关系。如：长沙市 必须在 衡阳市的前面，（因为其是省...
mysql select 无限极_关于使用sql语句实现无限极分类查询的疑问
weixin_35684578的博客
02-26 251
如果只有这三级的话，可以这样自己和自己连表取语句为:select a.id as pid, a.name as pname, a.pid as ppid, b.name as ppnamefrom family a join family b on a.pid=b.id where a.id=儿子的信息的pid;实例：db83-3306>>select * from family;+-...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值