mysql - 在树中查找节点级别

Question

我有一棵树(嵌套类别)存储如下:

CREATE TABLE `category` (
  `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `category_name_UNIQUE` (`category_name`,`parent_id`),
  KEY `fk_category_category1` (`parent_id`,`category_id`),
  CONSTRAINT `fk_category_category1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`category_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

我需要为我的客户端语言 (PHP) 提供节点信息(子+父)，以便它可以在内存中构建树。我可以调整我的 PHP 代码，但我认为如果我可以按照所有 parent 在他们的 child 之前出现的顺序检索行，操作会更简单。如果我知道每个节点的级别，我可以这样做:

SELECT category_id, category_name, parent_id
FROM category
ORDER BY level -- No `level` column so far :(

你能想出一种方法( View 、存储例程或其他...)来计算节点级别吗？我想如果它不是实时的也没关系，我需要在节点修改时重新计算它。

第一次更新:到目前为止的进展

我根据 Amarghosh 的反馈编写了这些触发器:

DROP TRIGGER IF EXISTS `category_before_insert`;

DELIMITER //

CREATE TRIGGER `category_before_insert` BEFORE INSERT ON `category` FOR EACH ROW BEGIN
    IF NEW.parent_id IS NULL THEN
        SET @parent_level = 0;
    ELSE
        SELECT level INTO @parent_level
        FROM category
        WHERE category_id = NEW.parent_id;
    END IF;

    SET NEW.level = @parent_level+1;
END//

DELIMITER ;


DROP TRIGGER IF EXISTS `category_before_update`;

DELIMITER //

CREATE TRIGGER `category_before_update` BEFORE UPDATE ON `category` FOR EACH ROW BEGIN
    IF NEW.parent_id IS NULL THEN
        SET @parent_level = 0;
    ELSE
        SELECT level INTO @parent_level
        FROM category
        WHERE category_id = NEW.parent_id;
    END IF;

    SET NEW.level = @parent_level+1;
END//

DELIMITER ;

它似乎可以很好地用于插入和修改。但它不适用于删除:当行从 ON UPDATE CASCADE 外键更新时，MySQL 服务器不会启动触发器。

第一个显而易见的想法是编写一个新的删除触发器；然而，表 categories 上的触发器不允许修改同一表上的其他行:

DROP TRIGGER IF EXISTS `category_after_delete`;

DELIMITER //

CREATE TRIGGER `category_after_delete` AFTER DELETE ON `category` FOR EACH ROW BEGIN
    /*
     * Raises an error, see below
     */
    UPDATE category SET parent_id=NULL
    WHERE parent_id = OLD.category_id;
END//

DELIMITER ;

错误:

Grid editing error: SQL Error (1442): Can't update table 'category' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

第二次更新:可行的解决方案(除非被证明是错误的)

我的第一次尝试非常明智，但我发现了一个我无法解决的问题:当您从触发器启动一系列操作时，MySQL 将不允许更改同一表中的其他行。由于节点删除需要调整所有后代的级别，我碰壁了。

最后，我使用 here 中的代码更改了方法: 而不是在节点更改时更正各个级别，我有代码来计算所有级别并在每次编辑时触发它。由于它的计算速度很慢并且获取数据需要非常复杂的查询，因此我将其缓存到一个表中。就我而言，这是一个可以接受的解决方案，因为版本应该很少见。

<强>1。缓存级别的新表:

CREATE TABLE `category_level` (
  `category_id` int(10) NOT NULL,
  `parent_id` int(10) DEFAULT NULL, -- Not really necesary
  `level` int(10) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

<强>2。计算级别的辅助函数

如果我真的掌握了它的工作原理，它本身并不会真正返回任何有用的东西。相反，它将内容存储在 session 变量中。

CREATE FUNCTION `category_connect_by_parent_eq_prior_id`(`value` INT) RETURNS int(10)
    READS SQL DATA
BEGIN
    DECLARE _id INT;
    DECLARE _parent INT;
    DECLARE _next INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @category_id = NULL;

    SET _parent = @category_id;
    SET _id = -1;

    IF @category_id IS NULL THEN
        RETURN NULL;
    END IF;

    LOOP
        SELECT  MIN(category_id)
        INTO    @category_id
        FROM    category
        WHERE   COALESCE(parent_id, 0) = _parent
            AND category_id > _id;
        IF @category_id IS NOT NULL OR _parent = @start_with THEN
            SET @level = @level + 1;
            RETURN @category_id;
        END IF;
        SET @level := @level - 1;
        SELECT  category_id, COALESCE(parent_id, 0)
        INTO    _id, _parent
        FROM    category
        WHERE   category_id = _parent;
    END LOOP;
END

<强>3。启动重新计算过程的程序

它基本上封装了检索由辅助函数辅助的级别的复杂查询。

CREATE PROCEDURE `update_category_level`()
    SQL SECURITY INVOKER
BEGIN
    DELETE FROM category_level;

    INSERT INTO category_level (category_id, parent_id, level)
    SELECT hi.category_id, parent_id, level
    FROM (
        SELECT category_connect_by_parent_eq_prior_id(category_id) AS category_id, @level AS level
        FROM (
            SELECT  @start_with := 0,
                @category_id := @start_with,
                @level := 0
            ) vars, category
        WHERE @category_id IS NOT NULL
        ) ho
    JOIN category hi ON hi.category_id = ho.category_id;
END

<强>4。保持缓存表最新的触发器

CREATE TRIGGER `category_after_insert` AFTER INSERT ON `category` FOR EACH ROW BEGIN
    call update_category_level();
END

CREATE TRIGGER `category_after_update` AFTER UPDATE ON `category` FOR EACH ROW BEGIN
    call update_category_level();
END

CREATE TRIGGER `category_after_delete` AFTER DELETE ON `category` FOR EACH ROW BEGIN
    call update_category_level();
END

<强>5。已知问题

• 如果节点频繁更改，则不是最佳选择。
• MySQL 不允许触发器和过程中的事务或表锁定。您必须在编辑节点时注意这些细节。

Answer 1

Hierarchical Queries in MySQL 上有一系列优秀的文章其中包括如何识别层级、叶节点、层次结构中的循环等。