Find database name from select query

Question

I've googled pretty hard on this topic but didn't manage to find anything relevant.

MySQL question: is it possible to display the database that holds the table row in a select query?

For example, I have 3 databases each with the same structure. I run something like this:

select database(), id, name, created_on from (
select * from db1.user
union all
select * from db2.user
union all 
select * from db3.user) as q where id = "123";

In this case, the database() refers to the current database that the query is being run from. I want to change it to show the database name that's holding the row and table instead (db1,db2 or db3)

I am not sure if this is possible so I'm hoping someone will have a clue on this. Thanks!

You could do this: `select id,name,created_on,'db1' as db from db1.user union all...` — Strawberry, Aug 23 '18 at 08:56
Added `database-metadata` tag as that should be possible with it. — Vlastimil Ovčáčík, Aug 23 '18 at 08:59
@Strawberry thank you! i don't know how to upvote a comment but gave a tick for a similar solution below. — Mookanana, Aug 24 '18 at 03:40

Brian Cryer · Accepted Answer · 2018-08-23T10:55:55.603

The thing is that the DATABASE() function returns the name of the current default database. This won't be the same as the database used in the query when you are explicitly including the database as part of the query.

An alternative would be to include the name of the database in your select, thus:

select db, id, name, created_on from (
select "db1" as db, user.* from db1.user
union all
select "db2" as db, user.* from db2.user
union all 
select "db3" as db, user.* from db3.user) as q where id = "123";

Be aware that that "database" is a reserved word (because it refers to the database function, so remember that if you decide to change the name I've used from "db".

score 0 · Answer 2 · answered Aug 23 '18 at 10:13

You can achieve the desired result by following below tricky steps.

1) First, create db_name column in the user table of all three databases.
2) Then,In your insert into query add db_name's value as database(), hence while insertion, it will store respective database name ( i.e. When insert query is being executed from db1 then the value of db_name will be db1, similarly db_name column of db2 and db3 will have values db2 and db3 respectively.
3) Then in your query just replace database() by db_name.

Thus you will get respective database name.

thanks for your suggestion! it will work, however this means changing the data structure and data of the db which is not an option in my case. i went with the solution made by Brian Cryer below that just adds the db name as a select column. — Mookanana, Aug 24 '18 at 03:39

Find database name from select query

2 Answers2