Skip navigation links
12.1.18 DROP TRIGGER Syntax »
Section Navigation      [Toggle]

12.1.17. 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 nonexisting 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.4.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 nonexistent table when using IF EXISTS. See Section 12.4.5.37, “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 session 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

Posted by Malcolm Ke (kexianbin@diyism.com) on September 23 2008 6:32am[Delete] [Edit]

Guy, "drop tables like" could be realized thus:
delimiter $$
create procedure drop_tables_like(pattern varchar(255), db varchar(255))
begin
select @str_sql:=concat('drop table ', group_concat(table_name))
from information_schema.tables
where table_schema=db and table_name like pattern;

prepare stmt from @str_sql;
execute stmt;
drop prepare stmt;
end$$

call drop_tables_like('kw_%', 'db_1')$$

drop procedure if exists drop_tables_like$$
delimiter ;

Posted by Morgan Christiansson on November 22 2008 1:40am[Delete] [Edit]

It's also possible to do DROP TABLE LIKE 'tableprefix%' using the free software maatkit tool from http://www.maatkit.org/

Example:
mk-find --dblike "dbname" --tbllike "tableprefix%" --exec_plus "DROP TABLE %s"

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

To drop ALL tables in your 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 you need to empty a 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.