mysql show databases by condition

Question

I have databases named like this:

    database_100
    database_120
    database_153
    database_143
    ...

I'll call the numbers at the end 'Databse ID' for the sake of this example.

Doing something like SHOW DATABASES LIKE "database_% will output all the DBs and it's fine. Now I want to show databases with id = 100 or 120 or 320 this is just an example, the list of IDs itself is dynamically pulled from an array.

How can I do something like that?

EDIT:

I'm trying to use INFORMATION_SCHEMA

USE INFORMATION_SCHEMA;
SELECT `SCHEMA_NAME` from `SCHEMATA` WHERE `SCHEMA_NAME` LIKE "database_%";

but still can't figure out how to select by the dynamic IDs.

I'm thinking about something like (in pseudo code):

USE INFORMATION_SCHEMA;
SELECT `SCHEMA_NAME` from `SCHEMATA` WHERE `SCHEMA_NAME` LIKE "database_$var" WHILE $var in (121, 153, 143);

Could it be done only in SQL ?

I don't believe MySQL supports dynamic sql. Why not you can use prepare statement.?? try this link rpbouman.blogspot.in/2005/11/… — prashant thakre
– prashant thakre, Commented Aug 19, 2014 at 17:52

prashant thakre · Accepted Answer · 2014-08-19 18:13:58Z

1

you can use prepare statement ,Try the below code it will resolve your issue... However you need modify this line set @schema := 'tes%'; according to your requirement

 mysql> USE INFORMATION_SCHEMA
    Database changed
    mysql> prepare stmt from
        -> 'SELECT SCHEMA_NAME from SCHEMATA WHERE SCHEMA_NAME like ?';
    Query OK, 0 rows affected (0.00 sec)
    Statement prepared

    mysql> set @schema := 'tes%';
    Query OK, 0 rows affected (0.00 sec)

    mysql> execute stmt using @schema;
    +-------------+
    | SCHEMA_NAME |
    +-------------+
    | test        |
    +-------------+
    1 row in set (0.00 sec)

answered Aug 19, 2014 at 18:13

prashant thakre

5,1753 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql show databases by condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related