MySQL递归CTE实现嵌套评论按点赞数排序并保留层级结构

需求说明 #

• 支持无限层级回复
• 评论按点赞数（voteCount）降序排序，同时严格保留树形结构（父评论始终直接显示在子评论上方）

原尝试代码 #

CREATE TABLE `comment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent` int DEFAULT NULL,
  `content` text NOT NULL,
  `voteCount` int DEFAULT NULL
);

INSERT INTO comment (id,parent,content,voteCount) VALUES
     (1, NULL,'Comment 1'      ,0),
     (2, NULL,'Comment 2'      ,0),
     (3, 2   ,'Comment 2.1'    ,0),
     (4, 2   ,'Comment 2.2'    ,20),
     (5, NULL,'Comment 3'      ,5),
     (6, 3   ,'Comment 2.1.1'  ,0),
     (7, 6   ,'Comment 2.1.1.1',0);

WITH RECURSIVE nested_comments(
id,
content,
voteCount,
path,
level,
sortable
) AS (
SELECT
    id,
    content,
    voteCount,
    CAST(id AS CHAR(1000)),
    0,
    CONCAT('-', CAST(COALESCE(voteCount, 0) AS CHAR(1000)), '.', CAST(id AS CHAR(1000)))
FROM
    comment
WHERE
    parent IS NULL
UNION ALL
SELECT
    c.id,
    c.content,
    c.voteCount,
    CONCAT(nc.path, '.', CAST(c.id AS CHAR(1000))),
    nc.level + 1,
    CONCAT(nc.sortable, '.-', CAST(COALESCE(c.voteCount, 0) AS CHAR(4)), '.', CAST(c.id AS CHAR(1000)))
FROM
    nested_comments nc
JOIN comment c ON
    nc.id = c.parent
)
SELECT
    *
FROM
    nested_comments
ORDER BY
    sortable;

当前执行结果 #

+----+-----------------+-----------+---------+-------+---------------------+
| id | content         | voteCount | path    | level | sortable            |
+----+-----------------+-----------+---------+-------+---------------------+
| 1  | Comment 1       | 0         | 1       | 0     | -0.1                |
+----+-----------------+-----------+---------+-------+---------------------+
| 2  | Comment 2       | 0         | 2       | 0     | -0.2                |
+----+-----------------+-----------+---------+-------+---------------------+
| 3  | Comment 2.1     | 0         | 2.3     | 1     | -0.2.-0.3           |
+----+-----------------+-----------+---------+-------+---------------------+
| 6  | Comment 2.1.1   | 0         | 2.3.6   | 2     | -0.2.-0.3.-0.6      |
+----+-----------------+-----------+---------+-------+---------------------+
| 7  | Comment 2.1.1.1 | 0         | 2.3.6.7 | 3     | -0.2.-0.3.-0.6.-0.7 |
+----+-----------------+-----------+---------+-------+---------------------+
| 4  | Comment 2.2     | 20        | 2.4     | 1     | -0.2.-20.4          |
+----+-----------------+-----------+---------+-------+---------------------+
| 5  | Comment 3       | 5         | 5       | 0     | -5.5                |
+----+-----------------+-----------+---------+-------+---------------------+

预期结果 #

+----+-----------------+-----------+---------+-------+---------------------+
| id | content         | voteCount | path    | level | sortable            |
+----+-----------------+-----------+---------+-------+---------------------+
| 5  | Comment 3       | 5         | 5       | 0     | -5.5                |
+----+-----------------+-----------+---------+-------+---------------------+
| 1  | Comment 1       | 0         | 1       | 0     | -0.1                |
+----+-----------------+-----------+---------+-------+---------------------+
| 2  | Comment 2       | 0         | 2       | 0     | -0.2                |
+----+-----------------+-----------+---------+-------+---------------------+
| 4  | Comment 2.2     | 20        | 2.4     | 1     | -0.2.-20.4          |
+----+-----------------+-----------+---------+-------+---------------------+
| 3  | Comment 2.1     | 0         | 2.3     | 1     | -0.2.-0.3           |
+----+-----------------+-----------+---------+-------+---------------------+
| 6  | Comment 2.1.1   | 0         | 2.3.6   | 2     | -0.2.-0.3.-0.6      |
+----+-----------------+-----------+---------+-------+---------------------+
| 7  | Comment 2.1.1.1 | 0         | 2.3.6.7 | 3     | -0.2.-0.3.-0.6.-0.7 |
+----+-----------------+-----------+---------+-------+---------------------+

问题分析 #

原代码的核心问题在于sortable字段的字符串排序逻辑不符合数值降序需求：
字符串排序是逐字符比较字典序，比如-20.4会被判定为大于-0.3（因为第二个字符2的ASCII码大于0），导致点赞数更高的评论反而排在后面。

要解决这个问题，需要将点赞数转换为固定长度的字符串，确保数值降序对应字符串升序，这样排序时就能正确按点赞数从高到低排列，同时保留树形结构。

正确实现方案 #

这里假设点赞数的范围是0到999999（可根据实际情况调整），通过999999 - voteCount将降序需求转换为升序字符串排序，并用LPAD补零保证固定长度：

CREATE TABLE `comment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent` int DEFAULT NULL,
  `content` text NOT NULL,
  `voteCount` int DEFAULT NULL
);

INSERT INTO comment (id,parent,content,voteCount) VALUES
     (1, NULL,'Comment 1'      ,0),
     (2, NULL,'Comment 2'      ,0),
     (3, 2   ,'Comment 2.1'    ,0),
     (4, 2   ,'Comment 2.2'    ,20),
     (5, NULL,'Comment 3'      ,5),
     (6, 3   ,'Comment 2.1.1'  ,0),
     (7, 6   ,'Comment 2.1.1.1',0);

WITH RECURSIVE nested_comments(
id,
content,
voteCount,
path,
level,
sortable
) AS (
SELECT
    id,
    content,
    voteCount,
    CAST(id AS CHAR(1000)),
    0,
    -- 生成固定长度的排序键：点赞数越高，该值越小，升序排序时越靠前
    CONCAT(LPAD(CAST(999999 - COALESCE(voteCount, 0) AS CHAR), 6, '0'), '.', CAST(id AS CHAR))
FROM
    comment
WHERE
    parent IS NULL
UNION ALL
SELECT
    c.id,
    c.content,
    c.voteCount,
    CONCAT(nc.path, '.', CAST(c.id AS CHAR(1000))),
    nc.level + 1,
    -- 拼接父节点的排序键和当前节点的排序片段
    CONCAT(nc.sortable, '.', LPAD(CAST(999999 - COALESCE(c.voteCount, 0) AS CHAR), 6, '0'), '.', CAST(c.id AS CHAR))
FROM
    nested_comments nc
JOIN comment c ON
    nc.id = c.parent
)
SELECT
    id,
    content,
    voteCount,
    path,
    level,
    -- 可选：还原为类似原格式的sortable用于展示，不影响排序
    CONCAT('-', REPLACE(sortable, '.', '.-')) AS sortable
FROM
    nested_comments
ORDER BY
    sortable;

执行结果 #

该代码会输出符合预期的排序结果：父评论按点赞数降序排列，每个父评论下的子评论也按点赞数降序排列，同时严格保留树形结构。

内容的提问来源于stack exchange，提问作者OscarVGG