MySQL версии 5.5.37: Удаление таблиц старше 30 дней с помощью CURSOR

Я исследовал, пытаясь найти способ получить все строки из результата запроса и обработать их по отдельности. Я написал сценарий, который, как я думал, будет работать, но, по-видимому, нет.

Сценарий:

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 для Windows (XP). Возможно, этот синтаксис для CURSORS не подходит для соответствующей версии сервера? Я не уверен, но я был бы очень рад, если бы кто-нибудь мог мне помочь.

ИЗМЕНИТЬ:

Это сообщение об ошибке возвращается, когда я пытаюсь выполнить скрипт из командной строки SQL:

ОШИБКА 1064 (42000): ошибка в синтаксисе SQL;

проверьте руководство, соответствующее версии вашего сервера MySQL, для правильного синтаксиса для использования рядом с «DECLARE @name char (20)» в строке 1.

ОШИБКА 1064 (42000): ошибка в синтаксисе SQL;

проверьте руководство, соответствующее версии вашего сервера MySQL, для правильного синтаксиса для использования рядом с «DECLARE c1 CURSOR READ_ONLY FOR SELECT table_name FROM information_schema.tables» в строке 1

ОШИБКА 1064 (42000): ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, для правильного синтаксиса для использования рядом с «FETCH NEXT FROM c1 INTO @table_name».

ПОКА @@FETCH_STATUS = 0 НАЧАТЬ

ПОДГОТОВЬТЕ ст' в строке 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 возвращает:

ОШИБКА 1064 (42000): ошибка в синтаксисе SQL;

проверьте руководство, соответствующее вашей версии сервера MySQL, чтобы узнать, какой правильный синтаксис использовать рядом с '@name char(20); DECLARE c1 CURSOR READ_ONLY FOR SELECT table_name FROM infor' в строке 5


mysql sql cursor drop-table mysql-5.5
person Linus    schedule 11.04.2014    source источник
comment
Разделители операторов (;) отсутствуют в вашем коде, вы удалили их перед публикацией или они действительно отсутствуют в исходном коде? Кроме того, DECLARE работает только внутри хранимой процедуры/функции. Вы также удалили заголовки функций/процедур для краткости?   -  person RandomSeed    schedule 11.04.2014
comment
В дополнение к точкам @RandomSeed вам нужно определить, что означает, что не работает. Какую ошибку или поведение вы получаете?   -  person    schedule 11.04.2014
comment
Нет, я выложил весь сценарий. Я сохраняю его как файл sql и запускаю из командной строки SQL. Так что да, разделители операторов отсутствуют. Я не уверен, как правильно воплотить эту идею в рабочий сценарий.   -  person Linus    schedule 11.04.2014
comment
@dan1111 dan1111 Я добавил сообщение об ошибке, возвращаемое при выполнении скрипта. (См. редактирование)   -  person Linus    schedule 11.04.2014

Ответы (1)


arrow_upward
2
arrow_downward

Попробуйте следующую процедуру:

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 ;
person Zafar Malik    schedule 11.04.2014
comment
Большое спасибо, я обязательно попробую это, и если это сработает, я приму это как лучший ответ :) - person Linus; 11.04.2014
comment
Круто, сработало, мне просто нужно было изменить SET @str1=CONCAT("DROP TABLE IF EXISTS ",_tblname); на SET @str1=CONCAT("DROP TABLE IF EXISTS `",_tblname,"`"); Еще раз спасибо. - person Linus; 11.04.2014