MySQL 版本 5.5.37 : Dropping tables older than 30 days using CURSOR

Question

我一直在研究试图找到一种方法来从查询结果中获取所有行并单独处理它们。我写了一个脚本，我认为它可以工作，但显然不行。

脚本:

DECLARE @name char(20);

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT table_name
FROM information_schema.tables WHERE table_schema = 'puslogger' AND UPDATE_TIME < (now() - interval 30 day)

OPEN c1; 

FETCH NEXT FROM c1
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN

PREPARE stmt FROM "concat('DROP TABLE IF EXISTS `', @table_name,'`;')"
EXECUTE stmt
DEALLOCTATE stmt

FETCH NEXT FROM c1
INTO @table_name

END

CLOSE c1
DEALLOCATE c1

该脚本旨在删除所有超过 30 天的表。虽然它似乎不适用于 MySQL 版本 5.5.37。

我是 MySQL 的新手，我正在运行一个带有 MySQL for Windows (XP) 的服务器。也许 CURSORS 的这种语法对于相应的服务器版本不正确？我不确定，但如果有人能帮助我，我会很高兴。

编辑:

这是当我尝试从 SQL 命令行执行脚本时返回的错误消息:

ERROR 1064 (42000): You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @name char(20)' at line 1

ERROR 1064 (42000): You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE c1 CURSOR READ_ONLY FOR SELECT table_name FROM information_schema.tables' at line 1

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FETCH NEXT FROM c1 INTO @table_name

WHILE @@FETCH_STATUS = 0 BEGIN

PREPARE st' at line 1

更新:

我也试过这个脚本(注意:我打算创建一个每天执行的事件，以删除超过 30 天的表。):

delimiter |

CREATE EVENT clean_logger
    ON SCHEDULE EVERY 1 DAY
    DO
    BEGIN
        DECLARE @name char(20);
    DECLARE c1 CURSOR READ_ONLY
    FOR
    SELECT table_name
    FROM information_schema.tables WHERE table_schema = 'puslogger' AND UPDATE_TIME < (now() - interval 30 day);

    OPEN c1; 

    FETCH NEXT FROM c1
    INTO @table_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    PREPARE stmt FROM "concat('DROP TABLE IF EXISTS `', @table_name,'`;')";
    EXECUTE stmt;
    DEALLOCTATE stmt;

    FETCH NEXT FROM c1
    INTO @table_name;

    END;

CLOSE c1;
DEALLOCATE c1;
END |

delimiter ;

在 SQL 命令行中运行此脚本返回:

ERROR 1064 (42000): You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the right syntax to use near '@name char(20); DECLARE c1 CURSOR READ_ONLY FOR SELECT table_name FROM infor' at line 5

Answer 1

尝试以下程序:

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `sp_drop_table`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_drop_table`()
BEGIN
        DECLARE done INT(1) DEFAULT 0;
        DECLARE _tblname VARCHAR(20) DEFAULT '';

        DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'puslogger' AND UPDATE_TIME < (NOW() - INTERVAL 30 DAY);
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- open cursor1
OPEN cur1;
BEGIN

REPEAT
FETCH cur1 INTO _tblname;
IF _tblname = '' THEN
    SET done = 1;
END IF; 

            IF (done<>1) THEN
    SET @str1=CONCAT("DROP TABLE IF EXISTS ",_tblname);
                 PREPARE stmt1 FROM @str1;
                 EXECUTE stmt1;
                 DEALLOCATE PREPARE stmt1;

            END IF;

UNTIL done
END REPEAT;
END;
     CLOSE cur1;
-- close cursor1     

     SELECT 'done';

END$$

DELIMITER ;