One of the first things you’ll want to know when logging onto a new MySQL server is what databases are available. You may want a list of databases when performing maintenance. Or, you may just be curious or trying to find an old database whose name you forgot long ago.
A blank command line can be intimidating. Each one of these cases calls for a simple command, and MySQL provides it in the form of SHOW DATABASES. This command has very straightforward usage, but you’ll soon find it coming in handy if you work with MySQL.
In this article, you'll find out everything you need to know about listing MySQL databases.
How to Use the SHOW DATABASES Command
Once you’ve logged onto your MySQL server, enter the text SHOW DATABASES; on the command line and press Enter on your keyboard—you can see an example below.
MySQL returns the results in a table with one column: Database. This column contains the name of each database and orders them alphabetically. The summary line tells you how many rows—i.e. databases—there are.
You should always see at least four MySQL list databases by default. These are special system databases that MySQL installs itself:
- mysql.
- information_schema.
- performance_schema.
- sys.
You’ll see what one of them—information_schema—is used for later on.
Filtering the Results of SHOW DATABASES
You can use the same LIKE condition that you use in a WHERE clause of a SELECT statement. LIKE takes a single argument, a pattern to match on. The pattern can include two special characters: % (percent) and _ (underscore). These match any string and any single character respectively.
For example, to list all databases with the letter a in their name:
SHOW DATABASES LIKE '%a%';
How to Use More Complicated Conditions
If you need to use a more complicated conditional than the basic pattern matching of LIKE, you can still use the familiar WHERE clause. The trick you’ll need to apply is to refer to the column representing database names. As you can see from the output above, it’s simply Database. Now, because MySQL considers this a reserved keyword, you’ll need to escape it with backticks.
SHOW DATABASES WHERE LENGTH(`Database`) > 6;
The results now just contain tables with names greater than six characters long:
Filtering Databases by Other Metadata
MySQL supports a few other database-related fields, but they’re not available via SHOW_DATABASES. For these, you’ll need to make use of the schemata table from the information_schema database. Here’s what the structure of this special system table looks like:
Aside from SCHEMA_NAME, which is exactly the same as the Database column from SHOW DATABASES, there are just two useful fields: DEFAULT_CHARACTER_SET_NAME and DEFAULT_COLLATION_NAME. The other fields always have the same value, so are pointless when it comes to filtering.
The DEFAULT_CHARACTER_SET_NAME and DEFAULT_COLLATION_NAME fields define which characters the database supports, and how to order them. You may not have thought about them before, but they are important if you’re dealing with non-ASCII text.
Knowing the difference between ASCII and Unicode is important! If you have several databases installed from different sources, you’re more likely to have a range of values.
Reusing the previous example, you can filter on the columns from this table just like any other:
SELECT schema_name FROM information_schema.schemata WHERE DEFAULT_CHARACTER_SET_NAME='utf8';
You can perform any other table operation on the schemata table, such as grouping:
SELECT DEFAULT_CHARACTER_SET_NAME, COUNT(*) FROM information_schema.schemata GROUP BY DEFAULT_CHARACTER_SET_NAME;
Using a Simple Command in MySQL to List Databases
This command is probably the simplest that MySQL has to offer. But that doesn’t stop it from being very useful. If you ever find yourself staring at a MySQL command line, mind drawing a blank, SHOW DATABASES is a good way of starting from scratch.
Once you’ve got a database in place, and you know which one you’re working with, it’s time to learn more about schemas and how best to organize your data.