MySQL 8重建嵌套集_lft/_rgt值时遇'tmp_tree无法重开'错误求助

问题场景 #

尝试通过存储过程重建directories表的嵌套集_lft和_rgt值时，触发错误：

Query 3 ERROR at Line 110: : Can't reopen table: 'tmp_tree'

表结构：

directories (id CHAR(36), name VARCHAR(...), path VARCHAR(...), parent_id CHAR(36), _lft INT UNSIGNED, _rgt INT UNSIGNED)

存储过程核心逻辑：

• 删除旧存储过程并修改语句分隔符
• 创建带异常回滚的tree_recover存储过程
• 配置内存表大小并开启事务
• 创建临时内存表tmp_tree导入原表数据，重置_lft/_rgt为NULL
• 用tmp_unprocessed表跟踪未处理节点
• 初始化根节点的_lft/_rgt值
• 循环处理有已处理父节点的未处理节点，调整节点的_lft/_rgt值
• 将计算结果同步回原表，提交事务并清理临时表

完整修复后的存储过程代码：

-- Drop the existing procedure if it exists to avoid conflicts
DROP PROCEDURE IF EXISTS tree_recover;

-- Change the delimiter to '//' to allow the use of semicolons within the procedure
DELIMITER //

-- Create the stored procedure tree_recover
CREATE PROCEDURE tree_recover ()
MODIFIES SQL DATA
BEGIN
    -- Declare variables to store the current node's ID, parent ID, left value, and starting ID
    DECLARE currentId CHAR(36);
    DECLARE currentParentId CHAR(36);
    DECLARE currentLeft INT;
    DECLARE startId INT DEFAULT 1;

    -- Declare an exit handler to rollback the transaction in case of an error
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK; -- Rollback the transaction
        RESIGNAL; -- Propagate the exception
    END;

    -- Set the maximum size for MEMORY tables to 512MB to ensure enough space
    SET max_heap_table_size = 1024 * 1024 * 512;

    -- Start a new transaction
    START TRANSACTION;

    -- Drop temporary tables if they exist
    DROP TEMPORARY TABLE IF EXISTS tmp_tree;
    DROP TEMPORARY TABLE IF EXISTS tmp_unprocessed;
    DROP TEMPORARY TABLE IF EXISTS tmp_processed_parents;

    -- Create a temporary table in MEMORY to perform the updates efficiently
    CREATE TEMPORARY TABLE tmp_tree (
        id CHAR(36) NOT NULL, -- Node ID
        parent_id CHAR(36) DEFAULT NULL, -- Parent node ID
        _lft INT UNSIGNED DEFAULT NULL, -- Left value
        _rgt INT UNSIGNED DEFAULT NULL, -- Right value
        PRIMARY KEY (id), -- Primary key on ID
        KEY parent_id_idx (parent_id) -- Index on parent ID for fast lookups
    ) ENGINE = MEMORY;

    -- Insert all nodes from the original directories table into the temporary table
    INSERT INTO tmp_tree (id, parent_id, _lft, _rgt)
    SELECT id, parent_id, _lft, _rgt FROM directories;

    -- Set all left and right values to NULL to prepare for recalculation
    UPDATE tmp_tree SET _lft = NULL, _rgt = NULL;

    -- Create another temporary table to track unprocessed nodes
    CREATE TEMPORARY TABLE tmp_unprocessed (
        id CHAR(36) NOT NULL PRIMARY KEY
    ) ENGINE = MEMORY;

    -- Initialize tmp_unprocessed with all node IDs
    INSERT INTO tmp_unprocessed (id)
    SELECT id FROM tmp_tree;

    -- Initialize left and right values for root nodes (nodes with no parent)
    WHILE EXISTS (SELECT 1 FROM tmp_tree WHERE parent_id IS NULL AND _lft IS NULL AND _rgt IS NULL LIMIT 1) DO
        -- Set the left and right values for the next root node
        UPDATE tmp_tree
        SET _lft = startId, _rgt = startId + 1
        WHERE parent_id IS NULL AND _lft IS NULL AND _rgt IS NULL
        LIMIT 1;
        -- Increment the startId by 2 for the next node
        SET startId = startId + 2;
    END WHILE;

    -- Create temporary table to store processed parent nodes, avoid repeated tmp_tree queries
    CREATE TEMPORARY TABLE tmp_processed_parents (
        id CHAR(36) NOT NULL PRIMARY KEY
    ) ENGINE = MEMORY;
    INSERT INTO tmp_processed_parents (id)
    SELECT id FROM tmp_tree WHERE _lft IS NOT NULL;

    -- Process each node in the temporary table to set the left and right values
    WHILE EXISTS (SELECT 1 FROM tmp_unprocessed LIMIT 1) DO
        -- Use JOIN instead of nested subqueries to avoid multiple tmp_tree references
        SELECT tu.id INTO currentId
        FROM tmp_unprocessed tu
        JOIN tmp_tree tt ON tu.id = tt.id
        JOIN tmp_processed_parents tpp ON tt.parent_id = tpp.id
        WHERE tt._lft IS NULL
        LIMIT 1;

        -- Exit loop if no eligible nodes left, prevent infinite loop
        IF currentId IS NULL THEN
            LEAVE;
        END IF;

        -- Get the parent ID of the current node
        SELECT parent_id INTO currentParentId
        FROM tmp_tree WHERE id = currentId;

        -- Get the left value of the parent node
        SELECT _lft INTO currentLeft
        FROM tmp_tree WHERE id = currentParentId;

        -- Shift the right values of nodes to the right of the current node by 2
        UPDATE tmp_tree SET _rgt = _rgt + 2 WHERE _rgt > currentLeft;

        -- Shift the left values of nodes to the right of the current node by 2
        UPDATE tmp_tree SET _lft = _lft + 2 WHERE _lft > currentLeft;

        -- Set the left and right values for the current node
        UPDATE tmp_tree
        SET _lft = currentLeft + 1, _rgt = currentLeft + 2
        WHERE id = currentId;

        -- Add current node to processed parents table
        INSERT INTO tmp_processed_parents (id) VALUES (currentId);

        -- Mark the current node as processed by removing it from tmp_unprocessed
        DELETE FROM tmp_unprocessed WHERE id = currentId;
    END WHILE;

    -- Update the original directories table with the calculated left and right values
    UPDATE directories
    JOIN tmp_tree ON directories.id = tmp_tree.id
    SET directories._lft = tmp_tree._lft, directories._rgt = tmp_tree._rgt;

    -- Commit the transaction to make the changes permanent
    COMMIT;

    -- Drop the temporary tables as they are no longer needed
    DROP TEMPORARY TABLE tmp_tree;
    DROP TEMPORARY TABLE tmp_unprocessed;
    DROP TEMPORARY TABLE tmp_processed_parents;
END//

-- Restore the delimiter to the default ';'
DELIMITER ;

Call tree_recover();

错误原因 #

MySQL不允许在同一个SELECT查询中多次引用同一个临时表，原代码中SELECT id INTO currentId的嵌套子查询里，两次引用了tmp_tree，触发了"Can't reopen table"限制。

修复方案 #

1. 新增临时表tmp_processed_parents存储已处理的父节点ID，避免重复查询tmp_tree
2. 用JOIN替代嵌套子查询获取待处理节点，彻底规避同一查询多次引用临时表的问题
3. 增加空值判断逻辑，防止无符合条件节点时进入死循环

运行环境：MySQL 8

内容的提问来源于stack exchange，提问作者Ahmed Nagi