更新上下级关系
CREATE TEMPORARY TABLE hierarchy AS
WITH RECURSIVE hierarchy_cte AS (
-- 基础层：从顶层节点开始
SELECT
id,
parentId,
CAST(CONCAT('/', id) AS CHAR(255)) AS path
FROM
dep
WHERE
parentId IS NULL
UNION ALL
-- 递归层：将子节点附加到路径中
SELECT
d.id,
d.parentId,
CONCAT(h.path, '/', d.id) AS path
FROM
dep d
INNER JOIN hierarchy_cte h ON d.parentId = h.id
)
SELECT * FROM hierarchy_cte;
UPDATE dep d
JOIN hierarchy h ON d.id = h.id
SET d.idpath = h.path;
DROP TEMPORARY TABLE IF EXISTS hierarchy;
结果：
id name parentId idpath
1 Root NULL /1
2 Child 1 1 /1/2
3 Child 2 2 /1/2/3
4 Child 3 3 /1/2/3/4
