如何用sql删除所有非系统的数据库

Question

如何用sql批量删除数据库(比如数据库名以java开头)，导师要求要用sql实现，不是表！是数据库！

Answer 1

DELIMITER //

CREATE PROCEDURE DropDatabasesWithPrefix(prefix VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE dbName VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT SCHEMA_NAME 
        FROM INFORMATION_SCHEMA.SCHEMATA
        WHERE SCHEMA_NAME LIKE CONCAT(prefix, '%');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO dbName;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @dropStmt = CONCAT('DROP DATABASE `', dbName, '`');
        PREPARE stmt FROM @dropStmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

-- 调用存储过程
CALL DropDatabasesWithPrefix('java');