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
DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name_2] ... [RESTRICT | CASCADE]
Note: Syntax within square brackets is optional.
TEMPORARYensures 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 EXISTSesures that no error occurs for nonexisting tables.
table_nameis the name of the table.
CASCADEkeywords 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.
Example 1: Trying to delete a non-existing table without
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
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%'
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.
If you have any doubts or suggestions then please comment below.