ieplus/sql/update_learn_knowledge_tree_ancestors.sql

-- ============================================
-- 填充 learn_knowledge_tree 表的 ancestors 字段
-- ============================================
-- 说明：ancestors 字段存储从根节点到当前节点的所有祖先ID，用逗号分隔
-- 例如：如果节点层级是 1 -> 2 -> 3，那么节点3的ancestors应该是 "1,2"
-- ============================================

-- 方法1：使用存储过程（推荐，兼容MySQL 5.7+）
-- 这个方法会递归查找所有节点的祖先并更新ancestors字段

-- ============================================
-- 方法2：使用递归CTE（MySQL 8.0+推荐，性能更好）
-- ============================================

-- 第一步：先清空所有ancestors字段（如果需要重新生成）
-- UPDATE learn_knowledge_tree SET ancestors = '';

-- 第二步：使用递归CTE更新ancestors字段
WITH RECURSIVE knowledge_tree_path AS (
    -- 基础查询：根节点（pid为0或NULL的节点）
    SELECT 
        id,
        pid,
        CAST('' AS CHAR(1000)) AS ancestors_path,
        0 AS depth
    FROM learn_knowledge_tree
    WHERE pid IS NULL OR pid = 0
    
    UNION ALL
    
    -- 递归查询：子节点
    SELECT 
        t.id,
        t.pid,
        CASE 
            WHEN tp.ancestors_path = '' THEN CAST(tp.id AS CHAR(1000))
            ELSE CONCAT(tp.ancestors_path, ',', tp.id)
        END AS ancestors_path,
        tp.depth + 1
    FROM learn_knowledge_tree t
    INNER JOIN knowledge_tree_path tp ON t.pid = tp.id
    WHERE tp.depth < 20  -- 防止无限递归，假设最多20层
)
UPDATE learn_knowledge_tree lkt
INNER JOIN knowledge_tree_path ktp ON lkt.id = ktp.id
SET lkt.ancestors = ktp.ancestors_path;

-- ============================================
-- 方法3：简单循环更新（适合小数据量，MySQL 5.7及以下）
-- ============================================

DELIMITER $$

DROP PROCEDURE IF EXISTS update_knowledge_tree_ancestors$$

CREATE PROCEDURE update_knowledge_tree_ancestors()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id BIGINT;
    DECLARE v_pid BIGINT;
    DECLARE v_ancestors VARCHAR(1000);
    DECLARE v_parent_ancestors VARCHAR(1000);
    
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT id, pid FROM learn_knowledge_tree ORDER BY level, id;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 先清空所有ancestors
    UPDATE learn_knowledge_tree SET ancestors = '';
    
    -- 打开游标
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_id, v_pid;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 如果是根节点，ancestors为空
        IF v_pid IS NULL OR v_pid = 0 THEN
            SET v_ancestors = '';
        ELSE
            -- 获取父节点的ancestors
            SELECT ancestors INTO v_parent_ancestors 
            FROM learn_knowledge_tree 
            WHERE id = v_pid;
            
            -- 构建当前节点的ancestors
            IF v_parent_ancestors IS NULL OR v_parent_ancestors = '' THEN
                SET v_ancestors = CAST(v_pid AS CHAR);
            ELSE
                SET v_ancestors = CONCAT(v_parent_ancestors, ',', v_pid);
            END IF;
        END IF;
        
        -- 更新当前节点的ancestors
        UPDATE learn_knowledge_tree 
        SET ancestors = v_ancestors 
        WHERE id = v_id;
        
    END LOOP;
    
    CLOSE cur;
END$$

DELIMITER ;

-- 执行存储过程
CALL update_knowledge_tree_ancestors();

-- 删除存储过程（可选）
-- DROP PROCEDURE IF EXISTS update_knowledge_tree_ancestors;

-- ============================================
-- 方法3：简单循环更新（适合小数据量）
-- ============================================

-- 第一步：更新根节点（ancestors为空）
UPDATE learn_knowledge_tree 
SET ancestors = '' 
WHERE pid IS NULL OR pid = 0;

-- 第二步：循环更新子节点（需要多次执行，直到没有更多更新）
-- 执行多次，直到受影响的行数为0
UPDATE learn_knowledge_tree t1
INNER JOIN learn_knowledge_tree t2 ON t1.pid = t2.id
SET t1.ancestors = CASE 
    WHEN t2.ancestors IS NULL OR t2.ancestors = '' THEN CAST(t2.id AS CHAR)
    ELSE CONCAT(t2.ancestors, ',', t2.id)
END
WHERE (t1.ancestors IS NULL OR t1.ancestors = '')
  AND t1.pid IS NOT NULL AND t1.pid != 0;

-- 重复执行上面的UPDATE语句，直到受影响的行数为0
-- 通常需要执行的次数等于树的最大深度

-- ============================================
-- 验证查询：检查ancestors是否正确填充
-- ============================================

UPDATE learn_knowledge_tree set LEVEL=1 where pid is null;


SELECT ct1.* from learn_knowledge_tree ct1
join learn_knowledge_tree pt2 on ct1.pid=pt2.id
 where ct1.pid is not null and pt2.pid is null
 
 
UPDATE learn_knowledge_tree ct1
join learn_knowledge_tree pt2 on ct1.pid=pt2.id
 set ct1.LEVEL=2 where ct1.pid is not null and pt2.pid is null
 
 
 

-- 查看所有节点的ancestors
SELECT 
    id,
    name,
    pid,
    ancestors,
    level,
    (SELECT COUNT(*) FROM learn_knowledge_tree WHERE FIND_IN_SET(t.id, ancestors) > 0) AS children_count
FROM learn_knowledge_tree t
ORDER BY level, id
LIMIT 100;

-- 查找ancestors为空的非根节点（应该没有）
SELECT id, name, pid, ancestors, level
FROM learn_knowledge_tree
WHERE (ancestors IS NULL OR ancestors = '')
  AND (pid IS NOT NULL AND pid != 0);

-- ============================================
-- 注意事项：
-- 1. 如果数据量很大，建议在低峰期执行
-- 2. 执行前建议备份数据
-- 3. 如果树结构有循环引用，可能导致无限递归，需要先检查数据
-- 4. 方法1（递归CTE）需要MySQL 8.0+
-- 5. 方法2（存储过程）兼容MySQL 5.7及以下版本
-- 6. 方法3（循环更新）适合小数据量，需要手动多次执行
-- ============================================