How to check if mysql database exists

Question

Is it possible to check if a (MySQL) database exists after having made a connection.

I know how to check if a table exists in a DB, but I need to check if the DB exists. If not I have to call another piece of code to create it and populate it.

I know this all sounds somewhat inelegant - this is a quick and dirty app.

Macario

I'm a novice programmer. Can you please elaborate on why this would be inelegant? – Klarrisa

@Klarrisa just in case you haven't gotten an answer, typically, you don't want your application code to manage the definition of your database. Your application should be limited to Create/Read/Update/Delete (CRUD). You should have other processes for managing the structure of your DB, worked into your integration/deployment process. – Foresail

Answer 1

SELECT SCHEMA_NAME
  FROM INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'DBName'

If you just need to know if a db exists so you won't get an error when you try to create it, simply use (From here):

CREATE DATABASE IF NOT EXISTS DBName;

Bunk

First one good. Second one not so much. You might not have database creation privilege. – Penmanship

@OllieJones second one is good too, the answerer is assuming if OP wants to create a database – Houdini

Why is "INFORMATION_SCHEMA" in all caps? With me it's in all lower case – Desultory

* OK, apparently PHPMyAdmin just displays all database names in lower case, and your query works using both anyway – Desultory

About the 2nd: Besides the privilege issue, do you mean create a database unnecessarily??? Would you also use the same method to check if a file exists??? People must like you very much to upvote you on this one! :) – Atp

Agree with @nawfal; I might not want to create the DB, just know if it exists. – Cherin

I think he gave the second option because OP stated: I need to check if the DB exists. If not I have to call another piece of code to create it and populate it. – Alyse

Warning! "CREATE IF NOT EXISTS" can stall for hours or days. It waits for a metatable lock (mysql 8.0.13 tested) and if you are just during an operation (like compressing ANY table) it will wait until timeout. – Heracles

I tried the first command but received the following error Unknown column 'TEST' in 'where clause – Hairdo

Answer 2

A simple way to check if a database exists is:

SHOW DATABASES LIKE 'dbname';

If database with the name 'dbname' doesn't exist, you get an empty set. If it does exist, you get one row.

Gonnella

Worked better then the solution marked correct. Thanks ] – Patrology

For official information that explains this good answer, go to the official website's documentation page about the command: dev.mysql.com/doc/refman/5.5/en/show-databases.html (a useful tutorial page led me to it, dev.mysql.com/doc/refman/5.5/en/database-use.html ("MySQL 5.5 Reference Manual / Tutorial / Creating and Using a Database"). – Harless

This might be slower than directly querying INFORMATION_SCHEMA, but it's a lot more readable and easy to understand, which was the more important consideration in my case. – Krohn

This is a better solution because if you test for the existence of a database you may then want to create it. The "create database if not exists" is annoying because if the database does exist any following code to define tables will bomb. I can't see how the command is useful. – Khania

@Khania While it may seem annoying, imagine two threads trying to create the same database at the same time and what would happen if one failed. While it's not useful for determining if a database exists before trying to create it, it is useful for preventing collisions while multiple threads may be trying to create it. – Blackshear

Don't use like to test for existence as it could result in all databases being returned if the variable is empty, check my response below: https://mcmap.net/q/92789/-how-to-check-if-mysql-database-exists – Hemingway

Answer 3

From the shell like bash

if [[ ! -z "`mysql -qfsBe "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='db'" 2>&1`" ]];
then
  echo "DATABASE ALREADY EXISTS"
else
  echo "DATABASE DOES NOT EXIST"
fi

Glyconeogenesis

This doesn't actually work... Instead try something like:` result=$(mysql -s -N -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='db'"); if [ -z "$result" ]; then echo "db does not exists"; fi – Ouabain

@StevenGreen's adaptation of this works fine, so +1 for the bash/sql snippet. – Axe

Dont forget to include your user details, either on the command line or via .my.cnf. – Forby

For those curious about the tags used in Steven Green's comment, -s is for silent mode which gives less output, -N will skip the column names when returning the result, and -e is the tag for executing the provided query. – Laplante

Answer 4

If you are looking for a php script see below.

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
  die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
  die ('Cannot use foo : ' . mysql_error());
}

Unpleasant

Answer 5

A very simple BASH-one-liner:

mysqlshow | grep dbname

Hoy

This will also match databases named prefix_dbname_suffix and so on, because grep matches lines that contain the string, not lines that equal the string exactly. – Laos

Answer 6

Here is a bash function for checking if a database exists:

function does_db_exist {
  local db="${1}"

  local output=$(mysql -s -N -e "SELECT schema_name FROM information_schema.schemata WHERE schema_name = '${db}'" information_schema)
  if [[ -z "${output}" ]]; then
    return 1 # does not exist
  else
    return 0 # exists
  fi
}           

