Using MySQL DROP TABLE To Remove Existing Tables?

Summary: in this tutorial, we will show you how to remove existing tables using the MySQL DROP TABLE statement.

MySQL DROP TABLE statement syntax

To remove existing tables, you use the MySQL DROP TABLE statement. The syntax of the DROP TABLE is as follows:

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

The DROP TABLE statement removes a table and its data permanently from the database. In MySQL, you can also remove multiple tables using a single DROP TABLE statement, each table is separated by a comma (,).

The TEMPORARY flag allows you to remove temporary tables only. It is very convenient to ensure that you do not accidentally remove non-temporary tables.

The IF EXISTS addition helps you prevent from removing non-existent tables. When you use IF EXISTS addition, MySQL generates a NOTE, which can be retrieved by using the SHOW WARNINGstatement. It is important to note that the DROP TABLE statement removes all existing tables and issues an error message or a NOTE when you have a non-existent table in the list.

As mentioned above, the DROP TABLE statement only removes table and its data. However, it does not remove specific user privileges associated with the table. Therefore if a table with the same name is re-created after that, the existing privileges will apply to the new table, which may pose a security risk.

The RESTRICT and CASCADE  flags are reserved for the future versions of MySQL.

Last but not least, you must have DROP privileges for the table that you want to remove.

MySQL DROP TABLE example

We are going to remove the tasks table that we created in the previous tutorial in the creating tables using CREATE TABLE statement tutorial. In addition, we remove a non-existent table to practice with the SHOW WARNING statement. The statement to remove the tasks table and a non-existent table with the name nonexistent_table is as follows:

1	DROP TABLE IF EXISTS tasks, nonexistent_table;

If you check the database, you will see that the tasks table was removed. You can check the NOTE, which is generated by MySQL because of non-existent table, by using the SHOW WARNING statement as follows:

1	SHOW WARNINGS;

MySQL DROP TABLE with LIKE

Image you have a lot of tables whose names start with test in your database and you want to save time by removing all of them using a single DROP TABLE statement. Unfortunately, MySQL does not provide the DROP TABLE LIKE statement that can remove tables based on pattern matching like the following:

1	DROP TABLE LIKE '%pattern%'

However, there are some workarounds. We will discuss one of them here for your reference.

Let’s start creating test* tables for the sake of demonstration.

1 2 3 4 5 6 7 8

CREATE TABLE IF NOT EXISTS test1(   id int(11) NOT NULL AUTO_INCREMENT,   PRIMARY KEY(id) );   CREATE TABLE IF NOT EXISTS test2 LIKE test1; CREATE TABLE IF NOT EXISTS test3 LIKE test1; CREATE TABLE IF NOT EXISTS test4 LIKE test1;

We’ve created four tables named test1 , test2 , test3  and test4  with the same table structure.

Suppose you want to remove all test* tables at a time, you can follow the steps below:

First, you declare two variables that accept database schema and a pattern that you want to the tables to match:

1 2 3

-- set table schema and pattern matching for tables SET @schema = 'classicmodels'; SET @pattern = 'test%';

Next, you need to build a dynamic DROP TABLE statement:

1 2 3 4 5 6

-- build dynamic sql (DROP TABLE tbl1, tbl2...;) SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';') INTO @droplike FROM information_schema.tables WHERE @schema = database() AND table_name LIKE @pattern;

Basically, the query instructs MySQL to go to the information_schema  table , which contains data on all tables in all databases, and to concatenate all tables in the database @schema ( classicmodels ) that matches the pattern @pattern ( test% ) with the prefix DROP TABLE . The GROUP_CONCAT functioncreates a comma-separated list of tables.

Then, we can display the dynamic SQL to verify if it works correctly:

1 2	-- display the dynamic sql statement SELECT @droplike;

We can see that it works as expected.

After that, we can execute the statement using prepared statement in MySQL as follows:

1 2 3 4

-- execute dynamic sql PREPARE stmt FROM @droplike; EXECUTE stmt; DEALLOCATE PREPARE stmt;

For more information on MySQL prepared statement, check it out the MySQL prepared statementtutorial.

Putting it all together.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- set table schema and pattern matching for tables SET @schema = 'classicmodels'; SET @pattern = 'test%';   -- build dynamic sql (DROP TABLE tbl1, tbl2...;) SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';') INTO @droplike FROM information_schema.tables WHERE @schema = database() AND table_name LIKE @pattern;   -- display the dynamic sql statement SELECT @droplike;   -- execute dynamic sql PREPARE stmt FROM @dropcmd; EXECUTE stmt; DEALLOCATE PREPARE stmt;

So if you want to drop multiple tables that have a specific pattern in a database, you just use the script above to save time. All you need to do is replacing the pattern and the database schema in @pattern and @schema variables. If you often have to deal with this task, you can always develop a stored procedurebased on the script and reuse this stored procedure.

In this tutorial, we’ve shown you how to use the DROP TABLE statement to remove existing tables in a particular database. We also discussed about a workaround that allows you to use the DROP TABLEstatement to remove tables based on pattern matching.