PHP递归更新MySQL的最优方案咨询（支持百万级数据）

首先，我明确你的需求：插入新记录（ID7，BAnumber=10008）后，需要递归遍历其所有父节点链（10005 → 10003 → 10001），并根据子节点的Side值更新父节点对应的LastA/LastB字段。结合你未来要处理200万行数据的场景，我给你两种适配不同MySQL版本的方案，以及性能优化建议：

先整理你的表结构与测试数据 #

表结构 #

CREATE TABLE your_table (
    ID INT PRIMARY KEY,
    BAnumber VARCHAR(20),
    DateEntry DATE,
    Parent VARCHAR(20),
    Side CHAR(1),
    LastA DATE,
    LastB DATE
);

现有测试数据 #

待插入的新记录 #

INSERT INTO your_table (ID, BAnumber, DateEntry, Parent, Side)
VALUES (7, '10008', '2018-03-20', '10005', 'B');

方案1：MySQL 8.0+ 用递归CTE实现（推荐，适合大数据量） #

递归CTE可以一次性找出所有祖先节点，然后批量更新，比循环存储过程高效得多，非常适合未来200万行的场景。

完整代码 #

START TRANSACTION;

-- 插入新记录
INSERT INTO your_table (ID, BAnumber, DateEntry, Parent, Side)
VALUES (7, '10008', '2018-03-20', '10005', 'B');

-- 递归遍历祖先链并更新对应Last字段
WITH RECURSIVE ancestor_chain AS (
    -- 起始：获取新记录的父节点、最新日期和子节点Side
    SELECT 
        Parent AS ban, 
        DateEntry AS latest_date, 
        Side AS child_side
    FROM your_table WHERE ID = 7
    UNION ALL
    -- 递归：向上遍历父节点的父节点，传递最新日期
    SELECT 
        t.Parent, 
        ac.latest_date, 
        t.Side
    FROM your_table t
    JOIN ancestor_chain ac ON t.BAnumber = ac.ban
    WHERE t.Parent IS NOT NULL AND t.Parent != ''
)
UPDATE your_table t
JOIN ancestor_chain ac ON t.BAnumber = ac.ban
SET 
    LastA = CASE WHEN ac.child_side = 'A' THEN ac.latest_date ELSE LastA END,
    LastB = CASE WHEN ac.child_side = 'B' THEN ac.latest_date ELSE LastB END;

COMMIT;

效果说明 #

执行后：

• BAnumber=10005的LastB会被更新为2018-03-20
• BAnumber=10003的LastA会被更新为2018-03-20（因为10003的Side是A，继承子节点的最新日期）
• BAnumber=10001的Parent为空，停止递归

方案2：MySQL <8.0 用存储过程递归更新 #

如果你的MySQL版本不支持CTE，就用存储过程循环遍历父节点：

存储过程代码 #

DELIMITER //

CREATE PROCEDURE UpdateAncestorLast(IN new_record_id INT)
BEGIN
    DECLARE current_parent VARCHAR(20);
    DECLARE latest_dt DATE;
    DECLARE child_side CHAR(1);
    
    -- 获取新记录的核心信息
    SELECT Parent, DateEntry, Side 
    INTO current_parent, latest_dt, child_side
    FROM your_table WHERE ID = new_record_id;
    
    -- 循环遍历所有父节点
    WHILE current_parent IS NOT NULL AND current_parent != '' DO
        -- 根据子节点Side更新对应Last字段
        IF child_side = 'A' THEN
            UPDATE your_table SET LastA = latest_dt WHERE BAnumber = current_parent;
        ELSEIF child_side = 'B' THEN
            UPDATE your_table SET LastB = latest_dt WHERE BAnumber = current_parent;
        END IF;
        
        -- 获取下一级父节点和该节点的Side
        SELECT Parent, Side INTO current_parent, child_side
        FROM your_table WHERE BAnumber = current_parent;
    END WHILE;
END //

DELIMITER ;

使用方式 #

START TRANSACTION;
INSERT INTO your_table (ID, BAnumber, DateEntry, Parent, Side)
VALUES (7, '10008', '2018-03-20', '10005', 'B');
CALL UpdateAncestorLast(7);
COMMIT;

针对200万行数据的性能优化建议 #

1. 添加索引：给Parent和BAnumber建立索引，大幅提升递归查询速度：

   CREATE INDEX idx_parent_ban ON your_table (Parent, BAnumber);
   CREATE INDEX idx_ban ON your_table (BAnumber);
   

2. 优先用CTE批量更新：避免存储过程的单行循环更新，批量更新能减少事务开销，更适合大数据量。
3. 维护路径字段（可选）：如果递归链很长，可以在插入时维护一个ancestor_path字段（比如10001,10003,10005），这样直接用FIND_IN_SET就能批量更新所有祖先节点，不需要递归查询，但会增加存储成本。
4. 事务控制：始终把插入和更新放在同一个事务里，保证数据一致性，避免中间状态。

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