直接UPDATE无法安全更新自引用路径字段，因为path依赖父节点的完整祖先链，单次UPDATE修改parent_id后子节点path未同步重算即失效；数据库不自动维护派生路径，且触发器禁止同表递归更新，需用递归CTE+临时表或业务层标记延迟计算等方案保障一致性。
为什么直接 UPDATE 无法安全更新自引用路径字段？
因为 path 字段（如 '/1/5/12/'）依赖父节点的完整祖先链，而自引用表中父子关系可能在单次 UPDATE 中被同时修改。若先改某行的 parent_id，其子节点的 path 若未同步重算，就会立刻失效——数据库不自动维护这类派生路径。
常见错误现象：UPDATE t SET parent_id = 7 WHERE id = 12 后，id=12 的 path 仍是旧值，导致后续按路径查询时漏数据或报错。
- 不能依赖触发器递归更新子树（多数数据库禁止在触发器中修改同表）
- 不能用简单 JOIN 自连接 UPDATE（MySQL 8.0+ 支持，但需确保祖先路径已存在且无环）
- PostgreSQL 的
WITH RECURSIVE可查，但标准 SQL 不允许在 UPDATE 中直接嵌套递归 CTE
用递归 CTE + 临时表重建全量 path（通用可靠方案）
核心思路：不原地 UPDATE，而是用递归查询生成新 path，写入临时表，再原子性替换原表字段。适用于 MySQL 8.0+、PostgreSQL、SQL Server。
以 PostgreSQL 为例（MySQL 语法类似，仅需调整字符串拼接符）：
WITH RECURSIVE tree AS (
SELECT id, parent_id, CAST('/' || id || '/' AS TEXT) AS new_path
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, t.new_path || c.id || '/'
FROM categories c
INNER JOIN tree t ON c.parent_id = t.id
)
UPDATE categories
SET path = t.new_path
FROM tree t
WHERE categories.id = t.id;
-
CAST和||确保字符串类型一致，避免隐式转换失败 - 必须从根节点（
parent_id IS NULL）开始，否则递归无法收敛 - 若表中有环（如
id=5的parent_id=5），需加SELECT ... LIMIT 100或循环检测逻辑，否则查询卡死
MySQL 8.0+ 中用窗口函数避免递归（仅限深度可控场景）
当最大层级 ≤ 5 且结构稳定时，可用多层 LEFT JOIN 拼接路径，比递归 CTE 更快，也绕过 MySQL 对递归深度的默认限制（cte_max_recursion_depth=1000）。
示例：三级以内结构
UPDATE categories c0
JOIN categories c1 ON c0.parent_id = c1.id
JOIN categories c2 ON c1.parent_id = c2.id
SET c0.path = CONCAT('/', c2.id, '/', c1.id, '/', c0.id, '/')
WHERE c2.parent_id IS NULL;
- 每级 JOIN 都要对应一个非空
parent_id条件，否则NULL会污染路径 - 需为不同深度分别写 UPDATE（如单独处理二级节点、一级节点），无法一揽子覆盖
- JOIN 层数增加后执行计划易退化，建议加复合索引：
(parent_id, id)
业务层批量更新时如何避免路径不一致？
如果应用频繁移动节点（如拖拽排序），不要每次移动都重刷整棵树。改为标记 + 延迟计算：
- 新增布尔字段
path_dirty BOOLEAN DEFAULT TRUE，移动parent_id时只设为TRUE - 读取时若
path_dirty = TRUE，走一次轻量 CTE 查当前路径（不 UPDATE），缓存结果 - 后台任务定时扫描
path_dirty = TRUE的节点，批量重建path并置FALSE - 关键点：所有写操作必须用事务包裹，先改
parent_id，再设path_dirty = TRUE，防止中间态暴露
路径字段本质是冗余缓存，不是真相源。真正可靠的永远是 parent_id 链，path 只是加速查询的副产品——这点容易被忽略，直到某次手动 UPDATE 后发现搜索结果对不上。
