13.1.28 DROP TABLE Syntax

DROP TABLE removes one or more
tables. You must have the DROP
privilege for each table.

Be careful with this statement! It removes
the table definition and all table data. For a partitioned table,
it permanently removes the table definition, all its partitions,
and all data stored in those partitions. It also removes the
partitioning definition (.par) file
associated with the dropped table.

When a table is dropped, privileges granted specifically for the
table are not automatically dropped. They
must be dropped manually. See Section 13.7.1.4, “GRANT Syntax”.

If any tables named in the argument list do not exist, the
statement returns an error indicating by name which nonexisting
tables it was unable to drop, but also drops all tables in the
list that do exist.

IF EXISTS can also be useful for dropping
tables in unusual circumstances under which there is an
.frm file but no table managed by the storage
engine. (For example, if an abnormal server exit occurs after
removal of the table from the storage engine but before
.frm file removal.)

The TEMPORARY keyword has the following
effects:

The statement drops only TEMPORARY tables.

The statement does not cause an implicit commit.

No access rights are checked. A TEMPORARY
table is visible only with 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.

The RESTRICT and CASCADE
keywords do nothing. They are permitted to make porting easier
from other database systems.

linux example for removing all the archive tables in a database called piwik to simulate the "drop table like 'piwik_archive_%'" example above

Posted by
Brendan Byrd
on
July 22, 2015

It should be noted that the TEMPORARY keyword is also allowed on slave servers, especially non-existent temporary tables. For example, if 'tempo' is an existing temp table for a non-admin user on a slave:

> DROP TABLE IF EXISTS tempo;Query OK, 0 rows affected (0.00 sec)

> DROP TABLE IF EXISTS tempo;ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement