Spring Boot家谱应用复杂MySQL查询构建及报错解决求助

我正在基于Spring Boot开发类似Ancestry.com的家谱数据展示Web API，数据存储在MySQL中，核心是由多个核心家庭（父母+子女）组成的树状结构。目前无法高效整合这些核心家庭，仅通过指针标识成员在家庭中的子女或配偶角色（多数人兼具两种角色）。我缺乏复杂MySQL查询经验，借助ChatGPT生成的递归查询出现**Error Code:1292（截断不正确的DOUBLE值）**错误，现求助合适的查询方案，以在Spring Boot中获取可直接用于HTML展示的有序家谱树对象。

报错的递归查询语句 #

WITH RECURSIVE FamilyHierarchy AS (
    SELECT
        f.id,
        f.family_pointer,
        f.marriage_date,
        f.husband_id,
        f.wife_id,
        CAST(i.individual_pointer AS CHAR) AS child_token,
        CAST(i.individual_pointer AS CHAR) AS spouse_token,
        0 AS generation
    FROM
        tree_families tf
        JOIN Family f ON f.id = tf.families_id
        JOIN Individual i ON i.family_child_token = f.family_pointer OR i.family_spouse_token = f.family_pointer
    WHERE
        tf.tree_id = 9 -- Specify the tree ID here

    UNION ALL

    SELECT
        f.id,
        f.family_pointer,
        f.marriage_date,
        f.husband_id,
        f.wife_id,
        CAST(i.individual_pointer AS CHAR) AS child_token,
        CAST(i.individual_pointer AS CHAR) AS spouse_token,
        fh.generation + 1
    FROM
        FamilyHierarchy fh
        JOIN Family f ON f.husband_id = fh.child_token OR f.wife_id = fh.child_token
        JOIN Individual i ON i.family_child_token = f.family_pointer OR i.family_spouse_token = f.family_pointer
)
SELECT
    fh.id,
    fh.family_pointer,
    fh.marriage_date,
    fh.generation,
    h.name AS husband_name,
    w.name AS wife_name,
    c.name AS child_name
FROM
    FamilyHierarchy fh
    LEFT JOIN Individual h ON fh.husband_id = h.individual_pointer
    LEFT JOIN Individual w ON fh.wife_id = w.individual_pointer
    LEFT JOIN Individual c ON fh.child_token = c.individual_pointer
ORDER BY
    fh.generation;

表结构说明 #

• tree_families：关联家谱树与核心家庭，包含tree_id、families_id字段
• Family：存储核心家庭信息，包含id、family_pointer、marriage_date、husband_id、wife_id字段
• Individual：存储家庭成员信息，包含individual_pointer、name、family_child_token、family_spouse_token字段（family_child_token关联成员作为子女所在家庭的family_pointer，family_spouse_token关联成员作为配偶所在家庭的family_pointer）

错误原因分析 #

Error Code:1292是字段类型不匹配导致的：

1. 递归查询中fh.child_token被强制转为字符串类型，但f.husband_id/f.wife_id应为数值类型，隐式类型转换触发截断错误
2. 初始查询关联Individual的逻辑混乱，将家庭的配偶与子女混为一谈，导致后续递归关联逻辑错误

修正后的递归查询方案 #

WITH RECURSIVE FamilyHierarchy AS (
    -- 初始节点：从指定树的根家庭开始，获取父母及他们的子女
    SELECT
        f.id AS family_id,
        f.family_pointer,
        f.marriage_date,
        f.husband_id,
        f.wife_id,
        i.individual_pointer AS child_id,
        0 AS generation,
        -- 记录遍历路径，用于有序展示和避免循环
        CONCAT(f.id, ',', COALESCE(i.individual_pointer, '')) AS path
    FROM tree_families tf
    JOIN Family f ON tf.families_id = f.id
    -- 仅关联当前家庭的子女（配偶通过家庭的husband/wife_id直接关联）
    LEFT JOIN Individual i ON i.family_child_token = f.family_pointer
    WHERE tf.tree_id = 9 -- 指定目标家谱树ID

    UNION ALL

    -- 递归节点：找到当前子女作为父母的家庭，继续向下遍历
    SELECT
        f.id AS family_id,
        f.family_pointer,
        f.marriage_date,
        f.husband_id,
        f.wife_id,
        i.individual_pointer AS child_id,
        fh.generation + 1,
        CONCAT(fh.path, ',', f.id, ',', COALESCE(i.individual_pointer, ''))
    FROM FamilyHierarchy fh
    -- 关联当前子女作为丈夫/妻子的家庭
    JOIN Family f ON f.husband_id = fh.child_id OR f.wife_id = fh.child_id
    -- 关联该家庭的子女
    LEFT JOIN Individual i ON i.family_child_token = f.family_pointer
)
-- 最终查询：关联成员姓名，按世代和路径排序
SELECT
    fh.family_id,
    fh.family_pointer,
    fh.marriage_date,
    fh.generation,
    h.name AS husband_name,
    w.name AS wife_name,
    c.name AS child_name,
    fh.path
FROM FamilyHierarchy fh
LEFT JOIN Individual h ON fh.husband_id = h.individual_pointer
LEFT JOIN Individual w ON fh.wife_id = w.individual_pointer
LEFT JOIN Individual c ON fh.child_id = c.individual_pointer
ORDER BY fh.generation, fh.path;

关键优化点 #

• 类型匹配：移除不必要的类型转换，确保关联字段类型一致（假设husband_id/wife_id/individual_pointer均为数值类型）
• 逻辑清晰：拆分配偶与子女的关联逻辑，避免角色混淆
• 路径追踪：新增path字段记录遍历路径，保证展示顺序的同时防止递归循环
• 数据完整性：使用LEFT JOIN处理无子女的家庭，避免丢失数据

Spring Boot中组装树状结构 #

查询结果可映射为实体类，再在Service层将扁平数据组装为树状结构：

// 核心家庭节点实体
public class FamilyNode {
    private Long familyId;
    private String marriageDate;
    private String husbandName;
    private String wifeName;
    private int generation;
    private List<IndividualNode> children;
    private List<FamilyNode> descendantFamilies; // 子女组建的家庭

    // getter、setter 略
}

// 家庭成员实体
public class IndividualNode {
    private Long individualId;
    private String name;

    // getter、setter 略
}

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