1

A Case in point is getting data from tables which is generated every day with new name like below: 

select table_name from INFORMATION_SCHEMA.TABLES where table_name like 'ifhcraw%';

ifhcraw_2016_03_31_24
ifhcraw_2016_04_01_8
ifhcraw_2016_04_02_14
ifhcraw_2016_04_03_20
ifhcraw_2016_04_05_8
ifhcraw_2016_04_06_14

As you can see, there is a name convention based on rule - "ifhcraw+year+month+day+hour". But the hour of generation is not known. Is there any way to create some SQL script which can get all data from the tables "where table_name like 'ifhcraw%'"

Javid
  • 47
  • 2
  • 8
  • You have to use dynamic SQL with a `PREPARE` statement in a stored procedure. – Barmar Apr 07 '16 at 07:42
  • 1
    Creating a new table every day seems like a bad design. Why don't you just have a single table where the date is a column that you can match? – Barmar Apr 07 '16 at 07:43
  • @Barmar, could you provide some little example? May be the question is not clear. I need to get data from all tables at the same time, like: select * from ifhcraw_2016_03_31_24, ifhcraw_2016_04_01_8 .... – Javid Apr 07 '16 at 11:19
  • You want a `JOIN` between all the tables with these names? Or did you mean you want a `UNION` of them? – Barmar Apr 07 '16 at 11:22
  • Have you considered using the `FEDERATED` engine to create a table that contains the union of all these tables? – Barmar Apr 07 '16 at 11:23
  • @Barmar, {UNION} or {JOIN} are not useful in this case, because the table names are changing every day. – Javid Apr 07 '16 at 11:31
  • If you're selecting from all the tables at once, you need to combine them in some way. Either you're joining them or you're unioning them. – Barmar Apr 07 '16 at 11:33
  • What does the fact that they're changing every day have to do with how you combine them in the query? – Barmar Apr 07 '16 at 11:34
  • When you list multiple tables in the `FROM` clause, that's an implicit `JOIN`, you know that? – Barmar Apr 07 '16 at 11:34

1 Answers1

1

You can use GROUP_CONCAT to combine all the table names in a single string.

SELECT @queries := GROUP_CONCAT(CONCAT('SELECT * FROM ', table_name) SEPARATOR ' UNION ')
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE 'ifhcraw%';

PREPARE stmt FROM @queries;
EXECUTE stmt;

The first query finds all the matching table names, and creates a query like SELECT * FROM <tablename>. Then it uses GROUP_CONCAT to connect them all with UNION, so the resulting query looks like:

SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3
...

Note that by default GROUP_CONCAT is limited to returning 1024 characters. If you have lots of these tables, you'll need to increase group_concat_max_len to get everything.

Barmar
  • 741,623
  • 53
  • 500
  • 612