MySQL database is popularly used in many websites and applications around the world. Often developers directly run SQL queries against a database without even checking if it exists or not. If a database (or its table) mentioned in your SQL query does not exist then the database server will throw an error and will not run subsequent queries. So it is always a good practice to check if a database exists before trying to access it the first time in your application or website. Once you are sure the database exists, you can always run a set of SQL queries against it, afterwards. In this article, we will learn how to check if MySQL database exists.
How to Check if MySQL Database Exists
It is very easy to check if a MySQL database exists on your server. Every MySQL server has a database named INFORMATION_SCHEMA that contains data about all databases, tables, columns in your server. It is created by MySQL at the time of installation and automatically updated whenever you create, modify, or delete any database, table or column from your server. You can log into MySQL console and run the following SQL query against this database to check if your database exists or not. Replace database_name with the name of database to be checked.
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'database_name'
You can also run the above query from within your script such as PHP file.
If the above query returns zero records in result it means the database does not exist, else it will return name of your database indicating that it exists.
If your website or application relies on number of records returned in above query, you can modify it as shown below.
SELECT count(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'database_name'
If you want to create database if it does not exist, and you have creation privileges, then you may run the following query to create database.
CREATE DATABASE IF NOT EXISTS database_name;
In this article, we have learnt how to check if MySQL database exists.
Forbidden Characters in Windows & Linux Filename