mysql - 在递归表上排序

Question

我有一个 categories 表，带有一些递归。一些类别是子类别，因此在 parent_category_id 字段中有一个值。顶级类别在 parent_category_id 字段中为空。

要求是列出类别，以便它们显示为:

Parent category 1
  Sub category 1
  Sub category 2

Parent category 2
  Subcategory 3
  Subcategory 4

这是否可以通过单个查询顺序语句实现，或者我是否需要进行单独的查询？

根据要求提供一些示例数据:

Answer 1

在不支持递归cte的MYSQL中:

select id, path(id) from mytable order by 2，其中path(id)定义为

DELIMITER $$
CREATE FUNCTION path(v_id INT(10)) RETURNS varchar(255)
begin
declare v_path varchar(255);
declare v_parent_id INT(10); 
declare MAX_ITERS int;
declare iters int;
set v_path = '';
set MAX_ITERS = 20;
set iters = 0;

if not exists (select * from node where id = v_id) then
    return 'no such node'; 
end if;

if not exists (select * from node where parent_id < 0) then
    return 'no root node in table';
end if;

select parent_id into v_parent_id from node where id = v_id;
while (v_parent_id >= 0) do
    set iters = iters + 1;
    if iters >= MAX_ITERS then
        return 'path too long'; 
    end if;
    select parent_id, concat(id, '.', v_path) into v_parent_id, v_path from node where id = v_id;
    set v_id = v_parent_id;
end while;
return trim(both '.' from v_path);

end$$


DELIMITER ;
;

请注意，在我的示例中，我使用的节点 ID 为 -1，而不是根的空父节点。

为了性能，维护第二个表(使用触发器)，它存储每个节点的“路径”，其中路径(节点)由上述 UDF 定义。