JIYIK CN >

In this article, we will introduce many ways to check if a database exists in MySQL.

Check if the database exists in MySQL

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

The MySQL schema is broadly divided into system tables for general operational purposes and data dictionary tables for storing metadata about database items.

Use the use command to check whether the MySQL database exists

Use the use command to determine whether the MySQL database exists. The syntax of the command is as follows:

use `database_name`

The database you want to use is identified here by the name database_name. If the database exists, this command will exit with status code 0; otherwise, it will display the error Unknown database "database_name".

Use the schema_name command to check if the MySQL database exists

You can determine if a MySQL database exists using the schema_name command. The syntax of the command is as follows:

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

Here, database_name is the name of the database that you want to check for existence.

Use the SHOW command to check if the MySQL database exists

Another option is to use the MySQL SHOW command to see how many databases are currently available. The syntax of the command is as follows:

SHOW databases;

Use the mysqlshow command to check if the MySQL database exists

You can use the mysqlshow command to determine if the MySQL database exists. The syntax of the command is as follows:

mysqlshow `database_name`

Here, database_name represents the name of the database for which you want detailed information. If the database exists, this command will exit with status code 0 and print the database and table details; otherwise, it will display the error unknown database 'database_name'.

To further understand the previous concepts, 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 first determined whether the Employees schema existed. We attempted to check each database on the current MySQL server using the second operation.

The third operation changes the given database from the default database, in this case Employees. Information about the Employees database and its tables is shown in the last operation.

Run the above lines of code in any browser compatible with MySQL. It will display the following result.

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 |
+--------------------------------+----------+