基于个人需要这里主要了解闭包表。
CREATE TABLE `comment_path` (
`ancestor` INT NOT NULL,
`descendant` INT NOT NULL,
`depth` TINYINT(5) NOT NULL,
PRIMARY KEY (`ancestor`, `descendant`),
INDEX `fk_comment_path_descendant_idx` (`descendant` ASC),
INDEX `fk_comment_path_ancestor_idx` (`ancestor` ASC),
CONSTRAINT `fk_comment_path_comment1`
FOREIGN KEY (`ancestor`)
REFERENCES `comment` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_comment_path_comment2`
FOREIGN KEY (`descendant`)
REFERENCES `comment` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
<-可以不要这个表->
CREATE TABLE `topic` (
`id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(256) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
<-用户信息,可以是用户中心的表,也可以和票据信息表放一块->
CREATE TABLE `user` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(128) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
<-票据信息表->
CREATE TABLE `comment` (
`id` INT NOT NULL AUTO_INCREMENT,
`value` VARCHAR(2048) NULL,
`topic_id` INT NOT NULL,
`user_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_comment_topic_idx` (`topic_id` ASC),
INDEX `fk_comment_user1_idx` (`user_id` ASC),
CONSTRAINT `fk_comment_topic2`
FOREIGN KEY (`topic_id`)
REFERENCES `topic` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_comment_user1`
FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
INSERT INTO `comment_path` (`ancestor`, `descendant`, `depth`) VALUES (2, 3, 1);
select c.* from comment c join comment_path cp on (c.id = cp.descendant) where cp.ancestor = 1 and depth = 1;
insert into comment(value, topic_id, user_id) values('(10)我以gin食阼啦', 1, 2);
insert into comment_path (ancestor, descendant, depth) select cp.ancestor, 10, cp.depth+1 from comment_path as cp where cp.descendant=6 union all select 10, 10, 0;
delete a from comment_path a join comment_path b on (a.descendant = b.descendant) where b.ancestor=3;
delete a from comment_path as a join comment_path as d on a.descendant = d.descendant left join comment_path as x on x.ancestor = d.ancestor and x.descendant = a.ancestor where d.ancestor = 册1 and x.ancestor is null;
insert into comment_path (ancestor, descendant, depth) select supertree.ancestor, subtree.descendant, supertree.depth+subtree.depth+1 from comment_path as supertree join comment_path as subtree where subtree.ancestor = 柜2 and supertree.descendant = 册1;
https://www.jianshu.com/p/951b742fd137 https://time.geekbang.org/column/article/67856 https://github.com/Agileaq/Hierarchical_Design/blob/master/Closure.sql https://juejin.cn/post/6844903906112176141 https://zh.wikipedia.org/wiki/%E5%B9%B3%E8%A1%A1%E6%A0%91