如何在PostgreSQL中实现同表多行关联？搭建类亚马逊商品分类架构

核心结论 #

不用图数据库，PostgreSQL完全能满足你的需求——项目规模小且是单体架构，用关系型数据库的方案足够灵活、易维护。

你的初始方案问题分析 #

你用数组存父分类UUID的思路能实现，但存在几个硬伤：

• 数组类型的外键约束只能检查元素是否存在，无法自动处理级联删除/更新（比如父分类删除时，得手动遍历子分类的数组删除对应ID）
• 数组里容易出现重复ID，需要额外逻辑去重
• 查询嵌套结构时需要展开数组，虽然能做，但不如关联表直观高效

可行实现方案 #

方案一：基于你的数组方案实现嵌套查询 #

如果坚持用数组设计，可以通过PostgreSQL的unnest展开数组，再关联分类表聚合得到你要的结构：

查询所有分类及父分类

SELECT
    c.id,
    c.name,
    json_agg(DISTINCT json_build_object('id', p.id, 'name', p.name)) AS parent_categories
FROM categories c
LEFT JOIN unnest(c.parent_categories) AS pc(parent_id) ON TRUE
LEFT JOIN categories p ON pc.parent_id = p.id
GROUP BY c.id, c.name;

（加DISTINCT避免数组重复ID导致的重复父分类）

查询指定商品的分类及父分类

SELECT
    c.id,
    c.name,
    json_agg(DISTINCT json_build_object('id', p.id, 'name', p.name)) AS parent_categories
FROM products pr
LEFT JOIN unnest(pr.category_id) AS cat_ids(cat_id) ON TRUE
LEFT JOIN categories c ON cat_ids.cat_id = c.id
LEFT JOIN unnest(c.parent_categories) AS pc(parent_id) ON TRUE
LEFT JOIN categories p ON pc.parent_id = p.id
WHERE pr.id = '你的商品UUID'
GROUP BY c.id, c.name;

方案二：规范多对多关联表设计（更推荐） #

分类与父分类、商品与分类都是多对多关系，用关联表是关系型数据库的标准设计，维护和扩展更方便：

创建表结构

-- 分类主表
CREATE TABLE categories (
    id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
    name VARCHAR NOT NULL UNIQUE
);

-- 分类-父分类关联表（多对多）
CREATE TABLE category_parent (
    category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    parent_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (category_id, parent_id),
    CHECK (category_id != parent_id) -- 禁止分类自关联为父分类
);

-- 商品主表
CREATE TABLE products (
    id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
    name VARCHAR NOT NULL
);

-- 商品-分类关联表（多对多）
CREATE TABLE product_category (
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, category_id)
);

查询嵌套结构

所有分类及父分类

SELECT
    c.id,
    c.name,
    json_agg(json_build_object('id', p.id, 'name', p.name)) AS parent_categories
FROM categories c
LEFT JOIN category_parent cp ON c.id = cp.category_id
LEFT JOIN categories p ON cp.parent_id = p.id
GROUP BY c.id, c.name;

指定商品的分类及父分类

SELECT
    c.id,
    c.name,
    json_agg(json_build_object('id', p.id, 'name', p.name)) AS parent_categories
FROM products pr
LEFT JOIN product_category pc ON pr.id = pc.product_id
LEFT JOIN categories c ON pc.category_id = c.id
LEFT JOIN category_parent cp ON c.id = cp.category_id
LEFT JOIN categories p ON cp.parent_id = p.id
WHERE pr.id = '你的商品UUID'
GROUP BY c.id, c.name;

方案二优势

• 自动级联处理：删除父分类/商品时，关联表的记录会自动删除，无需手动维护
• 无重复数据：主键约束保证关联关系唯一
• 易扩展：可以给category_parent加sort_order字段，控制父分类的展示顺序（类似亚马逊的分类优先级）
• 索引友好：给关联表的外键字段建索引，查询速度更快

总结 #

小规模单体项目用PostgreSQL完全够用，没必要引入图数据库增加复杂度。推荐用方案二的多对多关联表设计，规范且易维护；如果要沿用数组方案，方案一的查询逻辑可以生成你需要的嵌套结构，但要注意手动维护数组的一致性。

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