1

I have an issue where the MySQL schema contains more than one active schema and when I go to query a set of tables, I get the error that I need to select the schema.

I know I can just find the name and use the database, but I'm having to search our farm which consists of some 50k individual database names.

Is there a way I can query INFORMATION_SCHEMA for the table_schema which ends in _1 or _2 or any _# - (REGEXP '[a-z]_[[:digit:]]') and then pass that schema name to a select?

Say the schema is database_1

SELECT table.column FROM database_1.table WHERE table.column = 'foobar' ORDER BY 1 DESC LIMIT 1;

I just need to figure out how to pass the database name without using the actual database.

I hope my explanation is clear enough.

2
  • when I go to query a set of tables, I get the error that I need to select the schema. - this means that your current client session have no current/default database. Test - try to execute USE needed_database_name; as the most first SQL statement in your session.
    – Akina
    Jan 11, 2023 at 18:30
  • Is there a way I can query INFORMATION_SCHEMA for the table_schema ... and then pass that schema name to a select? You may query for the database names list which matches your criteria then execute USE statement and set one of these DBnames as current one. USE statement must be executed usng dynamic SQL in this case, because database name cannot be parametrized in this statement.
    – Akina
    Jan 11, 2023 at 18:33

1 Answer 1

1

You can search the information_schema for the one you want, but you can't do that and also use it as a schema identifier in the same query where you look it up.

You will have to do it in two queries:

  1. Look up the schema name.
  2. Format a second query using the result of that lookup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.