MySQL 5.7.12中如何将永久表转临时表？解决重开报错问题

将永久表FINAL_RESULTS改为临时表时，因MySQL临时表在单查询中仅允许被引用一次，原UPDATE语句的子查询再次引用该临时表会触发Can't reopen table 'FINAL_RESULTS'错误。由于MySQL 5.7.12不支持CTE，可通过创建临时表副本的方式绕过限制，具体实现如下：

修改后的完整SQL代码 #

-- 创建第一个临时表，存储初始查询结果
CREATE TEMPORARY TABLE FINAL_RESULTS(
    ID VARCHAR(38),
    DATE1 DATETIME,
    DATE2 DATETIME,
    PRIMARY KEY(ID,DATE1)
);

-- 插入初始数据（修正原语句字段顺序不匹配问题）
INSERT INTO FINAL_RESULTS(ID, DATE1)
SELECT DISTINCT t2.ID, DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))
FROM SOURCE1 AS t1
INNER JOIN SOURCE2 AS t2 ON t1.ID = t2.ID
WHERE t1.code IN ("a", "b")
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= t2.DATE3
AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= t2.DATE4;

-- 创建临时表副本，用于后续子查询关联
CREATE TEMPORARY TABLE FINAL_RESULTS_COPY LIKE FINAL_RESULTS;
INSERT INTO FINAL_RESULTS_COPY SELECT * FROM FINAL_RESULTS;

-- 执行更新操作，子查询引用副本表而非原临时表
UPDATE FINAL_RESULTS
INNER JOIN (
    SELECT t2.ID, MIN(DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S"))) AS mindate
    FROM SOURCE1 AS t1
    INNER JOIN FINAL_RESULTS_COPY AS t2 ON t1.ID = t2.ID
    WHERE t1.CODE IN ("c", "d")
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) >= DATE_SUB(t2.DATE1, INTERVAL 21 DAY)
    AND DATE(STR_TO_DATE(t1.dateevent,"%d %b %Y %H:%i:%S")) <= DATE_ADD(t2.DATE1, INTERVAL 14 DAY)
    GROUP BY t1.ID
) AS x ON FINAL_RESULTS.ID = x.ID
SET FINAL_RESULTS.DATE2 = x.mindate;

方案说明 #

• 核心逻辑：通过复制临时表生成独立副本，让UPDATE子查询引用副本表，避免同一条查询中重复引用同一个临时表，绕过MySQL的限制。
• 临时表特性：临时表仅在当前数据库会话中存在，会话结束后自动销毁，完全满足"仅用于查询过程、无需持久化"的需求，不会污染数据库。
• 细节修正：原INSERT语句存在字段顺序不匹配问题（原语句指定插入DATE1,DATE2，但SELECT返回的是ID和日期值），已修正为ID, DATE1确保数据插入正确。

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