首页 > 代码库 > 子账号表的设计(不用递归实现查询,同一张表做外键)

子账号表的设计(不用递归实现查询,同一张表做外键)

最近公司在做《资管系统》账号管理部分需要得到所有子账号、父账号等等信息。个人觉得用递归查询比较繁琐,于是设计了以下表

关键字段:账号id,父账号、顶级账号、账号层次(也就是树的级别)。

查询思路:查找顶级账号用层这个字段直接搞定

     查找某个账号的子账号用该账号的顶级账号和层搞定

     查找父账号用该账号的顶级账号和层搞定

设计难点:一张表外键关联到自己、添加修改删除时对数据的有效性有很大的保障

提供源码(包含查询方法),需要其他字段自行增加 (勿喷)

技术分享

技术分享

技术分享

 

表和数据

-- ----------------------------
-- Table structure for `tb_account`
-- ----------------------------
DROP TABLE IF EXISTS `tb_account`;
CREATE TABLE `tb_account` (
  `id` int(11) NOT NULL COMMENT ‘账号ID‘,
  `name` varchar(32) NOT NULL COMMENT ‘账号名称‘,
  `password` varchar(64) NOT NULL COMMENT ‘账号密码‘,
  `degree` int(11) NOT NULL COMMENT ‘账号层次,账号树的层次‘,
  `parent` int(11) NOT NULL COMMENT ‘父账号ID‘,
  `master` int(11) NOT NULL COMMENT ‘顶层账号ID,即真实账号‘,
  `childs` text COMMENT ‘子账号列表,所有的子账号id(包含子账号的子账号),按"|"分割‘,
  `inserttime` datetime DEFAULT NULL COMMENT ‘插入时间‘,
  `updatetime` datetime DEFAULT NULL COMMENT ‘修改时间‘,
  `remark` varchar(256) DEFAULT NULL COMMENT ‘备注‘,
  PRIMARY KEY (`id`),
  KEY `FK_TB_ACCOUNT_REFERENCE_TB_ACCOUNT_P_` (`parent`),
  KEY `FK_TB_ACCOUNT_REFERENCE_TB_ACCOUNT_M_` (`master`),
  CONSTRAINT `FK_TB_ACCOUNT_REFERENCE_TB_ACCOUNT_M_` FOREIGN KEY (`master`) REFERENCES `tb_account` (`id`),
  CONSTRAINT `FK_TB_ACCOUNT_REFERENCE_TB_ACCOUNT_P_` FOREIGN KEY (`parent`) REFERENCES `tb_account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_account
-- ----------------------------
INSERT INTO `tb_account` VALUES (‘1‘, ‘acct_1‘, ‘1‘, ‘1‘, ‘1‘, ‘1‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘2‘, ‘acct_2‘, ‘2‘, ‘1‘, ‘2‘, ‘2‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘3‘, ‘acct_3‘, ‘3‘, ‘2‘, ‘1‘, ‘1‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘4‘, ‘acct_4‘, ‘4‘, ‘2‘, ‘1‘, ‘1‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘5‘, ‘acct_5‘, ‘5‘, ‘3‘, ‘3‘, ‘1‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘6‘, ‘acct_6‘, ‘6‘, ‘3‘, ‘3‘, ‘1‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘7‘, ‘acct_7‘, ‘7‘, ‘4‘, ‘5‘, ‘1‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘8‘, ‘acct_8‘, ‘8‘, ‘4‘, ‘6‘, ‘1‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘9‘, ‘acct_9‘, ‘9‘, ‘4‘, ‘6‘, ‘1‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘10‘, ‘acct_10‘, ‘10‘, ‘2‘, ‘2‘, ‘2‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘11‘, ‘acct_11‘, ‘11‘, ‘2‘, ‘2‘, ‘2‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘12‘, ‘acct_12‘, ‘12‘, ‘3‘, ‘11‘, ‘2‘, null, null, null, null);
INSERT INTO `tb_account` VALUES (‘13‘, ‘acct_13‘, ‘13‘, ‘3‘, ‘11‘, ‘2‘, null, null, null, null);

 

查询操作

-- 查找所有的顶级账号
-- SELECT id,name,degree,parent,`master` FROM tb_account WHERE degree = 1

-- 查找顶级账号的所有子账号(包括子账号的子账号)
-- SELECT * FROM tb_account WHERE  `master` = 1

-- 查找子账号的父账号,根据子账号的parent直接找
-- SELECT * FROM tb_account WHERE  id = 1 -- (parent)

-- 查找子账号的所有子账号(包括子账号的子账号)
-- 账号id、层次
-- SELECT a.id,a.name,a.degree,a.parent,a.`master` 
-- FROM tb_account a,tb_account b
-- WHERE b.id = 1 and a.`master` = b.`master` AND a.degree > b.degree

-- 查找当前账号的直接子账号
-- SELECT * FROM tb_account where parent = 1 -- (当前账号id) 

 

子账号表的设计(不用递归实现查询,同一张表做外键)