mysql如何过滤空数据库

SHOW DATABASES;

SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA HAVING COUNT(*) = 0;

DELIMITER //

CREATE PROCEDURE filter_empty_databases()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE schema_name VARCHAR(255);

    -- 游标用于遍历数据库
    DECLARE cur CURSOR FOR SELECT schema_name FROM information_schema.schemata;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建临时表用于存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS empty_databases (database_name VARCHAR(255));

    -- 打开游标
    OPEN cur;

    -- 遍历数据库并判断是否为空
    read_loop: LOOP
        FETCH cur INTO schema_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @sql = CONCAT('SELECT COUNT(*) INTO @table_count FROM `', schema_name, '`.`', schema_name, '`');

        -- 执行动态SQL语句
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 如果表数量为0，则将数据库名称插入临时表
        IF @table_count = 0 THEN
            INSERT INTO empty_databases VALUES (schema_name);
        END IF;
    END LOOP;

    -- 关闭游标
    CLOSE cur;

    -- 返回结果
    SELECT * FROM empty_databases;

    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS empty_databases;
END //

DELIMITER ;

CALL filter_empty_databases();