Хранимая процедура, необходимая для массового удаления таблиц, которые старше 1 часа

Question

Я создал следующую хранимую процедуру MySQL (v5.7.14). Он успешно удаляет все таблицы в моей базе данных с префиксом members_list_. Тем не менее, я хотел бы только удалить таблицы с префиксом 'members_list_' и старше 1 часа.

BEGIN
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables 
FROM information_schema.tables 
WHERE table_schema = 'my_database_name' 
AND table_name LIKE 'members_list_%'; -- AND CREATE_TIME < (NOW() - INTERVAL 1 HOUR);

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END

Вы увидите, что я выдал команду в предложении WHERE. Он использует функцию CREATE_TIME, но когда я "оживляю" этот сегмент кода (удаляя часть "; -"), я получаю следующее сообщение об ошибке:

Ошибка выполнения процедуры 1064 - В вашем синтаксисе SQL есть ошибка; проверьте руководство, которое соответствует> вашей версии сервера MySQL, чтобы найти правильный синтаксис для использования рядом с 'NULL' в строке 1

Возможно ли вообще массовое удаление таблиц с префиксом, которые старше 1 часа?

Answer 1

Чтобы ответить на мой собственный вопрос. Причиной появления сообщения об ошибке "1064" было то, что длина строки GROUP_CONCAT имеет ограничение по умолчанию в 1024 символа. Это означало, что из-за того, что я иногда удалял много таблиц, этот предел 1024 был нарушен.

Чтобы исправить это, мне нужно было установить для команды GROUP_CONCAT намного большее число, таким образом:

SET SESSION group_concat_max_len = 1000000;

Хранимая процедура работала нормально после этого. Вот моя вся пересмотренная хранимая процедура. Он также включает в себя подпрограмму "CASE", которая гарантирует, что она не потерпит неудачу, если выполнено условие NULL.

BEGIN
SET SESSION group_concat_max_len = 1000000;

SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables FROM information_schema.tables 
    WHERE table_schema = 'indexingwebsite2017' AND table_name LIKE 'ma_members_list_%' AND CREATE_TIME < (NOW() - INTERVAL 1 HOUR);

CASE 
    WHEN ISNULL(@tables) THEN 
        SELECT 'NULL VALUE DETECTED' AS Verification;
    ELSE 
        SET @tables = CONCAT('DROP TABLE ', @tables);
        select @tables;
        PREPARE stmt1 FROM @tables;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1; 
END CASE;

КОНЕЦ