A former colleague recently posed a problem to me: “This guy has a table full of table names, and he wants to run a query that unions those tables.” I spent a couple of minutes telling him why it was a bad idea. It’s not a very robust solution, and an incorrect value in your table of tables could cause the query to fail, as could a missing, renamed, or altered column on one of the target tables. In the end, my former colleague was able to argue for an architecture change. However, my curiosity had been piqued.
I ask: “Can it be done in a stored proc?”
Hmmm… now I feel a challenge!
First, I created a simple test environment – three simple identical tables and a table of tables.
USE test; CREATE TABLE `a` (`a_id` INT AUTO_INCREMENT, `some_data` VARCHAR(20), PRIMARY KEY (`a_id`) ); INSERT INTO `a` (`some_data`) VALUES ('z'),('y'); CREATE TABLE `b` (`b_id` INT AUTO_INCREMENT, `some_data` VARCHAR(20), PRIMARY KEY (`b_id`) ); INSERT INTO `b` (`some_data`) VALUES ('x'),('w'); CREATE TABLE `c` (`c_id` INT AUTO_INCREMENT, `some_data` VARCHAR(20), PRIMARY KEY (`c_id`) ); INSERT INTO `c` (`some_data`) VALUES ('v'),('u'); CREATE TABLE `union_tables` (`union_tables_id` INT AUTO_INCREMENT, `table_name` VARCHAR(64) NOT NULL, PRIMARY KEY (`union_tables_id`) ); INSERT INTO `union_tables` (`table_name`) VALUES ('a'),('b'),('c');
Once I thought about it, the solution was actually really easy. I new that I was going to be using CONCAT, and suspected that GROUP_CONCAT would also come into play. The hinge pin would be making “UNION” the concatenation separator. I already new about CONCAT_WS(), but I didn’t know about the SEPARATOR keyword for GROUP_CONCAT (see: MySQL Reference Manual). With that, everything else fell into place:
SELECT GROUP_CONCAT( CONCAT( 'SELECT `some_data` FROM `', table_name,'`' ) SEPARATOR ' UNION ' ) INTO @sqlstring FROM `union_tables`; PREPARE sqlquery FROM @sqlstring; EXECUTE sqlquery; DEALLOCATE PREPARE sqlquery;
Now this example runs inside of the MySQL command line client. But you should be able to run the initial SELECT statement inside of any programming language and create a prepared statement from the results.