Working with Tables

Dropping Tables

Dropping a table is much easier than creating it because you don't have to specify anything about its contents. You just have to name it:

DROP TABLE tbl_name;

MySQL extends the DROP TABLE statement in some useful ways. First, you can drop several tables by specifying them all on the same statement:

DROP TABLE tbl_name1, tbl_name2, ... ;

Second, if you're not sure whether or not a table exists, but you want to drop it if it does, you can add IF EXISTS to the statement. This causes MySQL not to complain or issue an error if the table or tables named in the statement don't exist:

DROP TABLE IF EXISTS tbl_name;

IF EXISTS is particularly useful in scripts that you use with the mysql client. By default, mysql exits when an error occurs, and it is an error to try to remove a table that doesn't exist. For example, you might have a setup script that creates tables that you use as the basis for further processing in other scripts.

In this situation, you want to make sure the setup script has a clean slate when it begins. If you use a regular DROP TABLE at the beginning of the script, it would fail the first time because the tables have never been created. If you use IF EXISTS, there is no problem. If the tables are there, they are dropped; if not, the script continues anyway.