使用存储过程删除临时表

Question

我一直在尝试创建一个自动删除临时表的存储过程。

CREATE PROCEDURE DeleteTemp()
BEGIN
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;


DECLARE tmptablename VARCHAR(100);
DECLARE tmpTables CURSOR FOR
    SELECT TABLE_NAME
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA='myDB' AND TABLE_NAME LIKE 'tmp%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
OPEN tmpTables;
SELECT FOUND_ROWS() into num_rows;
the_loop: LOOP
    FETCH tmpTables INTO tmptablename;
    DROP TABLE tmptablename;
    IF no_more_rows THEN
        CLOSE tmpTables;
        LEAVE the_loop;
    END IF;
END LOOP the_loop;
END

然而，我得到的只是：

查询:调用DeleteTemp错误代码: 1051未知表“tmptablename”

如何将变量tmptablename正确地传递到"DROP TABLE“命令中？

修正了使用准备好的语句。

CREATE
    PROCEDURE DeleteTemp()
    BEGIN
    DECLARE no_more_rows BOOLEAN;
    DECLARE loop_cntr INT DEFAULT 0;
    DECLARE num_rows INT DEFAULT 0;


    DECLARE tmptablename VARCHAR(100);
    DECLARE tmpTables CURSOR FOR
        SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA='MY_SCHEMA'
            AND TABLE_NAME LIKE 'tmp%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
    OPEN tmpTables;
    SELECT FOUND_ROWS() INTO num_rows;

    the_loop: LOOP
        FETCH tmpTables INTO tmptablename;
        IF no_more_rows THEN
            CLOSE tmpTables;
            LEAVE the_loop;
        ELSE
            SET @a:=CONCAT('DROP TABLE ',tmptablename);
            PREPARE stmt1 FROM @a;
            EXECUTE stmt1;
            DEALLOCATE PREPARE stmt1;
        END IF;
    END LOOP the_loop;
    END

Answer 1

您看过准备和执行命令了吗？这就像执行动态SQL一样。

准备语句的SQL语法

以下SQL语句可用于准备好的语句: ALTER、CALL、COMMIT、CREATE、DELETE、DO、DROP、DROP、、INSERT、RENAME TABLE、RE替换、SELECT、SET、UPDATE和大多数显示语句。分析表、优化表和修复表也在MySQL 5.0.23中得到了支持。

Answer 2

这可能不是您想要的结果，但是您是否知道您的临时表只对创建它们的连接可见，并且在连接关闭后将自动删除？