如何通过层级路径在SQL中获取categories表记录？

现有Postgres分类表结构 #

CREATE TABLE categories 
(
    category_id uuid NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
    parent_id uuid REFERENCES categories(category_id) ON DELETE CASCADE,
    image_url text,
    name text NOT NULL,
    description text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (parent_id, name)
);

问题场景 #

针对上述树形结构的分类表，需要通过类似motorcycles/wheels/tires的路径字符串，精准定位到对应的分类记录。原本打算写复杂SQL函数返回category_id，但实现难度高，想了解业界通用的解决办法。

业界通用解决方案 #

方法1：递归CTE（标准SQL通用方案） #

这是最常用的无侵入方案，不用修改表结构，靠递归遍历树形结构生成每个分类的完整路径，再和目标路径匹配。

示例SQL（Postgres环境）：

WITH RECURSIVE category_path AS (
    -- 先找所有根分类（parent_id为空的节点），初始化路径数组
    SELECT 
        category_id,
        parent_id,
        name,
        ARRAY[name] AS path_array
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归拼接子分类的路径数组
    SELECT 
        c.category_id,
        c.parent_id,
        c.name,
        cp.path_array || c.name
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.category_id
)
-- 匹配目标路径：可以转成字符串匹配，也可以直接用数组匹配
SELECT category_id, name, description
FROM category_path
-- 方式1：转字符串匹配
WHERE array_to_string(path_array, '/') = 'motorcycles/wheels/tires';
-- 方式2：数组匹配（性能更优，建议用这个）
-- WHERE path_array = ARRAY['motorcycles', 'wheels', 'tires'];

提示：如果能提前把输入的路径字符串拆成数组（比如把motorcycles/wheels/tires拆成['motorcycles','wheels','tires']），直接用数组匹配的性能会比字符串拼接后匹配更好。

方法2：预存完整路径（性能优先方案） #

如果路径查询频率很高，递归CTE的性能可能跟不上，这时可以在表中新增字段预存每个分类的完整路径，避免每次查询都递归遍历。

步骤1：新增字段

ALTER TABLE categories ADD COLUMN full_path text;

步骤2：初始化现有数据

用递归CTE批量生成并更新所有分类的完整路径：

WITH RECURSIVE category_path AS (
    SELECT 
        category_id,
        name,
        parent_id,
        name AS full_path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        c.category_id,
        c.name,
        c.parent_id,
        cp.full_path || '/' || c.name
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.category_id
)
UPDATE categories c
SET full_path = cp.full_path
FROM category_path cp
WHERE c.category_id = cp.category_id;

步骤3：创建触发器维护路径一致性

当新增、修改分类的父节点或名称时，自动更新自身及所有子分类的full_path：

CREATE OR REPLACE FUNCTION update_category_full_path()
RETURNS TRIGGER AS $$
BEGIN
    -- 先更新当前分类的full_path
    IF NEW.parent_id IS NULL THEN
        NEW.full_path = NEW.name;
    ELSE
        SELECT full_path || '/' || NEW.name INTO NEW.full_path
        FROM categories WHERE category_id = NEW.parent_id;
    END IF;

    -- 再更新所有子分类的full_path
    WITH RECURSIVE child_categories AS (
        SELECT category_id, name, parent_id, full_path
        FROM categories
        WHERE parent_id = NEW.category_id
        UNION ALL
        SELECT c.category_id, c.name, c.parent_id, NEW.full_path || '/' || c.name
        FROM categories c
        JOIN child_categories cc ON c.parent_id = cc.category_id
    )
    UPDATE categories c
    SET full_path = cc.full_path
    FROM child_categories cc
    WHERE c.category_id = cc.category_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_category_full_path
BEFORE INSERT OR UPDATE OF parent_id, name ON categories
FOR EACH ROW EXECUTE FUNCTION update_category_full_path();

步骤4：查询时直接匹配

SELECT category_id, name, description
FROM categories
WHERE full_path = 'motorcycles/wheels/tires';

提示：这种方案是用存储空间换查询性能，查询速度会大幅提升，但需要维护路径的一致性，适合查询远多于修改的业务场景。

方法3：Postgres专属ltree扩展（高效专业方案） #

Postgres提供了ltree类型专门用于处理树形路径，支持高效的路径匹配、子节点查询等操作，是Postgres环境下的最优解之一。

步骤1：启用ltree扩展

CREATE EXTENSION IF NOT EXISTS ltree;

步骤2：新增ltree类型字段

ALTER TABLE categories ADD COLUMN path ltree;

步骤3：初始化现有数据

WITH RECURSIVE category_path AS (
    SELECT 
        category_id,
        name,
        parent_id,
        name::ltree AS path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        c.category_id,
        c.name,
        c.parent_id,
        cp.path || c.name::ltree
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.category_id
)
UPDATE categories c
SET path = cp.path
FROM category_path cp
WHERE c.category_id = cp.category_id;

步骤4：创建触发器维护路径

CREATE OR REPLACE FUNCTION update_category_ltree_path()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新当前分类的path
    IF NEW.parent_id IS NULL THEN
        NEW.path = NEW.name::ltree;
    ELSE
        SELECT path || NEW.name::ltree INTO NEW.path
        FROM categories WHERE category_id = NEW.parent_id;
    END IF;

    -- 更新所有子分类的path
    WITH RECURSIVE child_categories AS (
        SELECT category_id, parent_id, path
        FROM categories
        WHERE parent_id = NEW.category_id
        UNION ALL
        SELECT c.category_id, c.parent_id, NEW.path || subpath(c.path, nlevel(NEW.path))
        FROM categories c
        JOIN child_categories cc ON c.parent_id = cc.category_id
    )
    UPDATE categories c
    SET path = cc.path
    FROM child_categories cc
    WHERE c.category_id = cc.category_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_category_ltree_path
BEFORE INSERT OR UPDATE OF parent_id, name ON categories
FOR EACH ROW EXECUTE FUNCTION update_category_ltree_path();

步骤5：查询方式

-- 方式1：直接转成ltree类型匹配（注意路径用点分隔）
SELECT category_id, name, description
FROM categories
WHERE path = 'motorcycles.wheels.tires'::ltree;

-- 方式2：用text2ltree函数转换斜杠分隔的路径
SELECT category_id, name, description
FROM categories
WHERE path = text2ltree('motorcycles/wheels/tires', '/');

提示：ltree还支持更灵活的查询，比如匹配所有以motorcycles.wheels为前缀的子分类，性能比字符串匹配高很多，适合复杂的树形路径查询场景。

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