老马黑科技数据库的表设计
看过文章后选择的策略是闭包模式， 创建两张表：
- 用户表
CREATE TABLE `sys_member` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '姓名' ,
PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
- 关系表
CREATE TABLE `sys_member_relation` (
`ancestor_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '祖先ID' ,
`descendant_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '子孙ID' ,
`level` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' COMMENT '扩展等级表' ,
PRIMARY KEY (`ancestor_id`, `descendant_id`),
INDEX `i_sys_member_relation_descendant_id` (`descendant_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;
添加测试数据
- 用户表为了区分层级，姓名做了处理，如下图：
sys_member.png
- 用户关系表的数据如下:
relation.png
表的操作
添加操作
用户表添加了 李四_3, 关系表的添加操作如下：
INSERT INTO sys_member_relation (ancestor_id, descendant_id, LEVEL) SELECT ancestor_id, 7, level + 1 FROM sys_member_relation WHERE descendant_id = 2 UNION ALL SELECT 7, 7, 0;
查询操作
- 查询某个关系的所有数据
SELECT m.* FROM sys_member_relation r JOIN sys_member m ON m.id = r.descendant_id WHERE r.ancestor_id = 2 ;
- 查询所有下线
SELECT m.* FROM sys_member_relation r JOIN sys_member m ON m.id = r.descendant_id WHERE r.ancestor_id = 2 AND r.level > 0;
- 查询 李四_2_1 的所有上线
SELECT m.* FROM sys_member_relation r JOIN sys_member m ON m.id = r.ancestor_id WHERE r.descendant_id = 6 AND LEVEL > 0;
