MySQL如何给已有的具有层级结构的表增加层级路径字段

在 MySQL 中，给具有层级结构的表新增一个“层级路径字段”（通常叫 level_path、node_path、hierarchy等），用来存储 从根节点到当前节点的层级信息，常见做法如下。

一、使用递归 CTE（MySQL 8.0+，推荐方案）

这是最标准、最清晰、最好维护的方式。

1. 示例表结构

CREATE TABLE category (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50)
);

示例数据：

id | parent_id | name
1  | NULL      | 电子产品
2  | 1         | 手机
3  | 2         | 智能手机
4  | 3         | Android
5  | 1         | 电脑

2. 新增层级字段

ALTER TABLE category ADD COLUMN level_path VARCHAR(1000);

3. 使用递归CTE生成层级路径

WITH RECURSIVE cte AS (
    -- 根节点
    SELECT id, parent_id, name, CAST(id AS CHAR(1000)) AS level_path
    FROM category WHERE parent_id IS NULL
    UNION ALL
    -- 子节点
    SELECT c.id, c.parent_id, c.name, CONCAT(cte.level_path, ',', c.id)
    FROM category c
    JOIN cte ON c.parent_id = cte.id
)
UPDATE category t JOIN cte ON t.id = cte.id SET t.level_path = cte.level_path;

结果示例：

id | level_path
1  | 1
2  | 1,2
3  | 1,2,3
4  | 1,2,3,4
5  | 1,5

4. 同时存储层级深度（可选）

ALTER TABLE category ADD COLUMN level_depth INT;

WITH RECURSIVE cte AS (
    SELECT id, parent_id, 1 AS depth
    FROM category
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, cte.depth + 1
    FROM category c
    JOIN cte ON c.parent_id = cte.id
)
UPDATE category t JOIN cte ON t.id = cte.id
SET t.level_depth = cte.depth;

二、兼容方案（MySQL 5.7 / 不支持递归）

如果 没有 MySQL 8.0，只能用函数 + 循环模拟。

1. 编写函数生成路径

DELIMITER $$

CREATE FUNCTION get_level_path(p_id INT)
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
    DECLARE v_path VARCHAR(1000);
    DECLARE v_parent INT;

    SET v_path = '';
    SET v_parent = p_id;

    WHILE v_parent IS NOT NULL DO
        SET v_path = CONCAT(v_parent, ',', v_path);
        SELECT parent_id INTO v_parent
        FROM category
        WHERE id = v_parent;
    END WHILE;

    RETURN TRIM(BOTH ',' FROM v_path);
END
$$

DELIMITER ;

2. 更新字段

UPDATE category SET level_path = get_level_path(id);

性能较差，不适合超大数据量

三、设计建议（非常重要）

✅ 推荐路径格式

✅ 索引优化（查询子树）

ALTER TABLE category ADD INDEX idx_level_path (level_path);

查询某节点所有子节点：

SELECT * FROM category WHERE level_path LIKE '1,2%';

四、总结

✅ MySQL 8.0+：用递归 CTE 一次性生成并存储从根到当前节点的层级路径字段
❌ 低版本：用函数模拟，但性能和可维护性较差