Generate a MySQL query string using MySQL

Sometimes you may want to use MySQL to generate a series of SQL statements.

In this example we want to create a series of colon (;) delimited MySQL commands that the contents of a series of tables that exists in many different databases. The secret to this is knowing how to use the CONCAT function to create strings. The problem with CONCAT is that you cannot do something like this

CONCAT('I call my ', `animal`, ' ', `name`)

to get a list like the following:

I call my dog sassy

I call my cat sookie

I call my madagascar hissing cockroach Xavier

you have to instead do the following:

CONCAT('I call my ', CONCAT(`animal`, CONCAT(' ', `name`)))

To put this to use lets start with an example and work thru it. Imagine you have a series of databases called ac_1, ac_2, ac_3, etc. In each database there exists the same table `report` and you want to list the table `report` from every database easily. Let's say there are hundreds of ac_ databases so to write a query to list the contents of each database would be time intensive indeed. So we want to generate a series of queries using MySQL. There are of course better ways to do this than to generate a series of queries as strings, but we need an example so this is the one I am using.

1.) We start by constructing the following base query:

SELECT `table_schema`

FROM `information_schema`.`tables`

WHERE `table_schema` LIKE 'ac_%' GROUP BY `table_schema`

which gives us an table output like the following:

table_schema

ac_1

ac_2

ac_3

We could replace this SELECT statement with any number of SELECTs with the criteria we need to create our list. For instance instead of listing databases we could list tables with special criteria and so on and so forth.

2.) We want to put each of these rows into a statement like SELECT * FROM ac_1.report. To do this we add to our query the following:

SELECT CONCAT

(

CONCAT( 'select * from ', `table_schema` ) , '.report'

) AS qry

FROM (

SELECT `table_schema`

FROM `information_schema`.`tables`

WHERE `table_schema` LIKE 'test%'

GROUP BY `table_schema`

) AS dbs

Which gives us an output like the following:

qry

select * from ac_1.report

select * from ac_2.report

select * from ac_3.report

3.) Lets create one text field which has all our queries in a colon (;) delimited sequence by adding the following to our MySQL statement.

SELECT GROUP_CONCAT(lst.qry SEPARATOR '; ') as commands

FROM

(

SELECT CONCAT

(

CONCAT( 'select * from ', `table_schema` ) , '.report'

) AS qry

FROM (

SELECT `table_schema`

FROM `information_schema`.`tables`

WHERE `table_schema` LIKE 'test%'

GROUP BY `table_schema`

) AS dbs

) as lst

Which will give you the final result you are looking for as follows:

commands

select * from ac_1.report; select * from ac_2.report; select * from ac_3.report

You may then cut and paste that field, write the field to a file or whatever else you need to do in order to run it against your MySQL server to get all the contents of all the tables.