By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `comments` (
`comment_id` int NOT NULL,
`user_id` int DEFAULT NULL,
`comment` varchar(32) DEFAULT NULL,
`post_id` int DEFAULT NULL,
`parent_comment_id` int DEFAULT NULL,
PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO comments VALUES
(1, 1, 'comment 1', 123, null),
(2, 2, 'comment 2', 123, 1);
WITH RECURSIVE cte(comment, path, comment_id, user_id, post_id) AS
( SELECT
comment,
cast(comment_id as char),
comment_id,
user_id,
post_id
FROM
comments
WHERE parent_comment_id IS NULL
UNION ALL
SELECT
child.comment,
CONCAT(parent.path,'/',child.comment_id),
child.comment_id,
child.user_id,
child.post_id
FROM
cte parent INNER JOIN comments child
ON
child.parent_comment_id = parent.comment_id
)
SELECT * FROM cte;
Records: 2 Duplicates: 0 Warnings: 0
| comment | path | comment_id | user_id | post_id |
|---|---|---|---|---|
| comment 1 | 1 | 1 | 1 | 123 |
| comment 2 | 1/2 | 2 | 2 | 123 |