mySQL批量刪庫跑路存儲過程

模糊匹配存儲過程如下

DROP PROCEDURE IF EXISTS drop_databases;
CREATE PROCEDURE drop_databases(
    IN paramDBNamePrefix VARCHAR(32),
    IN paramDBNameSuffix VARCHAR(32)
)
BEGIN
    DECLARE _done INT DEFAULT 0;
    DECLARE _cursorValue VARCHAR(255);

    DECLARE _cursor CURSOR FOR SELECT SCHEMA_NAME FROM information_schema.SCHEMATA 
        WHERE SCHEMA_NAME LIKE @_paramDBName;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1;

    SET @_paramDBName = CONCAT("%", paramDBNamePrefix, "%", paramDBNameSuffix, "%");
    

    OPEN _cursor;
    REPEAT
        FETCH _cursor INTO _cursorValue;
        IF (_done <> 1) THEN
            SET @prepareStatement = CONCAT("DROP DATABASE ", _cursorValue);
            SELECT @prepareStatement;
            PREPARE stmt FROM @prepareStatement;
            EXECUTE stmt ;
            DEALLOCATE PREPARE stmt;
        END IF;
    UNTIL _done = 1 end REPEAT;
    CLOSE _cursor;
 END

参数有两个，分别是paramDBNamePrefix和paramDBNameSuffix表示待删除数据库的前缀和后缀，这两个参数会组成一个字符串’%paramDBNamePrefix%paramDBNameSuffix%’用作模糊查询的条件。

測試

call drop_databases(‘前綴’, ‘後綴’);