C# Mysql 单表父子多层级数据迁移_c# 高效遍历父子关系海量数据-优快云博客

本文链接：https://blog.youkuaiyun.com/hefeng_aspnet/article/details/147948169

以下是针对单表父子层级数据迁移的完整解决方案，采用层级遍历方式处理多级嵌套关系：

一、迁移流程图解

graph TD
A[读取根节点] --> B[插入根节点并记录ID映射]
B --> C[处理子节点队列]
C --> D{队列是否为空?}
D -->|是| E[完成迁移]
D -->|否| F[取出节点并插入子节点]
F --> G[更新子节点PID并记录映射]
G --> C

二、完整实现代码

public class TreeNode
{
public int OldId { get; set; }
public int? OldParentId { get; set; }
public string Data { get; set; }
}

public void MigrateHierarchicalData(string sourceConnStr, string targetConnStr)
{
// 禁用外键约束
using (var targetConn = new MySqlConnection(targetConnStr))
{
targetConn.Open();
new MySqlCommand("SET FOREIGN_KEY_CHECKS = 0", targetConn).ExecuteNonQuery();
}

// 获取原始数据
var allNodes = GetSourceData(sourceConnStr);

// 创建ID映射字典
Dictionary<int, int> idMap = new Dictionary<int, int>();
Queue<TreeNode> processQueue = new Queue<TreeNode>();

// 使用事务处理
using (var targetConn = new MySqlConnection(targetConnStr))
{
targetConn.Open();
using (var transaction = targetConn.BeginTransaction())
{
try
{
// 第一阶段：插入所有根节点
var rootNodes = allNodes.Where(n => !n.OldParentId.HasValue).ToList();
foreach (var node in rootNodes)
{
var newId = InsertNode(node, null, targetConn, transaction);
idMap.Add(node.OldId, newId);
processQueue.Enqueue(node);
}

// 第二阶段：层级遍历处理子节点
while (processQueue.Count > 0)
{
var current = processQueue.Dequeue();
var children = allNodes.Where(n =>
n.OldParentId == current.OldId).ToList();

foreach (var child in children)
{
var newId = InsertNode(child, idMap[current.OldId],
targetConn, transaction);
idMap.Add(child.OldId, newId);
processQueue.Enqueue(child);
}
}

transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}

// 恢复外键约束
using (var targetConn = new MySqlConnection(targetConnStr))
{
targetConn.Open();
new MySqlCommand("SET FOREIGN_KEY_CHECKS = 1", targetConn).ExecuteNonQuery();
}
}

private List<TreeNode> GetSourceData(string connStr)
{
var nodes = new List<TreeNode>();
using (var conn = new MySqlConnection(connStr))
{
conn.Open();
var cmd = new MySqlCommand("SELECT id, pid, data FROM hierarchy_table", conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
nodes.Add(new TreeNode
{
OldId = reader.GetInt32(0),
OldParentId = reader.IsDBNull(1) ? null : (int?)reader.GetInt32(1),
Data = reader.GetString(2)
});
}
}
}
return nodes;
}

private int InsertNode(TreeNode node, int? newParentId,
MySqlConnection conn, MySqlTransaction transaction)
{
var cmd = new MySqlCommand(
"INSERT INTO hierarchy_table (pid, data) VALUES (@pid, @data);
SELECT LAST_INSERT_ID();",
conn, transaction);

cmd.Parameters.AddWithValue("@pid", newParentId ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@data", node.Data);

return Convert.ToInt32(cmd.ExecuteScalar());
}

三、关键实现逻辑说明

1、层级遍历算法

使用队列实现广度优先遍历
确保父节点总是先于子节点插入
自动处理任意深度层级关系

2、ID映射维护

idMap字典存储原始ID与新ID对应关系
插入时即时更新映射关系

idMap.Add(child.OldId, newId);

3、事务控制

整个迁移过程在单个事务中完成
支持百万级数据迁移（需调整事务粒度）

四、性能优化方案

1、批量插入优化

// 每次批量插入500条
const int batchSize = 500;
var bulkInsertCmd = new MySqlCommand();
var parameters = new List<MySqlParameter>();

for (int i = 0; i < batchSize; i++)
{
bulkInsertCmd.CommandText +=
$"INSERT INTO hierarchy_table (...) VALUES (@pid{i}, @data{i});";
parameters.Add(new MySqlParameter($"@pid{i}", MySqlDbType.Int32));
parameters.Add(new MySqlParameter($"@data{i}", MySqlDbType.VarChar));
}
bulkInsertCmd.Parameters.AddRange(parameters.ToArray());

2、内存分页处理

int pageSize = 10000;
for (int page = 0; page < Math.Ceiling(allNodes.Count / (double)pageSize); page++)
{
var currentPage = allNodes.Skip(page * pageSize).Take(pageSize).ToList();
// 处理当前分页数据...
}

五、数据验证方法

1、层级完整性检查

WITH RECURSIVE cte AS (
SELECT id, pid, 1 as level
FROM hierarchy_table
WHERE pid IS NULL
UNION ALL
SELECT t.id, t.pid, cte.level + 1
FROM hierarchy_table t
INNER JOIN cte ON t.pid = cte.id
)
SELECT MAX(level) as max_depth, COUNT(*) as total_nodes
FROM cte;