Drop or delete a table in MySQL

To remove a table from a MySQL database, and remove all of its data, use the following SQL command.

The command will conditionally delete a table if it exists. The ‘if exists’ syntax is optional, but is useful when using SQL commands in a file, such as when importing data, as it will not display errors.

drop table if exists recipes;

The command can be issued from any SQL source including an application with database connectivity (i.e., PHP, Java, etc.) or directly from mysql command (see Connect to a MySQL server using the mysql command for more information).

How to drop a table with MySQL

This post shows the SQL query for dropping a table with MySQL and also some screenshots showing how ot drop a table using phpMyAdmin.

SQL Query

If the table to be dropped is called "test", run the following query to delete the table. This is irrecoverable and there is no prompting to see if you really do want to drop the table.

DROP TABLE test;

Using phpMyAdmin

If you have phpMyAdmin installed you can easily drop a table. It pays to be very careful when dropping a table with phpMyAdmin as it is trivially easy to drop the entire database by mistake. I have personally done this a couple of times myself.

Once you are looking at the table itself in phpMyAdmin the tabbed navigation will show a "Drop" entry in red on the far right. I've circled this in red in the example screenshot below.

Note the green arrow; this highlights what you need to look out for before clicking the "Drop" link. If a table name is listed then it's a table that will be dropped.

If there's no table name specified, as shown in the next screenshot below, then clicking "Drop" will drop the entire database. So be very careful.

After clicking "Drop" a Javascript prompt will pop up as shown below to ensure you really do want to delete the table.

Click "OK" and the table will be dropped.

Related posts:

• MySQL: SQL to drop a column from a table
• Drop multiple MySQL tables

Drop multiple MySQL tables

Last week I looked at how to drop a table with MySQL showing how to do this with a query and then using the phpMyAdmin web based interface. This time I will show how to drop multiple tables with a single query and then how to do the same with phpMyAdmin.

SQL Query

Dropping multiple tables is the same as dropping a single table; additional tables are added to the DROP TABLE query in comma separated fashion. Note that running the following query to delete the example categories, products and orders table is irrecoverable and there is no prompting.

DROP TABLE categories, orders, products;

Using phpMyAdmin

To drop multiple tables in phpMyAdmin select the page which shows all the tables for a database. Tick the boxes for the tables you want to delete, and select "Drop" from the "With selected:" drop down box. This is shown in the screenshot below where all three tables have been checked: categories, orders and products.

The page will submit and a confirmation page will display asking if you really do want to drop those tables. Click "Yes" and they will be dropped.

Related posts:

• MySQL: SQL to drop a column from a table
• How to drop a table with MySQL