How to delete a table using the MySQL DROP TABLE statement
This tutorial explains how to drop a table in MySQL using the DROP TABLE statement, which allows you to drop one or more tables in MySQL with a single SQL query.
Learn more:
MySQL DROP TABLE Syntax
To drop a table, use the following DROP TABLE statement:
DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] ...
Remarks
- The DROP TABLE statement permanently removes a table and its data from a given database.
- MySQL allows you to drop one or more tables with a single DROP TABLE statement
- The IF EXISTS option: If specified, MySQL will not issue an error when you attempt to drop a table that does not exist
- The TEMPORARY option is useful to prevent the accidental deletion of non-temporary tables
Examples
Example 1: Dropping a table with the IF EXISTS option
The following statement will drop an existing table named "customers" from the given database:
DROP TABLE IF EXISTS customers;
If the "customers" table exists, MySQL will delete it from the database. The IF EXISTS option is specified, so MySQL will not issue an error even if the "customers" table does not exist.
If you want to display a warning message, use the following statement:
DROP TABLE IF EXIST customers;
SHOW WARNINGS;
If the "customers" table does not exist, MySQL will issue an error like the following:
| Level | Code | Message |
| Note | 1051 | Unknown table 'mysql_tutorial.customers' |
Example 2: The IF EXISTS option is omitted
The following statement deletes an existing "customers" table from the specified database:
DROP TABLE customers;
Here, we have omitted the IF EXISTS option, and if the customer table does not exist, MySQL will issue the following error:
[SQL] DROP TABLE customers;
[Err] 1051 - Unknown table 'mysql_tutorial.customers'
Example 3: Delete multiple tables
The following statement deletes three tables named "customers," "suppliers," and "employees" from the database:
DROP TABLE IF EXISTS customers, suppliers, employees;
Note that MySQL only drops the tables that exist in the database and ignores the tables that do not exist.
Summary
In this tutorial, you have learned how to use the DROP TABLE statement to drop one or more tables in MySQL from a given database. If you try to delete a table that does not exist in the database, MySQL will issue an error. To avoid this, specify the IF EXISTS option in the SQL query.