Skip navigation links

Additional languages
12.1.9 RENAME TABLE Syntax »
Section Navigation      [Toggle]

12.1.8. DROP TABLE Syntax

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed, so be careful with this statement! If any of the tables named in the argument list do not exist, MySQL returns an error indicating by name which non-existing tables it was unable to drop, but it also drops all of the tables in the list that do exist.

Important

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

Use IF EXISTS to prevent an error from occurring for tables that do not exist. A NOTE is generated for each non-existent table when using IF EXISTS. See Section 12.5.4.33, “SHOW WARNINGS Syntax”.

RESTRICT and CASCADE are allowed to make porting easier. In MySQL 5.0, they do nothing.

Note

DROP TABLE automatically commits the current active transaction, unless you use the TEMPORARY keyword.

The TEMPORARY keyword has the following effects:

  • The statement drops only TEMPORARY tables.

  • The statement does not end an ongoing transaction.

  • No access rights are checked. (A TEMPORARY table is visible only to the client that created it, so no check is necessary.)

Using TEMPORARY is a good way to ensure that you do not accidentally drop a non-TEMPORARY table.


User Comments

Posted by Pankaj Khanna on March 26 2006 7:52am[Delete] [Edit]

An example to drop tables having parent-child relationship is to drop the child tables first and then the parent tables. This can be very helpful when we drop tables and then recreate them in a script.

Example:
Let's say table A has two children B and C. Then we can use the following syntax to drop all tables.

DROP TABLE IF EXISTS B,C,A;

This can be placed in the beginning of the script instead
of individually dropping each table (somewhat but not exactly similar to CASCADE CONSTRAINTS option in Oracle).

Posted by David Andersen on August 31 2007 9:54pm[Delete] [Edit]

Just found an excellent library, which allows dropping multiple tables using syntax similar to "drop table like 'sales%'". The library can also do some multi-purpose Dynamic SQL.

You can read about the specific syntax at: http://datacharmer.blogspot.com/2005/12/mysql-5-general-purpose-routine.html

It can be downloaded from https://sourceforge.net/project/showfiles.php?group_id=166288

Add your own comment.