0

Im trying to get a list of all the user-created databases (not tables) from mysql, but I always get a list containing 'mysql', 'information_schema' and 'performance_schema'. Is it possible to filter these 3 out of the whole list?

I have tried with the query 'SHOW DATABASES' using the LIKE and NOT LIKE and also the wildcard '%', but not luck so far. I also tried with logical operators like AND and OR:

   SHOW DATABASES NOT LIKE 'information_schema' # doesn´t work
   SHOW DATABASES LIKE 'information_schema' # Works but outputs 1 record.
   SHOW DATABASES WHERE 'Database' NOT LIKE 'information_schema' # Works but doesn´t filter anything.

Do you guys have any idea how can I show only the user-created databases in mysql?

NOTE

In my view this is not a duplicate of When to use single quotes, double quotes, and backticks in MySQL, because the question is how to get a filtered list of user-created databases and NOT about the use of the backticks.

Best,

Charlie
  • 252
  • 5
  • 16
  • `Database` should be enclosed in backticks, not single quotes i.e. `SHOW DATABASES WHERE \`Database\` NOT LIKE 'information_schema'` – Nick Aug 19 '19 at 10:56
  • @Nick My question shouldn´t be marked as duplicate, because Im not asking about the use of the backticks, but about how to get a filtered list of databases. – Charlie Aug 19 '19 at 11:13
  • If you replace the single quotes in your query (which are wrong, as described in the duplicate) with backticks, your query works fine. So the question is essentially a duplicate, because you already have the answer, it just doesn't work because you have the wrong quotes. Your complete query can be `SHOW DATABASES WHERE \`Database\` NOT IN ('information_schema', 'mysql', 'performance_schema')` – Nick Aug 19 '19 at 12:22

2 Answers2

1

Try using backtick for the Database Because this worked for me

SHOW DATABASES WHERE `Database` NOT LIKE 'information_schema'
Tunji Oyeniran
  • 3,948
  • 1
  • 18
  • 16
0
mysql> show databases where `database` not like 'information_schema';
+--------------------+
| Database           |
+--------------------+
| mysql              |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)



mysql> show databases where `database` not in('information_schema');
+--------------------+
| Database           |
+--------------------+
| mysql              |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)
s_ramesh
  • 41
  • 4