如何在特殊层级条件下用MySQL或Go更新grade_id字段

需求说明 #

给定表数据（假设表名为node_table），需按规则更新grade_id列：

• 原始数据：

id    parent_id  grade_id 
test    admin   
test1   test    
test2   test    
test3   test1   
test4   test    

• 更新后目标数据：

id    parent_id    grade_id 
test    admin   
test1   test    admin
test2   test    admin
test3   test1   test
test4   test    admin

• 规则：
  • 若节点的父节点存在于表中，则当前节点的grade_id设为父节点的parent_id值
  • 若节点的父节点不在表中（如test的父节点admin），则该节点grade_id保持为空
  • 递归生效：子节点的grade_id继承其父节点的parent_id值

MySQL实现方案 #

利用递归CTE（公共表表达式）遍历层级关系，计算每个节点的目标grade_id后批量更新：

WITH RECURSIVE node_grade AS (
    -- 基础节点：父节点不在表中的节点，grade_id为空
    SELECT n.id, n.parent_id, NULL AS target_grade
    FROM node_table n
    LEFT JOIN node_table p ON n.parent_id = p.id
    WHERE p.id IS NULL
    UNION ALL
    -- 递归节点：子节点的target_grade等于父节点的parent_id
    SELECT n.id, n.parent_id, p.parent_id AS target_grade
    FROM node_table n
    JOIN node_grade p ON n.parent_id = p.id
)
-- 执行更新
UPDATE node_table nt
JOIN node_grade ng ON nt.id = ng.id
SET nt.grade_id = ng.target_grade
WHERE ng.target_grade IS NOT NULL;

说明 #

• 递归CTE先识别出所有顶级节点（父节点不在表中），再逐层向下计算子节点的目标grade_id
• 通过JOIN关联原表与递归结果，仅更新有有效目标值的节点，避免空值覆盖

Go实现方案 #

通过查询全表构建节点映射，遍历计算每个节点的grade_id后批量更新：

1. 定义数据结构 #

type Node struct {
    ID        string `db:"id"`
    ParentID  string `db:"parent_id"`
    GradeID   string `db:"grade_id"`
}

2. 完整更新逻辑 #

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

func updateGradeIDs(db *sql.DB) error {
    // 查询全表节点
    rows, err := db.Query("SELECT id, parent_id, grade_id FROM node_table")
    if err != nil {
        return err
    }
    defer rows.Close()

    var nodes []Node
    nodeMap := make(map[string]Node)
    for rows.Next() {
        var n Node
        if err := rows.Scan(&n.ID, &n.ParentID, &n.GradeID); err != nil {
            return err
        }
        nodes = append(nodes, n)
        nodeMap[n.ID] = n
    }
    if err := rows.Err(); err != nil {
        return err
    }

    // 开启事务批量更新
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    for _, node := range nodes {
        // 父节点不在表中，跳过更新
        parent, exists := nodeMap[node.ParentID]
        if !exists {
            continue
        }
        // 父节点的parent_id为空，跳过更新
        if parent.ParentID == "" {
            continue
        }
        // 更新当前节点的grade_id
        _, err := tx.Exec("UPDATE node_table SET grade_id = ? WHERE id = ?", parent.ParentID, node.ID)
        if err != nil {
            return err
        }
    }

    return tx.Commit()
}

说明 #

• 用map存储节点ID与节点的映射，快速查找父节点信息
• 事务保证更新操作的原子性，避免部分更新失败导致数据不一致
• 跳过无有效父节点或父节点无parent_id的节点，符合需求规则

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