Dropping a database table is a common occurrence for any database administrator. Whether you are just cleaning up unused data or optimizing performance, you will eventually encounter a scenario where you need to drop a single or multiple database tables.
NOTE: This is a very DESCTRUCTIVE action. All the commands and scripts provided in this tutorial will permanently delete data stored at any specified table.
To following along with this post, ensure you have the following
- Basic knowledge of SQL
- MySQL Server Installed (Version 8.0 recommended)
MySQL Sequential Drop
The most basic way of dropping multiple tables in MySQL is using multiple
DROP TABLE statements for each table you wish to delete.
For example, if you wish to remove
table3, you can run multiple queries as shown
DROP TABLE table1; DROP TABLE table2; DROP TABLE table3;
In this case, we use a set of sequential
DROP statements to remove the tables.
MySQL Drop Table Command - One Liner
MySQL also provides us with the ability to specify more than one table in the
DROP TABLE command. This allows us to quickly delete multiple tables in a single command.
The command syntax is as shown:
DROP TABLE table1, table2, table3;
MySQL Pattern Based Drop
We can also take advantage of MySQL scripting feature to create a simple statement that drops multiple tables that match a specific criteria.
For example, suppose we wish to drop multiple tables that start with the name
SELECT Concat('DROP TABLE ', table_name, ';') FROM information_schema.tables WHERE table_schema = 'database_name' AND table_name LIKE 'default%';
Ensure to replace
'database_name' with the name of your target database. This will generate a list of DROP TABLE commands. You can then execute these commands to drop the tables.
You can also combine the resulting statements above to quickly and automatically execute the generated statements using the
SET @tables = NULL; SELECT GROUP_CONCAT(table_name SEPARATOR ', ') INTO @tables FROM information_schema.tables WHERE table_schema = 'database_name' AND table_name LIKE 'default%'; SET @dropTableStatement = CONCAT('DROP TABLE ', @tables); PREPARE stmt FROM @dropTableStatement; EXECUTE stmt; DEALLOCATE PREPARE stmt;
The script above performs the following actions:
- We use the
GROUP_CONCATfunction to concatenate the names of all tables that start with
defaultinto a single string, separated by commas.
- Next, we create a
DROP TABLEstatement using this string.
- Finally, we call the
EXECUTEto execute this statement.
In this tutorial, we explored three main methods you can use to drop multiple tables in MySQL. Like any other programming language, you can take advantage of the provided MySQL features to come up with alternative ways of accomplishing the same thing.