13.1.28 DROP TABLE Syntax

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.

For a partitioned table, DROP TABLE
permanently removes the table definition, all of its partitions,
and all of the data which was stored in those partitions. It also
removes the partitioning definition (.par)
file associated with the dropped table.

User Comments

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 insteadof individually dropping each table (somewhat but not exactly similar to CASCADE CONSTRAINTS option in Oracle).

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)?