MySQL/MariaDB创建含所有表UNION ALL的动态视图存储过程实现

我刚好处理过类似的月度报表场景，给你一个完整的可运行方案，完全贴合你的需求：

核心思路拆解 #

你已经找对了第一步——从information_schema.TABLES里筛选目标表，接下来就是通过游标遍历表名，动态拼接UNION ALL的SQL语句，最后判断视图是否存在并重建它。

完整存储过程代码 #

DELIMITER //

CREATE PROCEDURE RebuildUnionView()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(255);
    DECLARE unionSql TEXT DEFAULT '';
    DECLARE viewName VARCHAR(255) DEFAULT 'vw_all_monthly_reports'; -- 自定义视图名

    -- 声明游标，获取所有table开头的表
    DECLARE tableCursor CURSOR FOR
        SELECT TABLE_NAME 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() -- 限定当前数据库，避免跨库
          AND TABLE_NAME LIKE 'table%';

    -- 游标结束标志
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 初始化：如果视图存在则删除
    SET @dropViewSql = CONCAT('DROP VIEW IF EXISTS ', viewName);
    PREPARE stmt FROM @dropViewSql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 打开游标，遍历表名拼接UNION ALL语句
    OPEN tableCursor;
    read_loop: LOOP
        FETCH tableCursor INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 第一次拼接不需要UNION ALL，后续添加
        IF unionSql = '' THEN
            SET unionSql = CONCAT('SELECT * FROM ', tableName);
        ELSE
            SET unionSql = CONCAT(unionSql, ' UNION ALL SELECT * FROM ', tableName);
        END IF;
    END LOOP;
    CLOSE tableCursor;

    -- 如果有目标表，创建视图；否则提示无表
    IF unionSql != '' THEN
        SET @createViewSql = CONCAT('CREATE VIEW ', viewName, ' AS ', unionSql);
        PREPARE stmt FROM @createViewSql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT CONCAT('视图 ', viewName, ' 已成功创建/重建，包含 ', (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'table%'), ' 张表') AS result;
    ELSE
        SELECT '未找到任何符合条件的表（table开头的表）' AS result;
    END IF;
END //

DELIMITER ;

关键细节说明 #

• 限定当前数据库：在查询information_schema.TABLES时加上TABLE_SCHEMA = DATABASE()，避免把其他库的table%表也包含进来，这是很容易踩的坑。
• 处理空结果集：如果没有找到任何table开头的表，存储过程会返回提示，不会执行无效的CREATE VIEW语句。
• 视图名自定义：你可以把vw_all_monthly_reports改成你想要的任意视图名。

注意事项 #

• 所有table%的表结构必须完全一致（字段名、数据类型、字段顺序都要匹配），否则UNION ALL会抛出字段不兼容的错误——毕竟是月度报表，这个前提应该是成立的。
• 执行存储过程的账号需要具备：
  • 查询information_schema的权限
  • 创建/删除视图的权限
  • 执行存储过程的权限

使用方法 #

直接调用存储过程即可：

CALL RebuildUnionView();

内容的提问来源于stack exchange，提问作者marko_b123