3

I'm new to MySQL and I am having difficulty understanding how to query across multiple databases.

Explaining the infrastructure, I am currently working on an assignment where I am in a public test Database that has a collection of databases.

I have an identifier value that I want to search across all of the databases that I know exists in a specific table. I'll use the table name "table1" for this example. The problem is that, not all of the databases possess the identifier column I am looking for within the table.

My question involves two parts: -How do I search all of the databases to return a collection of all of the database names that contain a particular value within this column (table1.id) -How can I verify that the column exists so that I can actually go about doing the check to see if the id that I am looking for matches the other databases' table1.id value?

To a smaller scale, I worked out the code for checking an individual table:

SELECT * FROM table1
WHERE searchId = db1.table1.id;

The difference is, I want to search all of the database table1's for this particular value while insuring that this column exists in the table first.

dpark
  • 45
  • 1
  • 3
  • You are "in a public test Database that has a collection of databases". What exactly do you mean by that? – Benvorth Aug 12 '15 at 19:36
  • I am using a database that has within it multiple databases, i.e.: db1 db2 db3 ... Then, within each of the databases, there are a collection of tables that are not necessarily identical to the other databases. – dpark Aug 12 '15 at 19:37
  • do you mean schemas? In MySQL this is the same... See here: http://stackoverflow.com/questions/11618277/difference-between-schema-database-in-mysql – Benvorth Aug 12 '15 at 19:38

2 Answers2

2

This should get you started:

SELECT table_schema 
FROM information_schema.columns 
WHERE table_name = 'table1' AND column_name = 'id'
;

From this, you can use the results in whatever programming language you are using to compose queries specific for each of those databases.

Alternately, I've been finding borderline abuses similar to this helpful lately.

SELECT CONCAT("SELECT '", table_schema, "' "
              "FROM `", table_schema, "`.`", table_name, "` "
              "WHERE `", column_name, "` = ", searchId
       ) AS qStr
FROM information_schema.columns 
WHERE table_name = 'table1' AND column_name = 'id'
;

You concatenate the results of this together, with UNION between, and the resulting query should give you a list of all schemas who have a table with that name (and column) whose value matches searchId.

Edit: Replaced inappropriate backticks above with single-quotes, and... added this below.

SET @criteriaVal := "'somestring'";
-- SET @criteriaVal := 3; -- for example

SELECT CONCAT("SELECT '", table_schema, "' "
              "FROM `", table_schema, "`.`", table_name, "` "
              "WHERE `", column_name, "` = ", @criteriaVal
       ) AS qStr
FROM information_schema.columns 
WHERE table_name = 'table1' AND column_name = 'id'
;
Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • Pardon my lack of knowledge on syntax, but given your second coding block where you mention "searchId" If I wanted to do a search on all of the databases that are output as a result of your first coding block on an identifier such as "RNASEQG00000000001" How would I incorporate that into your second coding block to do a search? – dpark Aug 12 '15 at 19:55
  • Just change `"WHERE ", column_name, " = ", searchId` to `WHERE ", column_name, " = 'RNASEQG00000000001' "` – Uueerdo Aug 12 '15 at 21:07
  • @dpark see edit for a more versatile, two statement, solution. – Uueerdo Aug 12 '15 at 21:12
  • 1
    using your help, I was able to generate a list of strings with query statements for each of the valid tables which is exactly what I want. However, what would be the syntax that I would use in order to UNION all of the results from these SELECT statements into one output? – dpark Aug 12 '15 at 21:22
  • That would depend on your programming environment. – Uueerdo Aug 12 '15 at 21:23
  • Not sure if this helps the question, but I am using a terminal/shell environment to access MySQL. – dpark Aug 12 '15 at 21:30
  • You want the final query to look like `[first qStr] UNION [second qStr] UNION [third qStr]` and so on... – Uueerdo Aug 12 '15 at 21:40
1

You can get the columns of a table in MySQL by querieing the information_schema: 

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'myschema' -- "database"
AND table_name ='mytablename'
Benvorth
  • 7,416
  • 8
  • 49
  • 70