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

User · Accepted Answer

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

User · Answer

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

User · Answer

A great way to check if a database exists in PHP is    mysql   mysql connect   lt your host gt     root         if  mysql select db  mysql    lt your db name gt           echo  Database exists     else       echo  Database does not exist       That is the method that I always use

User · Answer

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

User · Answer

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

User · Answer

A very simple BASH-one-liner   mysqlshow   grep dbname

User · Answer

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

User · Answer

From the shell like bash  if      -z   mysql -qfsBe  SELECT SCHEMA NAME FROM INFORMATION SCHEMA SCHEMATA WHERE SCHEMA NAME  db   2 gt  amp 1       then   echo  DATABASE ALREADY EXISTS  else   echo  DATABASE DOES NOT EXIST  fi

User · Answer

CREATE SCHEMA IF NOT EXISTS  demodb  DEFAULT CHARACTER SET utf8

User · Answer

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- gt prepare  query    stmt- gt bind param  s   database    stmt- gt execute     stmt- gt bind result  data   if  stmt- gt fetch          echo  Database exists      else       echo Database does not exist         stmt- gt close

User · Answer

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

User · Answer

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  quot use DB NAME quot  2 gt  amp 1  if      -eq 0    then     database exists and is accessible elif     -z  quot   echo  CHECK   grep  Unknown database   quot     then     database does not exist elif     -z  quot   echo  CHECK   grep  Access denied   quot     then     cannot tell if database exists  not enough permissions  quot  else     unexpected output fi

User · Answer

SELECT IF  database name  IN SELECT SCHEMA NAME FROM INFORMATION SCHEMA SCHEMATA   1  0  AS found

User · Answer

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

User · Answer

Another php solution  but with PDO    lt  php try       pdo   new PDO  mysql host localhost dbname dbname    root    password       echo  table dbname exists        catch  PDOException  e       die  dbname not found

User · Answer

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    gt  dev null 2 gt  amp 1  lt  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

User · Answer

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    lt  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- gt 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- gt select db  en                                  static   link- gt query  CREATE DATABASE  en       die   false   die   true                  if   die                      return false                  static   link- gt select db  en                              else                               static   link- gt 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- gt 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

User · Answer

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

User · Answer

Rails Code   ruby-1 9 2-p290  099  gt  ActiveRecord  Base connection execute  USE INFORMATION SCHEMA    ruby-1 9 2-p290  099  gt  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    gt     entos development     ruby-1 9 2-p290  100  gt  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    gt          entos development exist   entos development1 not exist

User · Answer

Using bash   if     mysql -u USER  -p PASSWORD  -se USE  DATABASE NAME   2 gt  amp 1            then     echo  DATABASE NAME exist else     echo  DATABASE NAME doesn t exist fi

User · Answer

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

User · Answer

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

[mysql] How to check if mysql database exists

Examples related to mysql

Examples related to database

Examples related to exists