这个问题仍然显示在搜索结果的前几位。因此,我想分享我的解决方案,并希望它能帮助更多的人。当您有一个具有许多级父子关系的表时,这种解决方案也适用。虽然它是相当慢的解决方案。顶层的父项使用NULL
。
+
| id | parent |
+
| 1 | NULL |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
+
在我的方法中,将使用一个递归调用自身的过程,并将路径不断添加到请求的 id
的父级,直到达到 NULL
父级。
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PATH`(IN `input` INT, OUT `output` VARCHAR(128))
BEGIN
DECLARE _id INT;
DECLARE _parent INT;
DECLARE _path VARCHAR(128);
SET `max_sp_recursion_depth` = 50;
SELECT `id`, `parent`
INTO _id, _parent
FROM `database`.`table`
WHERE `table`.`id` = `input`;
IF _parent IS NULL THEN
SET _path = _id;
ELSE
CALL `PATH`(_parent, _path);
SELECT CONCAT(_path, '-', _id) INTO _path;
END IF;
SELECT _path INTO `output`;
END $$
DELIMITER ;
为了在
ORDER BY
子句中使用结果,您需要编写一个
FUNCTION
来封装
PROCEDURE
的结果。
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GETPATH`(`input` INT) RETURNS VARCHAR(128)
BEGIN
CALL `PATH`(`input`, @path);
RETURN @path;
END $$
DELIMITER ;
现在我们可以使用递归路径来排序表格的顺序。 在我的工作站上,对于具有10000行的表格,仅需要略微超过一秒钟的时间。
SELECT `id`, `parent`, GETPATH(`id`) `path` FROM `database`.`table` ORDER BY `GETPATH`(`id`);
示例输出:
+
| id | parent | path |
+
| 1 | NULL | 1 |
| 10 | 1 | 1-10 |
| 300 | 10 | 1-10-300 |
| 301 | 300 | 1-10-300-301 |
| 302 | 300 | 1-10-300-302 |
+
5 rows in set (1,39 sec)