How to Check if a Database Exists in MySQL

Today’s post will look at many methods to check if a database exists in MySQL.

Check if a Database Exists in MySQL

The system schema is the one used by MySQL. It includes tables that contain data needed by the running MySQL server.

The MySQL schema is broadly categorized as system tables for general operational uses and data dictionary tables for storing database item metadata.

Use the use Command to Check if a MySQL Database Exists

Using the use command, you may determine whether or not a MySQL database exists. This command’s syntax is as follows:

use `database_name`

The database you want to utilize is identified here by the name database_name. If the database is there, this command will exit with a status code of 0; else, it will display the error Unknown database "database_name".

Use the schema_name Command to Check if a MySQL Database Exists

You may determine whether or not a MySQL database exists by using the schema_name command. This command’s syntax is as follows:

SELECT SCHEMA_NAME
  FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';

Here, database_name is the database name you wish to check whether it exists.

Use the SHOW Command to Check if a MySQL Database Exists

Another option is to use MySQL’s SHOW command to see how many currently available databases are. This command’s syntax is as follows:

SHOW databases;

Use the mysqlshow Command to Check if a MySQL Database Exists

You may determine whether or not a MySQL database exists by using the mysqlshow command. This command’s syntax is as follows:

mysqlshow `database_name`

Here, database_name denotes the name of the database you want details about. If the database is present, this command will exit with the status code 0 and output database and table details; otherwise, it will display the error Unknown database "database name".

To further understand the previous concept, consider the following example:

SELECT SCHEMA_NAME
  FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'Employees';
SHOW databases;
use Employees;
mysqlshow `Employees`;

In the previous example, we are first determining whether or not the Employees schema is present. We attempt to inspect every database currently on the MySQL server with the second operation.

The third operation changes the given database, in this instance, Employees, from the default database. The information about the Employees database and its tables is displayed in the last operation.

Run the above code line in any browser compatible with MySQL. It will display the following outcome.

Output:

+-------------+
| SCHEMA_NAME |
+-------------+
| Employees   |
+-------------+
1 row in set (0.00 sec)

+--------------------+
| Database           |
+--------------------+
| Employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


Database: Employees
+--------------------------------+----------+
|             Tables             | Columns  |
+--------------------------------+----------+
| employee                       |       13 |
| employee_audit                 |       10 |
| employee_salary                |        5 |
+--------------------------------+----------+