CODEWITHSUNDEEP
mysqlsqldatabase
dpark
dpark

Reputation: 55

MySQL Searching for value across multiple databases

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.

Upvotes: 4

Views: 4891

Answers (2)

Uueerdo
Uueerdo

Reputation: 15961

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'
;

Upvotes: 3

Benvorth
Benvorth

Reputation: 7722

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'

Upvotes: 1

Related Questions