How to Delete a Table in MySQL?

Summary: In this tutorial, we will leanr how to delete an existing table in MySQL using the MySQL shell or command line.

The general syntax to delete any existing table in MySQL is:

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name_2] ...
[RESTRICT | CASCADE]

Note: Syntax within square brackets is optional.

where,

• TEMPORARY ensures that statement drops only TEMPORARY tables and does not cause an implicit commit. It is recommended if you don’t want to accidentally delete non-temporary tables.
• IF EXISTS esures that no error occurs for non-existing tables.
• table_name is the name of the table.
• The RESTRICT and CASCADE keywords do nothing special. They are used to make porting easier from other database systems.

This single DROP TABLE statement can be used to delete multiple tables.

Along with deleting the table definition, the DROP TABLE statement also deletes all table data. Also, it is important to note that deleting a table also deletes any triggers for the table.

Let’s see some examples of DROP TABLE statement in MySQL.

MySQL DROP TABLE Examples

Here is the list of tables in ‘pencildb’ database.

Example 1: Trying to delete a non-existing table without IF EXISTS.

DROP TABLE my_table;

ERROR 1051 (42S02): Unknown table ‘pencildb.my_table’

We get an error from MySQL, that the specified table is unknown.

Example 2: Let’s try deleting the same non-existing table with IF EXISTS.

DROP TABLE IF EXISTS my_table;

Query OK, 0 rows affected, 1 warning (0.08 sec)

This time we do not get any error instead we get a warning from MySQL.

Example 3: Deleting an existing table.

DROP TABLE IF EXISTS pp_posts;

This time, the table has been deleted and we do not get any warning from MySQL. Also, if we look at the table list then we can notice that the ‘pp_post’ table is not there.

It is important to note that we can also delete tables based on a pattern using the following syntax

DROP TABLE LIKE '%pattern%'

Example 4:

DROP TABLE LIKE '%post%'

Using this statement will delete all the tables having ‘post’ in their table name. This could also delete tables that you initially did not intend to delete. Hence it is not the recommended way to delete tables in MySQL.

That’s all for this tutorial. I hope now you can easily delete tables in MySQL using the DROP TABLE statement.