Testing Whether a Database Exists

9.12.1 Problem

You want to know whether a database exists.

9.12.2 Solution

Use SHOW DATABASES to see if the table is listed.

9.12.3 Discussion

SHOW DATABASES can be used to determine whether a database exists if you add a LIKE clause that matches the database name:

SHOW DATABASES LIKE 'db_name';

The following Perl function shows how to do this:

sub database_exists
{
my ($dbh, $db_name) = @_;

 $db_name =~ s/([%_])/\$1/g; # escape any special characters
 return ($dbh->selectrow_array ("SHOW DATABASES LIKE '$db_name'"));
}

The function returns false if the database exists but the server was started with the --skip-show-database option and you don't have MySQL root user privileges.