【MySQL】无限层级数据表设计

需求：

用户A介绍B入会，B成为A的下线，B再发展C、D等下线，C、D也允许发展下线，不限制层级，但是每个人只允许有一个上线；

典型的树结构；

 问题：

快速查询某人的所有上线；

快速查询某人的所有下线；

快速为某人增加一个下线；

方案有以下四种，各自的定义和利弊请看：前辈的文档

一、邻接表:依赖父节点

二、路径枚举

三、嵌套集

四、闭包表

 

现在，只针对 “闭包表” 设计，实现以上需求

-- 会员信息表
-- level 字段说明：相对于第一个用户的等级。
-- 譬如：用户A介绍B入会，B成为A的下线，由此用户A的等级是1，用户B的等级是2
CREATE TABLE IF NOT EXISTS `members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(30) NOT NULL DEFAULT '' COMMENT '名称',
  `level` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '会员等级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员信息表';

-- 会员级别关联表
CREATE TABLE IF NOT EXISTS `member_relation` (
  `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `puid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '阶梯等级上的用户id',
  UNIQUE KEY `uid` (`uid`,`puid`),
  KEY `puid` (`puid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员级别关联表';


INSERT INTO `members` VALUES (1,'A1',1),(2,'A1B1',2),(3,'A1B2',2),(4,'A1B3',2),(5,'A1B4',2),(6,'A1B5',2),(7,'A1B6',2),(8,'A1B1C1',3),(9,'A1B1C2',3),(10,'A1B1C3',3),(11,'A1B1C4',3),(12,'A1B2C5',3),(13,'A1B2C6',3),(14,'A1B3C7',3),(15,'A1B3C8',3),(16,'A1B3C9',3),(17,'A1B4C10',3),(18,'A1B4C11',3),(19,'A1B5C12',3),(20,'A1B2C6D1',4),(21,'A1B2C6D2',4),(22,'A1B2C6D1E1',5),(23,'A1B2C6D1E1F1',6),(24,'A1B2C6D1E1F2',6),(25,'A2',1),(26,'A2B1',2),(27,'A2B2',2),(28,'A2B1C1',3);


INSERT INTO `member_relation` VALUES (0,0),(1,0),(25,0),(26,0),(27,0),(28,0),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1),(13,1),(14,1),(15,1),(16,1),(17,1),(18,1),(19,1),(20,1),(21,1),(22,1),(23,1),(24,1),(8,2),(9,2),(10,2),(11,2),(12,3),(13,3),(20,3),(21,3),(22,3),(23,3),(24,3),(14,4),(15,4),(16,4),(17,5),(18,5),(19,6),(20,13),(21,13),(22,13),(23,13),(24,13),(22,20),(23,20),(24,20),(23,22),(24,22),(26,25),(27,25),(28,25),(28,26);

 需求分析

-- 快速查询某人的所有上线：
SELECT * FROM members WHERE id IN (SELECT puid FROM member_relation WHERE uid = '用户ID');

-- 快速查询某人的所有下线：
SELECT * FROM members WHERE id IN (SELECT uid FROM member_relation WHERE puid = '用户ID');

-- 快速为某人增加一个下线：
INSERT INTO `members`(`name`, `level`) VALUES ('用户名', '上级用户等级+1');
INSERT INTO `member_relation`(`uid`, `puid`) SELECT '用户ID' AS uid,puid FROM member_relation WHERE uid = '上级用户ID';
INSERT INTO `member_relation`(`uid`, `puid`) VALUES ('用户ID', '上级用户ID');