Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to delete a given database along with all of its tables and data. The addition of the IF EXISTS flag suppresses an error message if the database does not already exist. You must have the DROP privilege for the database to be able to delete it. Here is an example of this statement’s use:

DROP DATABASE IF EXISTS test;
Query OK, 6 rows affected (0.42 sec)

The number of tables deleted is returned in the rows affected count. If the database doesn’t exist or if there are other files in the database’s filesystem directory, an error message will be displayed. The tables will be deleted if other files exist, but the foreign file and the directory for the database won’t be removed. They will have to be deleted manually at the command line using a filesystem command such as rm in Unix or del in Windows. Here’s an example in which a foreign file is found in the database directory when dropping a database:

DROP DATABASE IF EXISTS test;

ERROR 1010 (HY000): 
Error dropping database (can't rmdir './test/', errno: 17)

SHOW TABLES FROM test;
Empty set (0.00 sec)

SHOW DATABASES LIKE 'test';

+-----------------+
| Database (test) |
+-----------------+
| test            | 
+-----------------+

In this example, we attempt to drop the database, but we are unsuccessful because of a foreign file located in the database’s directory at the filesystem level. The tables are all dropped as indicated from the results of the SHOW TABLES statement, but the database remains. After manually deleting the foreign file, we run the DROP DATABASE statement again:

DROP DATABASE IF EXISTS test;
Query OK, 0 rows affected (0.43 sec)

DROP DATABASE test;

ERROR 1008 (HY000): Can't drop database 'test'; 
database doesn't exist

This time the statement is successful, as indicated by our extra attempt without the IF EXISTS flag. No tables are dropped by the second attempt because they are all deleted on the first attempt, so the number of rows affected is 0.

If a database is dropped, any user privileges specific to the database (e.g., privileges listed in the db table of the mysql database) are not automatically deleted. Therefore, if a database is later created with the same name, those user privileges will apply to the new database, which is a potential security risk.