MySQL存储过程改造需求：按参数返回不同查询结果

没问题，用临时表来实现这个需求是个很靠谱的思路，既能保证逻辑清晰，也能避免重复计算。我帮你写好了完整的存储过程实现，还加了详细的注释和注意事项：

完整存储过程代码 #

DELIMITER //

CREATE PROCEDURE GetProductOrderStats(
    IN start_date DATETIME,
    IN end_date DATETIME,
    IN full BOOLEAN
)
BEGIN
    -- 1. 创建临时表，用来缓存原查询的订单统计结果
    CREATE TEMPORARY TABLE IF NOT EXISTS order_stats (
        id INT,
        variant VARCHAR(255),
        product_id INT,
        cost DECIMAL(9,2),
        price DECIMAL(9,2),
        quantity INT,
        sku VARCHAR(255),
        avg_price DECIMAL(9,2),
        qty_at_hand INT,
        fulfillment_channel VARCHAR(255),
        returns INT DEFAULT 0
    );

    -- 2. 清空临时表，避免重复执行存储过程时残留旧数据
    TRUNCATE TABLE order_stats;

    -- 3. 把原查询的统计结果插入临时表
    INSERT INTO order_stats
    SELECT 
        `order`.`id` AS `id`, 
        IFNULL(product.main_name, product.name) AS `variant`, 
        `product`.`id` AS `product_id`, 
        `product`.`cost` AS `cost`, 
        ROUND(SUM(order.price), 2) AS `price`, 
        SUM(order.quantity) AS `quantity`, 
        `product`.`sku` AS `sku`, 
        ROUND(order.price/order.quantity, 2) AS `avg_price`, 
        `product`.`quantity` AS `qty_at_hand`, 
        `order`.`fulfillment_channel` AS `fulfillment_channel`, 
        0 as `returns` 
    FROM `order` 
    LEFT JOIN `product` ON product.sku = order.sku 
    WHERE (`order`.`account_id`=1) 
      AND (`order`.`item_status`<>'Cancelled') 
      AND (`order`.`purchase_date` >= start_date) 
      AND (`order`.`purchase_date` <= end_date) 
    GROUP BY `order`.`sku`;

    -- 4. 根据full参数返回对应结果
    IF full THEN
        -- 当full为true时，返回所有产品：关联临时表，无订单的字段设为0/空值
        SELECT 
            COALESCE(os.id, p.id) AS id,
            IFNULL(p.main_name, p.name) AS variant,
            p.id AS product_id,
            p.cost AS cost,
            COALESCE(os.price, 0.00) AS price,
            COALESCE(os.quantity, 0) AS quantity,
            p.sku AS sku,
            COALESCE(os.avg_price, 0.00) AS avg_price,
            p.quantity AS qty_at_hand,
            COALESCE(os.fulfillment_channel, '') AS fulfillment_channel,
            COALESCE(os.returns, 0) AS returns
        FROM product p
        LEFT JOIN order_stats os ON p.sku = os.sku
        WHERE p.account_id = 1; -- 确保只返回当前账户的产品，和原查询范围一致
    ELSE
        -- 当full为false时，直接返回临时表的统计结果
        SELECT * FROM order_stats;
    END IF;

    -- 5. 手动销毁临时表（可选，临时表在会话结束后会自动删除，这里清理更严谨）
    DROP TEMPORARY TABLE IF EXISTS order_stats;
END //

DELIMITER ;

关键逻辑说明 #

• 临时表的作用：先把原查询的统计结果缓存到临时表，避免在full模式下重复执行复杂的聚合查询，提升性能；
• full模式处理：用LEFT JOIN关联产品表和临时表，使用COALESCE函数把没有对应订单的字段值替换为0或空字符串，保证所有产品都能被返回；
• 数据一致性：在full模式下加上p.account_id=1的过滤，确保返回的产品和原查询的订单属于同一个账户，避免数据范围混乱；
• 临时表清理：虽然临时表会在会话结束后自动销毁，但手动调用DROP可以避免后续执行存储过程时出现表已存在的错误。

使用示例 #

• 仅返回有订单的产品统计：

CALL GetProductOrderStats('2024-01-01 00:00:00', '2024-06-30 23:59:59', false);

• 返回所有产品（包含无订单的产品，对应统计字段设为0）：

CALL GetProductOrderStats('2024-01-01 00:00:00', '2024-06-30 23:59:59', true);

优化建议 #

如果order表数据量较大，原查询的聚合操作可能会变慢，可以考虑给order表创建联合索引：

CREATE INDEX idx_order_account_sku_date_status ON `order`(account_id, sku, purchase_date, item_status);

这个索引可以大幅提升WHERE条件过滤和GROUP BY的性能。

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