MySQL多对多实体关系查询：获取指定实体±1层级关联实体

看起来你已经有了一个不错的基础设计，咱们一步步来解决你的问题——先从表结构说起，再修正SQL逻辑，最后聊聊方案选择的考量。

你采用的邻接表模型（entities存实体基础信息，entity_relations存父子关联）在MySQL 5.7环境下完全适配你的需求，这种模型简单直观，适合处理层级嵌套且仅需查询相邻层级的场景。不过有两个小优化点可以提升效率：

• 把entity_relations.id_parent的类型改成int(11) unsigned，和id_entity保持一致，避免隐式类型转换；
• 给entity_relations.id_entity单独加普通索引，因为查询父实体时会频繁用到这个字段做关联。

你需要的是当前层级±1的关联实体（不含自身、祖父母、孙辈），还要标注关系类型、排序并支持分页。我们可以用UNION ALL（比UNION效率更高，因为不需要去重）把三个关联查询合并，在外层统一排序和分页，全程在SQL层面完成逻辑，不需要PHP合并结果。

MySQL 5.7兼容版SQL（适配无CTE的特性） #

SELECT e.name, r.relation
FROM (
    -- 1. 父实体：目标实体的所有直接父节点（排除根节点的伪父ID=0）
    SELECT er.id_parent AS entity_id, 'parent' AS relation
    FROM entity_relations er
    WHERE er.id_entity = (SELECT id FROM entities WHERE name = 'Sibling 1')
      AND er.id_parent != 0
    
    UNION ALL
    
    -- 2. 子实体：目标实体的所有直接子节点
    SELECT er.id_entity AS entity_id, 'child' AS relation
    FROM entity_relations er
    WHERE er.id_parent = (SELECT id FROM entities WHERE name = 'Sibling 1')
    
    UNION ALL
    
    -- 3. 兄弟实体：和目标实体共享任意父节点的其他实体（排除自身）
    SELECT er.id_entity AS entity_id, 'sibling' AS relation
    FROM entity_relations er
    WHERE er.id_parent IN (
        SELECT id_parent FROM entity_relations er
        WHERE er.id_entity = (SELECT id FROM entities WHERE name = 'Sibling 1')
          AND er.id_parent != 0
    )
      AND er.id_entity != (SELECT id FROM entities WHERE name = 'Sibling 1')
) r
JOIN entities e ON r.entity_id = e.id
-- 按名称排序，加id确保稳定性（避免同名实体顺序混乱）
ORDER BY e.name ASC, e.id ASC
-- 分页示例：第1页，每页10条
LIMIT 0, 10;

这个查询会返回你预期的结果：

你的原查询问题出在哪？ #

• 父/子关联方向搞反了：查父实体应该找id_entity = 目标ID的id_parent，你原逻辑用反了关联条件；
• 完全缺失兄弟实体的查询逻辑：需要先找到目标的父节点，再筛选这些父节点下的其他实体；
• 没有标注关系类型，也没处理分页的排序稳定性。

用LIMIT offset, row_count即可实现分页，注意必须把ORDER BY放在LIMIT之前，确保分页顺序稳定。如果需要统计总条数，可以单独写一个结构相同的COUNT(*)查询，或者在PHP中执行两次查询（一次查总数，一次查分页数据）。

存储过程是可选方案，适合以下场景：

• 这个查询会被多个业务场景重复调用，封装成存储过程可以减少代码冗余；
• 希望把SQL逻辑和PHP代码解耦，后续修改查询逻辑时无需改动PHP代码。

示例存储过程 #

DELIMITER //
CREATE PROCEDURE GetAdjacentEntities(
    IN entity_name VARCHAR(200),
    IN page_offset INT,
    IN page_size INT,
    OUT total_count INT
)
BEGIN
    -- 先统计总条数
    SELECT COUNT(*) INTO total_count
    FROM (
        SELECT er.id_parent AS entity_id
        FROM entity_relations er
        WHERE er.id_entity = (SELECT id FROM entities WHERE name = entity_name)
          AND er.id_parent != 0
        
        UNION ALL
        
        SELECT er.id_entity AS entity_id
        FROM entity_relations er
        WHERE er.id_parent = (SELECT id FROM entities WHERE name = entity_name)
        
        UNION ALL
        
        SELECT er.id_entity AS entity_id
        FROM entity_relations er
        WHERE er.id_parent IN (
            SELECT id_parent FROM entity_relations er
            WHERE er.id_entity = (SELECT id FROM entities WHERE name = entity_name)
              AND er.id_parent != 0
        )
          AND er.id_entity != (SELECT id FROM entities WHERE name = entity_name)
    ) r;
    
    -- 分页查询结果
    SELECT e.name, r.relation
    FROM (
        SELECT er.id_parent AS entity_id, 'parent' AS relation
        FROM entity_relations er
        WHERE er.id_entity = (SELECT id FROM entities WHERE name = entity_name)
          AND er.id_parent != 0
        
        UNION ALL
        
        SELECT er.id_entity AS entity_id, 'child' AS relation
        FROM entity_relations er
        WHERE er.id_parent = (SELECT id FROM entities WHERE name = entity_name)
        
        UNION ALL
        
        SELECT er.id_entity AS entity_id, 'sibling' AS relation
        FROM entity_relations er
        WHERE er.id_parent IN (
            SELECT id_parent FROM entity_relations er
            WHERE er.id_entity = (SELECT id FROM entities WHERE name = entity_name)
              AND er.id_parent != 0
        )
          AND er.id_entity != (SELECT id FROM entities WHERE name = entity_name)
    ) r
    JOIN entities e ON r.entity_id = e.id
    ORDER BY e.name ASC, e.id ASC
    LIMIT page_offset, page_size;
END //
DELIMITER ;

调用方式：

SET @total = 0;
CALL GetAdjacentEntities('Sibling 1', 0, 10, @total);
SELECT @total; -- 获取总条数

如果查询逻辑不常变动，直接在PHP中写参数化SQL更灵活，不需要维护存储过程，也减少数据库层面的复杂度。

对于你的需求（仅查询±1层级），邻接表已经是最优选择。其他模型比如嵌套集或闭包表适合需要查询整个层级树的场景，但插入、更新逻辑复杂，对你的需求来说反而冗余，所以保持现有表结构即可。

内容的提问来源于stack exchange，提问作者B. Greenstein