MYSQL How to UNION Multiple "Unknown" Tables

Question

I have some History Tables with Structure of "TableNameYYMM" How can I UNION all History Tables which Starts with "TableName"?

I need a search over all History Tables.

I tryed it with PREPARE Execute and DEALLOCATE. But i get everytime a SQL Error.

SET group_concat_max_len = 2048;

SET @startQuery = (SELECT CONCAT('SELECT col1, col2, col3 FROM ',TABLE_SCHEMA,'.', TABLE_NAME)
FROM information_schema.tables 
WHERE 
    ENGINE = 'MyISAM'
    AND TABLE_NAME like 'hist%'
ORDER BY TABLE_NAME
LIMIT 0,1);



SET @subquery = (SELECT GROUP_CONCAT(@startquery, 'UNION ALL SELECT col1, col2, col3 FROM ',TABLE_SCHEMA,'.', TABLE_NAME)
FROM information_schema.tables 
WHERE 
    ENGINE = 'MyISAM'
    AND TABLE_NAME like 'hist%'
ORDER BY TABLE_NAME
LIMIT 1,1000);


PREPARE stmt1 FROM '? AS combinedTable';
EXECUTE stmt1 USING @subquery;
DEALLOCATE PREPARE stmt1;

On Part 1 (@startquery) i try to get the first part of the Query "select xxx from table1"

On Part 2 (@subquery) I tried to get all unions (from table2-max 1000)

select xxx from table1
UNION ALL select xxx from table2
UNION ALL select xxx from table3
...

I hope someone have an idea about this problem.

score 1 · Accepted Answer · answered Apr 12 '17 at 15:39

/*
create table history1701 (id int,col1 int,col2 int);
create table history1702 (id int,col1 int,col2 int);
create table history1703 (id int,col1 int,col2 int);
create table history1704 (id int,col1 int,col2 int);
insert into history1701 values (1,1,1);
insert into history1702 values (2,2,2);
insert into history1703 values (3,3,3);
insert into history1704 values (4,4,4);
*/

SET @startQuery = (SELECT CONCAT('SELECT col1, col2 FROM ',TABLE_SCHEMA,'.', TABLE_NAME)
FROM information_schema.tables 
WHERE 
#    ENGINE = 'MyISAM'    AND 
    TABLE_NAME like 'hist%17%'
ORDER BY TABLE_NAME
LIMIT 0,1);

SET @subquery = (
SELECT group_CONCAT(' UNION ALL SELECT col1, col2 FROM ',TABLE_SCHEMA,'.', TABLE_NAME order by table_name separator ' ' )
FROM information_schema.tables 
WHERE 
     TABLE_NAME like 'hist%17%'
     and table_name <> (
     select table_name from information_schema.tables WHERE 
#    ENGINE = 'MyISAM'    AND 
    TABLE_NAME like 'hist%17%'
    ORDER BY TABLE_NAME
    LIMIT 0,1
    )
);


select @startquery;
set @subquery = concat(@startquery,@subquery);

PREPARE stmt1 FROM @subquery;
EXECUTE stmt1 ;

DEALLOCATE PREPARE stmt1;

In your code The limit 1,1000 in the first set @subquery returns a null value (not good) and generates a comma after each union all (also not good). I have amended this to exlude the first history table and changed the group concat separator to a space and moved the order by to within the group_concat.

MYSQL How to UNION Multiple "Unknown" Tables

1 Answers1