Another alternative is to just try to use the database. Note that this checks permission as well:

if mysql "${db}" >/dev/null 2>&1 </dev/null
then
  echo "${db} exists (and I have permission to access it)"
else
  echo "${db} does not exist (or I do not have permission to access it)"
fi

Bunker

+1 for the alternative, but >/dev/null guarantees the result is always null. Try something like if [ -z "$(mysql ${db} 2>&1 </dev/null)" ]; then ... . – Axe

@Axe The >/dev/null doesn't change the exit code from running mysql. It just hides the output if there is an error. The if ...; then part checks the exit code. – Bunker

Answer 7

A great way to check if a database exists in PHP is:

$mysql = mysql_connect("<your host>", "root", "");

if (mysql_select_db($mysql, '<your db name>')) {
    echo "Database exists";
} else {
    echo "Database does not exist";
}

That is the method that I always use.

Phthisic

mysql_ functions are not recommend to use anymore! – Lander

@Lander who says that they are not recommended? My teacher still teaches us mysql. – Massasauga

@MJDelosSantos since they are replaced with mysqli functions and/or PDO classes – Lander

@Lander ah, I see. – Massasauga

Answer 8

Using bash:

if [ "`mysql -u'USER' -p'PASSWORD' -se'USE $DATABASE_NAME;' 2>&1`" == "" ]; then
    echo $DATABASE_NAME exist
else
    echo $DATABASE_NAME doesn't exist
fi

Utility

Answer 9

For those who use php with mysqli then this is my solution. I know the answer has already been answered, but I thought it would be helpful to have the answer as a mysqli prepared statement too.

$db = new mysqli('localhost',username,password);
$database="somedatabase";
$query="SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=?";
$stmt = $db->prepare($query);
$stmt->bind_param('s',$database);
$stmt->execute();
$stmt->bind_result($data);
if($stmt->fetch())
{
    echo "Database exists.";
}
else
{
    echo"Database does not exist!!!";
}
$stmt->close();

Spellbinder

Answer 10

Be careful when checking for existence with a like statement!

If in a series of unfortunate events your variable ends up being empty, and you end up executing this:

SHOW DATABASES like '' -- dangerous!

It will return ALL databases, thus telling the calling script that it exists since some rows were returned.

It's much safer and better practice to use an "=" equal sign to test for existence.

The correct and safe way to test for existence should be:

SHOW DATABASES WHERE `database` = 'xxxxx' -- safe way to test for existence

Note that you have to wrap the column name database with backticks, it can't use relaxed syntax in this case.

This way, if the code creating the variable 'xxxxx' returned blank, then SHOW DATABASES will not return ALL databases, but will return an empty set.

Hemingway

Answer 11

SELECT IF('database_name' IN(SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA), 1, 0) AS found;

Teishateixeira

Answer 12

CREATE SCHEMA IF NOT EXISTS `demodb` DEFAULT CHARACTER SET utf8 ;

Prepare

Answer 13

Here's my way of doing it inside a bash script:

#!/bin/sh

DATABASE_USER=*****
DATABASE_PWD=*****
DATABASE_NAME=my_database

if mysql -u$DATABASE_USER -p$DATABASE_PWD -e "use $DATABASE_NAME";
then
echo "Database $DATABASE_NAME already exists. Exiting."
exit
else
echo Create database
mysql -u$DATABASE_USER -p$DATABASE_PWD -e "CREATE DATABASE $DATABASE_NAME"
fi

Macrophysics

Answer 14

With this Script you can get Yes or No database exists, in case it does not exist it does not throw Exception.

SELECT 
    IF(EXISTS( SELECT 
                SCHEMA_NAME
            FROM
                INFORMATION_SCHEMA.SCHEMATA
            WHERE
                SCHEMA_NAME = 'DbName'),
        'Yes',
        'No')  as exist

Plait

Answer 15

Long winded and convoluted (but bear with me!), here is a class system I made to check if a DB exists and also to create the tables required:

<?php
class Table
{
    public static function Script()
    {
        return "
            CREATE TABLE IF NOT EXISTS `users` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT );

        ";
    }
}

class Install
{
    #region Private constructor
    private static $link;
    private function __construct()
    {
        static::$link = new mysqli();
        static::$link->real_connect("localhost", "username", "password");
    }
    #endregion

    #region Instantiator
    private static $instance;
    public static function Instance()
    {
        static::$instance = (null === static::$instance ? new self() : static::$instance);
        return static::$instance;
    }
    #endregion

