嵌套调用存储过程遇#1137临时表重开错误，如何解决？

问题背景 #

你尝试封装包含嵌套存储过程调用的代码时，触发错误：

#1137 - Can't reopen table: 'wm_ewallet_user_balance'

原因在于：调用wm_ewallet_transactions_balance生成临时表wm_ewallet_user_balance后，你在递归CTE中三次引用了该表，但MySQL临时表不允许在同一个查询语句中被多次引用——递归CTE的UNION ALL段和子查询都属于同一查询的一部分，因此触发了这个限制。

解决方案 #

方案1：复制临时表到新临时表 #

调用原存储过程后，将临时表数据复制到第二个临时表，后续查询全部使用新表：

SET @p0='87';
CALL `wm_ewallet_transactions_balance`(@p0);

-- 复制临时表结构与数据到新表
CREATE TEMPORARY TABLE wm_ewallet_user_balance_copy LIKE wm_ewallet_user_balance;
INSERT INTO wm_ewallet_user_balance_copy SELECT * FROM wm_ewallet_user_balance;

WITH RECURSIVE rcte(user_id, balance, date) AS (
  (
    SELECT user_id, balance, date
    FROM  wm_ewallet_user_balance_copy
    ORDER BY date
    LIMIT 1
  )
UNION ALL
SELECT COALESCE(t.user_id, r.user_id),
    COALESCE(t.balance, r.balance),
    r.date + INTERVAL 1 DAY
FROM rcte r
LEFT JOIN wm_ewallet_user_balance_copy t ON t.date = r.date + INTERVAL 1 DAY
WHERE r.date < (SELECT MAX(date) FROM wm_ewallet_user_balance_copy)
)
SELECT r.user_id, MIN(r.balance) AS balance, r.date
FROM rcte r
GROUP BY r.user_id, r.date
ORDER BY r.date;

方案2：修改原存储过程返回结果集 #

将wm_ewallet_transactions_balance改为直接返回查询结果，而非创建临时表，再将结果存入临时表使用：
修改后的存储过程：

DELIMITER //
CREATE PROCEDURE `wm_ewallet_transactions_balance`(IN user_id_param INT)
BEGIN
    SELECT
        id,
        user_id,
        type,
        amount,
        SUM(amount) OVER(PARTITION BY user_id ORDER BY id) AS `balance`,
        created_at,
        DATE(created_at) AS date
    FROM
        wm_ewallet_transactions
    WHERE user_id = user_id_param;
END //
DELIMITER ;

主查询中存入临时表并使用：

SET @p0='87';
-- 将存储过程结果存入临时表
CREATE TEMPORARY TABLE wm_ewallet_user_balance
(
    INDEX user_id_index (user_id),
    INDEX created_at_index (date)
)
AS CALL `wm_ewallet_transactions_balance`(@p0);

-- 后续递归查询同原逻辑，使用该临时表即可
WITH RECURSIVE rcte(user_id, balance, date) AS (
  (
    SELECT user_id, balance, date
    FROM  wm_ewallet_user_balance
    ORDER BY date
    LIMIT 1
  )
UNION ALL
SELECT COALESCE(t.user_id, r.user_id),
    COALESCE(t.balance, r.balance),
    r.date + INTERVAL 1 DAY
FROM rcte r
LEFT JOIN wm_ewallet_user_balance t ON t.date = r.date + INTERVAL 1 DAY
WHERE r.date < (SELECT MAX(date) FROM wm_ewallet_user_balance)
)
SELECT r.user_id, MIN(r.balance) AS balance, r.date
FROM rcte r
GROUP BY r.user_id, r.date
ORDER BY r.date;

方案3：改用普通表（不推荐，仅临时场景可用） #

若临时表生命周期无需和会话绑定，可创建普通表存储数据，但需注意定期清理避免冗余：

SET @p0='87';
-- 创建普通表（若不存在）
CREATE TABLE IF NOT EXISTS wm_ewallet_user_balance
(
    INDEX user_id_index (user_id),
    INDEX created_at_index (date)
);
-- 清空旧数据
TRUNCATE TABLE wm_ewallet_user_balance;
-- 修改原存储过程，将结果插入普通表而非临时表
CALL `wm_ewallet_transactions_balance`(@p0);

-- 后续递归查询同原逻辑
WITH RECURSIVE rcte(...) AS (...) SELECT ...;

原理说明 #

MySQL临时表的核心限制是：同一条SQL语句中只能引用一次临时表。递归CTE的UNION ALL分支、子查询都属于同一查询的组成部分，多次引用会触发无法重新打开的错误。通过复制临时表或改用结果集转存的方式，可绕过这一限制。

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