批量删除超过 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 服务器版本>对应的手册，了解在第 1 行"NULL"附近使用的正确语法

是否可以批量删除超过 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;

结束