    #region Start Install
    private static $installed;
    public function Start()
    {
        var_dump(static::$installed);
        if (!static::$installed)
        {
            if (!static::$link->select_db("en"))
            {
                static::$link->query("CREATE DATABASE `en`;")? $die = false: $die = true;
                if ($die)
                    return false;
                static::$link->select_db("en");
            }
            else
            {
                static::$link->select_db("en");          
            }
            return static::$installed = static::DatabaseMade();  
        }
        else
        {
            return static::$installed;
        }
    }
    #endregion

    #region Table creator
    private static function CreateTables()
    {
        $tablescript = Table::Script();
        return static::$link->multi_query($tablescript) ? true : false;
    }
    #endregion

    private static function DatabaseMade()
    {
        $created = static::CreateTables();
        if ($created)
        {
            static::$installed = true;
        }
        else
        {
            static::$installed = false;
        }
        return $created;
    }
}

In this you can replace the database name en with any database name you like and also change the creator script to anything at all and (hopefully!) it won't break it. If anyone can improve this, let me know!

Note
If you don't use Visual Studio with PHP tools, don't worry about the regions, they are they for code folding :P

Holleran

Answer 16

SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DbName'

1 - exists, 0 - not

Gourde

Answer 17

Rails Code:

ruby-1.9.2-p290 :099 > ActiveRecord::Base.connection.execute("USE INFORMATION_SCHEMA")

ruby-1.9.2-p290 :099 > ActiveRecord::Base.connection.execute("SELECT SCHEMA_NAME FROM         INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'entos_development'").to_a
SQL (0.2ms) SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME =               'entos_development'
=> [["entos_development"]] 
ruby-1.9.2-p290 :100 > ActiveRecord::Base.connection.execute("SELECT SCHEMA_NAME FROM              INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'entos_development1'").to_a
SQL (0.3ms) SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME =            'entos_development1'
=> []

=> entos_development exist , entos_development1 not exist

Adolphus

Answer 18

IF EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = N'YourDatabaseName')
BEGIN    
    -- Database exists, so do your stuff here.
END

If you are using MSSQL instead of MySQL, see this answer from a similar thread.

Floss

This is for MSSQL, not MySQL – Kial

Answer 19

I am using simply the following query:

"USE 'DBname'"

Then check if the result is FALSE. Otherwise, there might be an access denied error, but I cannot know that. So, in case of privileges involved, one can use:

"SHOW DATABASES LIKE 'DBname'"

as already mentioned earlier.

Atp

Answer 20

Another php solution, but with PDO:

<?php
try {
   $pdo = new PDO('mysql:host=localhost;dbname=dbname', 'root', 'password', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ]);
   echo 'table dbname exists...';
}
catch (PDOException $e) {
   die('dbname not found...');
}

Weighin

Your example assumes that PDO is configured to throw an exception Add the appropriate configuration to ensure an exception is being thrown. Here's an example how to do this: $db = new PDO ( $dns, $usr, $pwd, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ] ); – Coplin

Thanks @HerbertPeters, I added that to my answer – Weighin

Answer 21

Following solution worked for me:

mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
-s -N -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='${MYSQL_DATABASE}'"

Westcott

Answer 22

Golang solution

create a test package and add:

import "database/sql"

// testing database creation
func TestCreate(t *testing.T){
    Createdb("*Testdb") // This just calls the **sql.DB obect *Testdb 
    db,err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/*Testdb")
    if err != nil{
        panic(err)
    }
    defer db.Close()
    _, err = db.Exec("USE *Testdb")
    if err != nil{
        t.Error("Database not Created")
    }

} 

Tedric

Answer 23

Using the INFORMATION_SCHEMA or show databases is not reliable when you do not have enough permissions to see the database. It will seem that the DB does not exist when you just don't have access to it. The creation would then fail afterwards. Another way to have a more precise check is to use the output of the use command, even though I do not know how solid this approach could be (text output change in future versions / other languages...) so be warned.

CHECK=$(mysql -sNe "use DB_NAME" 2>&1)
if [ $? -eq 0 ]; then
  # database exists and is accessible
elif [ ! -z "$(echo $CHECK | grep 'Unknown database')" ]; then
  # database does not exist
elif [ ! -z "$(echo $CHECK | grep 'Access denied')" ]; then
  # cannot tell if database exists (not enough permissions)"
else
  # unexpected output
fi

Marylou

Answer 24

DB_EXISTS=$(mysql -h$DB_HOST -u$DB_USERNAME -p -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='$DB_DATABASE'")


if [[ -z  "$DB_EXISTS" ]];
then 
    echo "DATABASE DOES NOT EXIST"
    exit;
else
    echo "DOES EXIST"
fi

Denominative

Answer 25

For someone who need a shell solution, the one I ended up with is

mysql -u$USER -p $DB -e ""

This will execute empty query on the given database. Then check for the exiting code

if [ $? -eq 0 ]; then
    echo "Database $DB exists"
fi

Bipropellant