{"id":1167,"date":"2010-01-20T17:47:36","date_gmt":"2010-01-21T01:47:36","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=1167"},"modified":"2023-12-29T08:18:59","modified_gmt":"2023-12-29T15:18:59","slug":"mysql-drop-table","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-drop-table\/","title":{"rendered":"MySQL DROP TABLE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL DROP TABLE<\/code> statement to drop a table from the database.<\/p>\n\n\n\n

MySQL DROP TABLE statement syntax<\/h2>\n\n\n\n

To remove existing tables, you use the MySQL DROP TABLE<\/code> statement.<\/p>\n\n\n\n

Here is the basic syntax of the DROP TABLE<\/code> statement:<\/p>\n\n\n

DROP<\/span> [TEMPORARY<\/span>] TABLE<\/span> [IF<\/span> EXISTS<\/span>] table_name [, table_name] ...\n[RESTRICT | CASCADE<\/span>]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The DROP TABLE<\/code> statement removes a table and its data permanently from the database. In MySQL, you can also remove multiple tables using a single DROP TABLE<\/code> statement, each table is separated by a comma (,).<\/p>\n\n\n\n
The TEMPORARY<\/code> option allows you to remove temporary tables<\/a> only. It ensures that you do not accidentally remove non-temporary tables.<\/p>\n\n\n\n
The IF EXISTS<\/code> option conditionally drop a table only if it exists. If you drop a non-existing table with the IF EXISTS<\/code> option, MySQL generates a NOTE, which can be retrieved using the SHOW WARNINGS<\/a><\/code> statement.<\/p>\n\n\n\n
Note that the DROP TABLE<\/code> statement only drops tables. It doesn’t remove specific user privileges associated with the tables. Therefore, if you create a table with the same name as the dropped one, MySQL will apply the existing privileges to the new table, which may pose a security risk.<\/p>\n\n\n\n
The RESTRICT<\/code> and CASCADE<\/code>  options are reserved for the future versions of MySQL.<\/p>\n\n\n\n
To execute the DROP TABLE<\/code> statement, you must have DROP<\/code> privileges for the table that you want to remove.<\/p>\n\n\n\n
MySQL DROP TABLE examples<\/h2>\n\n\n\n
Let’s take some examples of using the DROP TABLE<\/code> statement.<\/p>\n\n\n\n
1) Using MySQL DROP TABLE to drop a single table example<\/h3>\n\n\n\n
First, create a table named insurances<\/code> for testing purposes:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> insurances (\n    id<\/span> INT<\/span> AUTO_INCREMENT,\n    title VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n    effectiveDate DATE<\/span> NOT<\/span> NULL<\/span>,\n    duration<\/span> INT<\/span> NOT<\/span> NULL<\/span>,\n    amount DEC<\/span>(10<\/span> , 2<\/span> ) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(id<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, use the DROP TABLE<\/code> to delete the insurances<\/code> table:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> insurances;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using MySQL DROP TABLE to drop multiple tables<\/h3>\n\n\n\n
First, create two tables named CarAccessories<\/code> and CarGadgets<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> CarAccessories (\n    id<\/span> INT<\/span> AUTO_INCREMENT,\n    name<\/span> VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n    price DEC<\/span>(10<\/span> , 2<\/span> ) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(id<\/span>)\n);\n\nCREATE<\/span> TABLE<\/span> CarGadgets (\n    id<\/span> INT<\/span> AUTO_INCREMENT,\n    name<\/span> VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n    price DEC<\/span>(10<\/span> , 2<\/span> ) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(id<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, use the DROP TABLE<\/code> statement to drop the two tables:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> CarAccessories, CarGadgets;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Using MySQL DROP TABLE to drop a non-existing table<\/h3>\n\n\n\n
This statement attempts to drop a non-existing table:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> aliens;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued the following error:<\/p>\n\n\n
Error Code: 1051. Unknown table 'classicmodels.aliens'\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, if you use the IF EXISTS<\/code> option in the DROP TABLE<\/code> statement:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> aliens;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued a warning instead:<\/p>\n\n\n
0 row(s) affected, 1 warning(s): 1051 Unknown table 'classicmodels.aliens'\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To show the warning, you can use the SHOW WARNINGS<\/code> statement:<\/p>\n\n\n
SHOW<\/span> WARNINGS<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL DROP TABLE based on a pattern<\/h2>\n\n\n\n
Suppose that you have many tables whose names start with test<\/code> in your database and you want to remove all of them using a single DROP TABLE<\/code> statement.<\/p>\n\n\n\n
Unfortunately, MySQL does not have the DROP TABLE LIKE<\/code> statement that can remove tables based on pattern matching:<\/p>\n\n\n
DROP TABLE LIKE '%pattern%'<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
However, there are some workarounds. We will discuss one of them here for your reference.<\/p>\n\n\n\n
First, create three tables test1<\/code>,test2<\/code>, test4<\/code> for demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test1(\n  id<\/span> INT<\/span> AUTO_INCREMENT,\n  PRIMARY KEY<\/span>(id<\/span>)\n);\n\nCREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> test2 LIKE<\/span> test1;\nCREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> test3 LIKE<\/span> test1;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Suppose you want to remove all test*<\/code> tables.<\/p>\n\n\n\n
Second, declare two variables that accept database schema and a pattern that you want to the tables to match:<\/p>\n\n\n
--<\/span> set<\/span> table<\/span> schema<\/span> and<\/span> pattern<\/span> matching<\/span> for<\/span> tables<\/span>\nSET<\/span> @schema<\/span> = 'classicmodels'<\/span>;\nSET<\/span> @pattern<\/span> = 'test%'<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, construct a dynamic DROP TABLE<\/code> statement:<\/p>\n\n\n
-- construct dynamic sql (DROP TABLE tbl1, tbl2...;)<\/span>\nSELECT<\/span> CONCAT<\/span>('DROP TABLE '<\/span>,GROUP_CONCAT<\/span>(CONCAT<\/span>(@schema<\/span>,'.'<\/span>,table_name)),';'<\/span>)\nINTO<\/span> @droplike\nFROM<\/span> information_schema.tables\nWHERE<\/span> @schema<\/span> = database<\/span>()\nAND<\/span> table_name LIKE<\/span> @pattern;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query instructs MySQL to go to the information_schema\u00a0 table, which contains information on all tables in all databases, and concatenates all tables in the database @schema<\/code> ( classicmodels<\/code> ) <\/em>that matches the pattern  @pattern<\/code> ( test%<\/code> ) <\/em>with the prefix DROP TABLE<\/code> . <\/em>The GROUP_CONCAT<\/a><\/code> function creates a comma-separated list of tables.<\/p>\n\n\n\n
Fourth, display the dynamic SQL to verify if it works correctly:<\/p>\n\n\n
-- display the dynamic sql statement<\/span>\nSELECT<\/span> @droplike;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mysql<\/figure>\n\n\n\n
As you can see, the output is what we expected.<\/p>\n\n\n\n
Fifth, execute the statement using a prepared statement <\/a> as shown in the following query:<\/p>\n\n\n
-- execute dynamic sql<\/span>\nPREPARE<\/span> stmt FROM<\/span> @droplike;\nEXECUTE<\/span> stmt;\nDEALLOCATE<\/span> PREPARE<\/span> stmt;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Putting it all together.<\/p>\n\n\n
-- set table schema and pattern matching for tables<\/span>\nSET<\/span> @schema<\/span> = 'classicmodels'<\/span>;\nSET<\/span> @pattern = 'test%'<\/span>;\n\n-- build dynamic sql (DROP TABLE tbl1, tbl2...;)<\/span>\nSELECT<\/span> CONCAT<\/span>('DROP TABLE '<\/span>,GROUP_CONCAT<\/span>(CONCAT<\/span>(@schema<\/span>,'.'<\/span>,table_name)),';'<\/span>)\nINTO<\/span> @droplike\nFROM<\/span> information_schema.tables\nWHERE<\/span> @schema<\/span> = database<\/span>()\nAND<\/span> table_name LIKE<\/span> @pattern;\n\n-- display the dynamic sql statement<\/span>\nSELECT<\/span> @droplike;\n\n-- execute dynamic sql<\/span>\nPREPARE<\/span> stmt FROM<\/span> @droplike;\nEXECUTE<\/span> stmt;\nDEALLOCATE<\/span> PREPARE<\/span> stmt;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
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. <\/p>\n\n\n\n
All you need to do is replace the pattern<\/em> and the database schema<\/em> in @pattern<\/code> and @schema<\/code> variables. <\/p>\n\n\n\n
In this tutorial, you have learned how to use the MySQL DROP TABLE<\/code> statement to remove existing tables from a database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
If you often have to deal with this task, you can develop a stored procedure<\/a> based on the script and reuse this stored procedure.<\/p>\n\n\n\n