In this post, we will discuss 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

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 accidentaly delete non temporary tables.
  • IF EXISTS esures that no error occurs for nonexisting tables.
  • table_name is the name of the table.
  • The RESTRICT and CASCADE keywords does 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.

Table List before deleting a table in MySQL

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

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.

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.

Table List after deleting a table in MySQL

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

Example 4:

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.

If you have any doubts or suggestions then please comment below.

Leave a Reply