评论树形表设计

comment-tree.sql

CREATE TABLE `comments` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `text` text NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `comments_tree` (
  `parent_id` bigint(20) NOT NULL,
  `child_id` bigint(20) NOT NULL,
  `level` int(11) DEFAULT NULL,
  PRIMARY KEY (`parent_id`,`child_id`),
  KEY `child` (`child_id`),
  CONSTRAINT `child` FOREIGN KEY (`child_id`) REFERENCES `comments` (`id`),
  CONSTRAINT `parent` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`id`)
) ;

-- 新增节点
SELECT @newChild := 6, @parent := 3 FROM dual;
SELECT     @level := t.level
FROM       comments_tree t
WHERE      t.parent_id = @parent AND t.parent_id = t.child_id;

INSERT INTO comments(id, text) VALUES (@newChild, '评论1-1-2');
INSERT INTO comments_tree (parent_id, child_id, level)
  SELECT t.parent_id, @newChild, @level + 1
  FROM   comments_tree t
  WHERE  t.child_id = @parent
  UNION ALL
  SELECT @newChild, @newChild, @level + 1;