How to query the column for all similar tables across multiple identical databases in MySQL

Question

Using the info from this other StackOverFlow question I can query the list of all databases that have the same table:

SELECT 
    table_name, table_schema AS dbname
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    table_name = 'myCommonTableName'

This will give me the list of database that have the table I'm interested in. My question is now how can I view the values of those tables instead of just the names of the tables. In other words something along the lines of:

SELECT 
    myCommonTableName.id 
FROM 
   [all tables from all databases - query above]
WHERE
    myCommonTableName.someValue > someTest

In other words I want to get all the rows from all the identical tables (multi-tenant) for all the databases that match a specific criteria. I need to do this for user and account management. Is it possible? And if so how?

UPDATE To further clarify as there seems to be some confusion, I do NOT want to do this:

SELECT * FROM myTable WHERE myTable.id = (SELECT id FROM anotherTable WHERE someOtherCondition).

What I'm trying to do is something like:

SELECT * FROM [databaseList].myTable WHERE myTable.id = 1.

Or to be more precise:

for(listOfDatabases)
    SELECT * FROM [database.x].myTable WHERE myTable.id = 1

Basically I want to get all the results from all myTable in all my databases where they have the same ID value of 1. It's actually a different WHERE clause but you get the idea.

Answer 1

You will need to wrap your /usr/bin/mysql interactions with a higher level language, like bash, python, or java. Pick an implementation language.

You suggested (essentially) select id from myCommonTableName. You will need to either use that query within a certain DB environment (/usr/bin/mysql --database=myDbName), or else select id from myDbName.myCommonTableName.

The sticky wicket is that DB names are not known apriori. You have an initial information_schema.tables query that supplies DB names, and you will need bash or another language to insert those DB names into fully qualified SELECT statements.

You've not yet granted permission to use a particular auxiliary language. But suppose we settle on bash, and mysql_creds supplies suitable credentials for DB login, and you've created a very nice template query. Then a solution might look like this:

echo 'select table_schema as dbname from information_schema.tables where ...' |
    mysql_creds --database=mysql > /tmp/dbnames.txt
for DBNAME in `cat /tmp/dbnames.txt`
do
    cat template.sql | mysql_creds --database=${DBNAME} | tee /tmp/result-${DBNAME}.txt
done

Feel free to sed the template if you want $DBNAME to be part of each query.

You might find it helpful to run wc -l or diff -u over /tmp/result-*.txt

One could write a stored procedure LOOP that does

set @q = concat('select option_value from ', scName, '.wp_options ...'
PREPARE stmt1 FROM @q;
EXECUTE stmt1;

but at that point we have left SQL92 far behind, we are computing strings and running eval on them, works out pretty similar in mysql, bash, or others. The OP hasn't yet described what scripting approach is best suited to his needs. Writing code is seldom difficult, but eliciting requirements often is.

Assume the template query mentions @DB@.myCommonTableName as part of a select. If we wish to impose a new requirement, like "no more than two DB connections shall be created", then it is straightforward to incorporate that into a loop:

echo 'select table_schema as dbname from information_schema.tables where ...' |
    mysql_creds --database=mysql > /tmp/dbnames.txt
rm -f /tmp/queries.sql
for DBNAME in `cat /tmp/dbnames.txt`
do
    sed -e "s/@DB@/${DBNAME}/"  < template.sql  >> /tmp/queries.sql
done
cat /tmp/queries.sql | mysql_creds --database=mysql | tee /tmp/results.txt