MySQL 嵌套集合模型：利用 lft/rght 字段高效查询树形结构

DROP TABLE IF EXISTS comment;
CREATE TABLE `comment` (
 `comment_id` int(11) DEFAULT NULL,
 `left_num` int(11) DEFAULT NULL,
 `right_num` int(11) DEFAULT NULL
);

INSERT INTO `comment` VALUES 
 (1,1,14),
 (2,2,5),
 (3,3,4),
 (4,6,13),
 (5,7,8),
 (6,9,12),
 (7,10,11);
 
-- 为左右值建立联合索引
CREATE INDEX idx$comment$left_num$right_num ON `comment` (`left_num`, `right_num`);

SELECT c.*
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
 AND p.comment_id = 4;

SELECT p.* 
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
 AND c.comment_id = 6;

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- 计算节点 4 的深度，根节点深度为 1
SELECT c.*,
 COUNT(c.comment_id) AS depth
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
 AND c.comment_id = 4
GROUP BY c.comment_id;

SELECT sub_child.*,
 (COUNT(sub_parent.comment_id) - 1) AS depth
FROM (
 SELECT child.*
 FROM comment AS parent, comment AS child
 WHERE child.left_num BETWEEN parent.left_num AND parent.right_num
  AND parent.comment_id = 4
) AS sub_child, (
 SELECT child.*
 FROM comment AS parent, comment AS child
 WHERE child.left_num BETWEEN parent.left_num AND parent.right_num
  AND parent.comment_id = 4
) AS sub_parent
WHERE sub_child.left_num BETWEEN sub_parent.left_num AND sub_parent.right_num
GROUP BY sub_child.comment_id
ORDER BY sub_child.left_num;

-- 确认插入位置的基准点（此处假设插入到 left_num=6 之后）
UPDATE comment SET left_num = left_num + 2 WHERE left_num > 6;
UPDATE comment SET right_num = right_num + 2 WHERE right_num > 6;

INSERT INTO comment 
SELECT 44, left_num + 1, left_num + 2
FROM comment WHERE comment_id = 4;