如何编写MySQL查询找出child与parents关联表的数据不一致？

我们有两张存在父子关联的表：

child表 #

建表语句 #

CREATE TABLE child (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `direct_parent_id` int(11) DEFAULT NULL,
  KEY `direct_parent_id_child_fk` (`direct_parent_id`),
  CONSTRAINT `direct_parent_id_child_fk` FOREIGN KEY (`direct_parent_id`) REFERENCES `child` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

表数据 #

parents表 #

建表语句 #

CREATE TABLE `parents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `child_id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `brand_id_parent_id_unx` (`child_id`,`parent_id`),
  KEY `parents_child_fk_idx` (`child_id`),
  KEY `parents_child_fk_2_idx` (`parent_id`),
  CONSTRAINT `parents_brand_fk` FOREIGN KEY (`child_id`) REFERENCES `child` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `brand_tree_brand_fk_2` FOREIGN KEY (`parent_id`) REFERENCES `child` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

表要求 #

该表需为每个child记录以下关联关系：

• 自身
• 直接父节点
• 父节点的所有上级父节点

示例正确数据 #

需求 #

编写SQL查询（或多个查询），找出parents表中与child表层级关系不符的child_id。例如：child_id 300的直接父节点是200，因此其所有上级父节点都应关联到300，parents表中需存在300->300、300->200、300->100三条记录；若缺少其中任意一条，或存在额外记录（如300->400），则需将300纳入查询结果。

解决方案 #

要找出不符合要求的child_id，可分两步排查：缺少必要父节点的child_id、存在多余父节点的child_id，最后合并结果。

1. 生成每个child应有的全量父节点集合 #

用递归CTE生成每个child的完整层级父节点链（包含自身），作为判断基准：

WITH RECURSIVE child_hierarchy AS (
    -- 初始节点：自身
    SELECT 
        id AS child_id,
        id AS parent_id
    FROM child
    UNION ALL
    -- 递归获取所有上级父节点
    SELECT 
        ch.child_id,
        c.direct_parent_id AS parent_id
    FROM child_hierarchy ch
    JOIN child c ON ch.parent_id = c.id
    WHERE c.direct_parent_id != c.id -- 避免自环无限递归
)
SELECT child_id, parent_id FROM child_hierarchy;

2. 找出缺少必要记录的child_id #

对比基准集合与parents表现有记录，筛选出缺失必要父节点的child_id：

WITH RECURSIVE child_hierarchy AS (
    SELECT id AS child_id, id AS parent_id FROM child
    UNION ALL
    SELECT ch.child_id, c.direct_parent_id AS parent_id
    FROM child_hierarchy ch
    JOIN child c ON ch.parent_id = c.id
    WHERE c.direct_parent_id != c.id
)
SELECT DISTINCT ch.child_id
FROM child_hierarchy ch
LEFT JOIN parents p ON ch.child_id = p.child_id AND ch.parent_id = p.parent_id
WHERE p.id IS NULL;

3. 找出存在多余记录的child_id #

筛选出parents表中不属于基准层级链的记录对应的child_id：

WITH RECURSIVE child_hierarchy AS (
    SELECT id AS child_id, id AS parent_id FROM child
    UNION ALL
    SELECT ch.child_id, c.direct_parent_id AS parent_id
    FROM child_hierarchy ch
    JOIN child c ON ch.parent_id = c.id
    WHERE c.direct_parent_id != c.id
)
SELECT DISTINCT p.child_id
FROM parents p
LEFT JOIN child_hierarchy ch ON p.child_id = ch.child_id AND p.parent_id = ch.parent_id
WHERE ch.child_id IS NULL;

4. 合并两类结果 #

将上述两个查询的结果合并，得到所有不符合要求的child_id：

WITH RECURSIVE child_hierarchy AS (
    SELECT id AS child_id, id AS parent_id FROM child
    UNION ALL
    SELECT ch.child_id, c.direct_parent_id AS parent_id
    FROM child_hierarchy ch
    JOIN child c ON ch.parent_id = c.id
    WHERE c.direct_parent_id != c.id
),
missing_parents AS (
    SELECT DISTINCT ch.child_id
    FROM child_hierarchy ch
    LEFT JOIN parents p ON ch.child_id = p.child_id AND ch.parent_id = p.parent_id
    WHERE p.id IS NULL
),
extra_parents AS (
    SELECT DISTINCT p.child_id
    FROM parents p
    LEFT JOIN child_hierarchy ch ON p.child_id = ch.child_id AND p.parent_id = ch.parent_id
    WHERE ch.child_id IS NULL
)
SELECT child_id FROM missing_parents
UNION
SELECT child_id FROM extra_parents;

说明 #

• 递归CTEchild_hierarchy生成的是每个child的标准父节点链，是判断的核心基准。
• missing_parents定位缺少必要父节点的child_id，extra_parents定位存在无效父节点的child_id。
• 最终通过UNION去重合并两类结果，得到所有不符合层级要求的child_id。

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