创建动态联合

数据库是mysql/mariadb。
我设计了一个数据库来存储每月的报告。他们的名字是（例如）：表1，表2，表3。。。
我想创建函数/过程，它将创建/重新创建包含所有表union（union all）的视图。
比如：
1首先从信息架构中选择所有表名。

SELECT TABLE_NAME from information_schema.`TABLES` where TABLE_NAME like 'table%'

然后我将尝试在某个循环中设置它，以使用第一个查询的结果集。
但我在第一步中遇到了问题，在第一步中，我尝试只合并一个固定表+第一个查询中的一个表，它会向我返回错误。
我试着：

select * from `table4`
UNION
SELECT * from (SELECT TABLE_NAME from information_schema.`TABLES`
    where TABLE_NAME like 'table%' limit 1) as dd

它返回错误：使用的select语句有不同的列数，但是当我执行子查询时，我得到一个结果，表的名称正确，当我在from子句中设置该名称而不使用子查询时，它就工作了。
你知道为什么会这样，也许还有一些关于如何实现这种动态联盟的建议。

我想稍微推动一下会帮助你找到处理这个问题的正确方法。
首先，正如timbiegeleisen所建议的，继续的方法是使用动态sql，如果在尝试运行查询之前无法绝对确定表名，那么这是唯一的方法。
第二，您正确地认为您需要从查询information\u schema.table开始，这应该使用游标来完成。然后，应该使用该查询的结果构建一个查询字符串，然后准备并执行该字符串。
第三，我认为您在帖子中包含的错误消息专门指的是查询的运行，并不表示monthly表有任何不同。除非每个部分的结果返回相同的列数，否则不能进行并集。
第四，因为我们要动态地构建查询，所以这必须在存储过程中完成，不可能在存储函数中完成。
mysql文档中有关于使用cursor和prepare/execute的好教程，您应该阅读。我下面给出的版本将基于这些例子。我假设唯一的输入参数是模式名（以防服务器上的另一个数据库中碰巧有一些名称类似的表）。

DELIMITER //

DROP PROCEDURE IF EXISTS dyn_union //
CREATE PROCEDURE dyn_union(IN v_sname VARCHAR(64))
READS SQL DATA
BEGIN
  -- NB the order of declaration for variables cursor 
  -- and handler must be strictly observed

  DECLARE sname VARCHAR(64);      -- variable the schema names  
  DECLARE tname VARCHAR(64);      -- variable the table names  

  DECLARE done INT DEFAULT FALSE; -- cursor control variable
  DECLARE cur1 CURSOR FOR 
    SELECT table_schema, table_name 
    FROM information_schema.TABLES
    WHERE table_schema = v_sname 
    AND table_name LIKE 'table%';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET @sql = '';  -- build the query string in this var

  OPEN cur1;

  read_loop: LOOP                 -- loop over the rows returned by cursor
    FETCH cur1 INTO sname, tname; -- fetching the schema and table names
    IF done THEN
      LEAVE read_loop;
    END IF;

    IF @sql = '' THEN  -- build the select statement  
      SET @sql := CONCAT('SELECT * FROM `', sname, '`.`', tname, '`');
    ELSE
      SET @sql := CONCAT(@sql, ' UNION ALL SELECT * FROM `', sname, '`.`', tname, '`');
    END IF;
  END LOOP;

  CLOSE cur1;

select @sql;

  PREPARE stmt FROM @sql;  -- prepare and execute the dynamically
  EXECUTE stmt;            -- created query.
  DEALLOCATE PREPARE stmt;  

END //

DELIMITER ;

-- call the procedure
CALL dyn_union('your_db_name');

创建动态联合

1条答案

相关问题

热门标签

最新问答