How to Drop a Table in MySQL

June 30, 2020

Introduction

The DROP TABLE statement allows a table to be removed from a MySQL database. This statement deletes the entire structure as well as the content of the table.

The tutorial explores DROP statement commands and shows options to drop tables from MySQL databases.

mysql tutorial on drop, or delete, a table from a MySQL database.

Prerequisites

DROP TABLE MySQL Command Syntax

To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows:

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

Let’s break down the syntax:

  • The DROP TABLE statement deletes a table and its rows permanently.
  • The [TEMPORARY] option ensures you remove temporary tables only.
  • The [IF EXISTS] option drops a table only if it exists.
  • The [RESTRICT] option will be available in future iterations of MySQL. It ensures that a parent row is not deleted if a child row is referencing a value in said parent row.
  • The [CASCADE] option ensures that when you delete a row, all rows in related tables that reference that row are deleted as well. This option will be available in future iterations of MySQL.

Note: When deleting a MySQL table, all user privileges associated with that table remain. If you create a new table with the same name as the deleted one, the existing user privileges will be assigned. Consider whether that impacts your security.

Use the DROP Statement to Remove a Table

To permanently remove a table, enter the following statement within the MySQL shell:

DROP TABLE table1;

Replace table1 with the name of the table you want to delete. The output confirms that the table has been removed.

MySQL DROP statment removes table from database.

Use DROP to Remove Only Existing Tables

MySQL generates an error if you try to drop a table that does not exist. This can be an issue when including the DROP statement in a predefined script.

Error informing us that the table does not exist.

To check MySQL if a table exists first:

DROP TABLE IF EXISTS table1;

The IF EXISTS option generates a warning if table1 does not exist.

Warning if the table to be droped does not exist.

Using this option prevents a script from getting stuck on an error. Display the warning by entering:

SHOW WARNINGS;
The SSOW WARNINGS statement tells us that table1 does not exist in the database.

How to DROP Multiple Tables

To drop multiple tables with a single DROP statement:

DROP TABLE IF EXISTS table1, table2, table3;

The IF EXISTS option shows one warning as table1 does not exist.

Dropping multiple tables with one statment. Warning due to the fact that table1 does not exist.

Note: Consider identifying duplicate values in MySQL databases and then deleting them to improve data efficiency.

How to DROP a Temporary Table

Temporary tables are used to generate and store a data set shortly before using it.

For example, you might decide to store the results of a SELECT statement with multiple JOIN statements in a temporary table. Next, query the temporary table with ease.

To remove temporary tables without risking losing regular tables, use the TEMPORARY option:

DROP TEMPORARY TABLE IF EXISTS table4;
drop Statement in MySQL to drop only temporary tables

How to DROP Tables Based on Character Strings

MySQL does not have a built-in command to drop tables that match a string of characters. Instead, use a script to help perform this task.

1. Define the database and string of characters you want to filter:

set @schema = 'tableselection';
set @string = 'table%';

Replace tableselection with the name of your database. Replace table% with the string of characters you want to select and delete. Make sure to leave the % wildcard at the end.

2. Create a MySQL statement that selects all of the tables that match the string of characters:

SELECT CONCAT ('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';')
INTO @droptool
FROM information_schema.tables
WHERE @schema = database()
AND table_name LIKE @string;

This code selects all tables with the table% characters specified from the information_schema table. It then concatenates them and executes a DROP statement against the group.

3. Create a selection from the results of this code:

SELECT @droptool;

4. Display the contents of the command:

PREPARE stmt FROM @droptool;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The code in the section below is presented in its entirety for ease of use:

set @schema = 'tableselection';
set @string = 'table%';
 
SELECT CONCAT ('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';')
INTO @droptool
FROM information_schema.tables
WHERE @schema = database()
AND table_name LIKE @string;
 
SELECT @droptool;
 
PREPARE stmt FROM @droptool;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Note: If you are interested in how to create index in new table, read our guide How to Create or Add an Index in MySQL With Examples.

Conclusion

You have learned how to use Drop Table to remove tables from a MySQL database. The options presented in this tutorial provide you with full control.

Additionally, you are now able to create code that helps find and delete tables based on character strings. Learn about the most important MySQL commands and how to use them in our MySQL commands cheat sheet article with a downloadable PDF.

Was this article helpful?
YesNo
Vladimir Kaplarevic
Vladimir is a resident Tech Writer at phoenixNAP. He has more than 7 years of experience in implementing e-commerce and online payment solutions with various global IT services providers. His articles aim to instill a passion for innovative technologies in others by providing practical advice and using an engaging writing style.
Next you should read
How to Remove Duplicate Rows in MySQL
March 5, 2020

When adding data to a MySQL database, you may end up with duplicate rows. This article shows you how to find...
Read more
How to Improve MySQL Performance With Tuning
January 15, 2020

The performance of MySQL databases is an essential factor in the optimal operation of your server. Make sure...
Read more
How to Import and Export MySQL Databases in Linux
April 25, 2019

MySQL is a versatile application. It can be used for something as simple as a product database, or as complex...
Read more
How to Back Up & Restore a MySQL Database
October 22, 2018

A MySQL database has been lost, and you're scrambling to restore a copy from your last backup. This guide...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.