Bulk apply alias to table columns in MYSQL

Question

I'm working with a 3rd party MYSQL database over which I have no control except I can read from it. It contains 51 tables with identical column structure but slightly different names. They hold daily summaries for a different data source. Example Table:

CREATE TABLE `archive_day_?????` (
  `dateTime` int(11) NOT NULL,
  `min` double DEFAULT NULL,
  `mintime` int(11) DEFAULT NULL,
  `max` double DEFAULT NULL,
  `maxtime` int(11) DEFAULT NULL,
  `sum` double DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  `wsum` double DEFAULT NULL,
  `sumtime` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

where ????? changes to indicate the type of data held.

The dateTime field is mirrored across all tables being midnight of every day since the system has been running.

I want to produce a single data set across all tables using an inner join on the dateTime. But to avoid writing

SELECT ad1.maxtime as ad1_maxtime, ad2.maxtime as ad2_maxtime...

51 times for 9 fields is there a way I can bulk create aliases e.g

ad1.* as ad_*, ad2.* as ad_* and so on.

I have looked at Create Aliases In Bulk? but this doesn't seem to work for MySQL. Ultimatly the data is being used by a Django ORM.

EDIT: Unfortunately Union doesn't uniquely identify the fields or group them together e.g.

SELECT * FROM `archive_day_ET` UNION ALL SELECT * FROM `archive_day_inTemp`

results in:

@Strawberry Thanks for the thought sadly it doesn't give the result I am looking for. See edited response. — Byte Insight, Dec 22 '18 at 10:06
Which version of MySql? Btw, why not just union them with an additional source field that's different for each unioned select? — LukStorms, Dec 22 '18 at 10:18
About that [other SO post](https://stackoverflow.com/questions/34930363/), MySql has [group_concat](https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php). — LukStorms, Dec 22 '18 at 10:28
@LukStorms Thanks Luke. I'll take a look at group_concat. Do you have an easily accessible example of adding an additional source field? — Byte Insight, Dec 22 '18 at 10:33
It's simple. `select 'ET' as src, * from archive_day_ET union all select 'inTemp', * from archive_day_inTemp union all ...`. Although it's better not to use `*` but the actual field names. Because if someone adds 1 column to 1 of the tables that unioned query would break if you use `*`. — LukStorms, Dec 22 '18 at 10:42
If you would go for joins, then I assume you would join the tables on that `dateTime` field. But then to make sure no records are overlooked that could require FULL JOIN's. But sadly, MySql [doesn't have a FULL JOIN](https://stackoverflow.com/questions/7978663/). — LukStorms, Dec 22 '18 at 10:47
It would be preferable to have all data corresponding to one date time on one row as it's easier for display. I'm thinking about trying joins built using a string formatter function. Dynamically produce the query based on list of tables and list of columns. — Byte Insight, Dec 22 '18 at 10:51

LukStorms · Answer 1 · 2018-12-22T12:06:27.987

To generate a string with all the field names from those tables, you could query information_schema.columns

For example:

SELECT 
 GROUP_CONCAT(CONCAT(TABLE_NAME,'.`',column_name,'` AS `',column_name,'_',replace(TABLE_NAME,'archive_day_',''),'`') SEPARATOR ',\r\n')
FROM information_schema.columns
WHERE TABLE_NAME like 'archive_day_%'

A test on db<>fiddle here

And to generate the JOIN's then you could use information_schema.tables

For example:

SELECT CONCAT('FROM (\r\n ',GROUP_CONCAT(CONCAT('SELECT `dateTime` FROM ',TABLE_NAME) SEPARATOR '\r\n UNION\r\n '),'\r\n) AS dt \r\nLEFT JOIN ',
 GROUP_CONCAT(CONCAT(TABLE_NAME,' ON ',
 TABLE_NAME,'.`dateTime` = dt.`dateTime`') SEPARATOR '\r\nLEFT JOIN ')) as SqlJoins
FROM information_schema.tables
WHERE TABLE_NAME like 'archive_day_%'

A test on db<>fiddle here

For the 2 example tables they would generate

archive_day_ET.`dateTime` AS `dateTime_ET`, 
archive_day_ET.`min` AS `min_ET`, 
archive_day_ET.`mintime` AS `mintime_ET`, 
archive_day_ET.`max` AS `max_ET`, 
archive_day_ET.`maxtime` AS `maxtime_ET`, 
archive_day_ET.`sum` AS `sum_ET`, 
archive_day_ET.`count` AS `count_ET`, 
archive_day_ET.`wsum` AS `wsum_ET`, 
archive_day_ET.`sumtime` AS `sumtime_ET`, 
archive_day_inTemp.`dateTime` AS `dateTime_inTemp`, 
archive_day_inTemp.`min` AS `min_inTemp`, 
archive_day_inTemp.`mintime` AS `mintime_inTemp`, 
archive_day_inTemp.`max` AS `max_inTemp`, 
archive_day_inTemp.`maxtime` AS `maxtime_inTemp`, 
archive_day_inTemp.`sum` AS `sum_inTemp`, 
archive_day_inTemp.`count` AS `count_inTemp`, 
archive_day_inTemp.`wsum` AS `wsum_inTemp`, 
archive_day_inTemp.`sumtime` AS `sumtime_inTemp`

And

FROM ( 
 SELECT `dateTime` FROM archive_day_ET
 UNION 
 SELECT `dateTime` FROM archive_day_inTemp
) AS dt
LEFT JOIN archive_day_ET ON archive_day_ET.`dateTime` = dt.`dateTime` 
LEFT JOIN archive_day_inTemp ON archive_day_inTemp.`dateTime` = dt.`dateTime`

Hi Luke. Thanks. I'll take a look later. Now on Xmas Prep Chores and Outside Projects while I have day light. Thanks for suggestions. — Byte Insight, Dec 22 '18 at 11:24
Hi Luk. So i have had a play with this tonight and it seems that it works OK outside Django - so thanks but inside the connection/raw query calls of Django it breaks and returns 1. I'll keep playing and update the Q if I find a solution. — Byte Insight, Dec 22 '18 at 19:32

Bulk apply alias to table columns in MYSQL

1 Answers1