Skip navigation links
12.1.7 DROP INDEX Syntax »
Section Navigation      [Toggle]

12.1.6. DROP DATABASE Syntax

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database.

Important

When a database is dropped, user privileges on the database are not automatically dropped. See Section 12.4.1.2, “GRANT Syntax”.

In MySQL 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring if the database does not exist.

As of MySQL 4.1.1, if the default database is dropped, the default database is unset (the DATABASE() function returns NULL).

If you use DROP DATABASE on a symbolically linked database, both the link and the original database are deleted.

As of MySQL 4.1.2, DROP DATABASE returns the number of tables that were removed. This corresponds to the number of .frm files removed.

The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation:

  • All files with the following extensions.

    .BAK .DAT .HSH .ISD
    .ISM .MRG .MYD .MYI
    .db .frm .ibd .ndb

  • All subdirectories with names that consist of two hex digits 00-ff. These are subdirectories used for RAID tables. (These directories are not removed in versions of MySQL after 4.1, where support for RAID tables is removed. You should convert any existing RAID tables and remove these directories manually before upgrading to later MySQL versions.)

  • The db.opt file, if it exists.

If other files or directories remain in the database directory after MySQL removes those just listed, the database directory cannot be removed. In this case, you must remove any remaining files or directories manually and issue the DROP DATABASE statement again.

You can also drop databases with mysqladmin. See Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”.


User Comments

Posted by Christopher Schultz on February 26 2004 11:49am[Delete] [Edit]

Please note that when dropping databases containing InnoDB tables in version 4.0.18 and beyond, you might get a FK violation.

It sounds bizarre, but DROP DATABASE seemingly drops each table, first, in arbitrary order. Since the ordering is arbitrary, it may drop them in the 'wrong' order. In this case, you'll get the message:

error: 'Cannot delete or update a parent row: a foreign key constraint fails'

... and half of your tables will be gone, the other half remaining. You can repeatedly execute "DROP DATABASE" commands and each time (at least for me), MySQL gets closer to an empty database, at which point, the database itself is dropped.

Here's a mailing list thread covering the phenomenon and a few responses from someone who sounds like they're in a position to know about these things.

http://lists.mysql.com/bugs/15255

I marked this comment as an "Explanation" because I cannot mark it as a bug :(. I feel that there needs to be a way to drop a database without manually dropping all the tables inside it, first. MySQL should at least drop the tables in the reverse order in which they were added to the database, to avoid such behavior.

-chris

Posted by [name withheld] on April 5 2004 11:22pm[Delete] [Edit]

Adding
SET FOREIGN_KEY_CHECKS = 0;
before dropping a database fixes the foreign key constraint problem (as introduced in 4.0.18) for me

Posted by Justin Vassallo on May 10 2005 1:02pm[Delete] [Edit]

Do note that when a db is dropped, the corresponding db structure is deleted. However, the data itself, which resides in a common file 'ibdata', is not deleted. In some cases, this might constitute a security hazard.
This is not a bug but i think that it would be a good idea that 'drop database' actually removes the data from ibdata.

Posted by Jonathan Watt on May 28 2009 9:43pm[Delete] [Edit]

To drop all tables without actually dropping the database (fill out the first line as appropriate):

MYSQL="mysql -h HOST -u USERNAME -pPASSWORD -D DB_NAME"
$MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; drop table `" $1 "`;"}' | $MYSQL
unset MYSQL

This can be useful if all you really need to do is empty the database in order to restore a backup made by mysqldump, but you couldn't use --add-drop-database because you don't have CREATE DATABASE privileges on the command line (e.g. you're on shared hosting). mysqldump adds DROP TABLE by default, but if tables may have been added or renamed since the time of your backup (e.g. by some sort of update process that you're trying to revert from), failing to drop those tables will likely cause serious headaches later on.

Of course this raises the question of why MySQL doesn't support "DROP TABLE *;" (in which case mysqldump could just insert that)?

Add your own comment.