MySQL存储过程含WITH子句时创建临时表报错求助

我编写了一个MySQL存储过程，当前可正常执行查询逻辑，但取消注释CREATE TEMPORARY TABLE required_items AS部分、尝试将查询结果存入临时表时出现报错，推测问题与WITH递归子句的使用有关。

原存储过程代码 #

DELIMITER $$
CREATE PROCEDURE GetRequiredItemsCount(
    IN item VARCHAR(16),
    IN parent_item_count INT)

    BEGIN
/*      DROP TABLE IF EXISTS required_items;
        CREATE TEMPORARY TABLE required_items AS */
        
            WITH RECURSIVE bom_temp AS (

                SELECT bom.item_id, bom.component_id, bom.bom_multiplier
                FROM bom
                WHERE bom.item_id = item
                
                UNION ALL

                SELECT child.item_id, child.component_id, child.bom_multiplier
                FROM bom_temp parent
                    JOIN bom child ON parent.component_id = child.item_id
                )

            SELECT DISTINCT component_id, SUM(bom_multiplier)*parent_item_count
            FROM bom_temp
            GROUP BY component_id;
    END$$
DELIMITER;

BOM表结构及测试数据 #

CREATE TABLE bom (  
    item_id VARCHAR(16),
    component_id VARCHAR(16),
    bom_multiplier INT NOT NULL,
    PRIMARY KEY (item_id, component_id)
);

INSERT INTO bom (item_id, component_id, bom_multiplier)
VALUES  
    ("001", "002", 1),
    ("001", "003", 1),
    ("002", "004", 3),
    ("002", "005", 3); 

解决办法 #

报错核心原因是查询结果的计算字段未指定合法别名，同时需确保MySQL版本兼容递归CTE：

• 给计算字段添加别名：临时表需要明确的字段名，不能直接用表达式作为字段名，给SUM(bom_multiplier)*parent_item_count指定别名（比如required_quantity）。
• 确认MySQL版本：递归CTE（WITH RECURSIVE）仅在MySQL 8.0及以上版本支持，低版本需升级或改用其他递归实现方式。

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

DELIMITER $$
CREATE PROCEDURE GetRequiredItemsCount(
    IN item VARCHAR(16),
    IN parent_item_count INT)

    BEGIN
        DROP TABLE IF EXISTS required_items;
        CREATE TEMPORARY TABLE required_items AS
        WITH RECURSIVE bom_temp AS (
            SELECT bom.item_id, bom.component_id, bom.bom_multiplier
            FROM bom
            WHERE bom.item_id = item
            
            UNION ALL

            SELECT child.item_id, child.component_id, child.bom_multiplier
            FROM bom_temp parent
                JOIN bom child ON parent.component_id = child.item_id
        )
        SELECT 
            DISTINCT component_id, 
            SUM(bom_multiplier)*parent_item_count AS required_quantity
        FROM bom_temp
        GROUP BY component_id;
    END$$
DELIMITER;

测试验证 #

调用存储过程并查询临时表：

CALL GetRequiredItemsCount('001', 2);
SELECT * FROM required_items;

预期结果：

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