MySQL DROP TABLE

Sometimes you may need to delete table from database in MySQL. You can easily do this using MySQL DROP TABLE command. Here’s how to drop table in MySQL.

 

How to Drop Table in MySQL

Here are the steps to drop table in MySQL. We will use the MySQL DROP TABLE statement to remove existing tables in a database.

Here’s the syntax of DROP TABLE statement:

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

DROP TABLE statement can be used for removing one or more database tables.

TEMPORARY keyword will delete only temporary tables and prevent non-temporary tables’ deletion.

IF EXISTS keyword will drop a table only if it exists.

Please note, in order to be able to delete a MySQL table, you must have DROP TABLE privileges.

Bonus Read : How to get record from today in MySQL

 

MySQL DROP TABLE to delete single table

Let’s say you have the following table.

mysql> create table orders(order_date date, sale int);

Here’s the MySQL DROP TABLE statement to delete this table.

mysql> DROP TABLE orders;

 

If you try to delete a non-existing table, then you will get a warning:

mysql> drop table orders;
ERROR 1051 (42S02): Unknown table 'sample.orders'

Bonus Read : How to Get First Record in Each Group in MySQL

 

MySQL Drop multiple tables

If you have multiple tables table1, table2, table3 in same database, then here’s the MySQL DROP TABLE statement to delete them

mysql> DROP TABLE table1, table2, table3;

 

Bonus Read : How to Create MySQL View

 

MySQL DROP TABLE matching pattern

If you have multiple database tables and you want to delete tables matching a particular pattern such as “order” then you cannot directly use MySQL DROP TABLE statement for it. For example, the following command WILL NOT WORK.

mysql> DROP TABLE like 'order%';

 

So we will need to use a work around. Let’s say you have tables orders1, orders2, order3 in your database.

mysql> create table orders1(order_date date, sale int);

mysql> create table orders2(order_date date, sale int);

mysql> create table orders3(order_date date, sale int);

 

First we create a dynamic SQL query string with table names in it

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

The above query instructs MySQL to fetch table names matching your required pattern @pattern(e.g order%) from information_schema table, that contains a list of all table names in your database @schema (e.g sample), and concatenate them with ‘DROP TABLE’. We use GROUP_CONCAT to create a comma-separated list of table names.

We store this query in @droplike variable.

Next, we set values to @pattern and @schema variables, and run prepared statements created from @droplike variable.

mysql> SET @schema = 'sample';

mysql> SET @pattern = 'order%';

mysql> SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';')
     INTO @droplike
     FROM information_schema.tables
     WHERE @schema = database()
     AND table_name LIKE @pattern;

mysql> select @droplike;
+-------------------------------------------------------------+
| @droplike                                                   |
+-------------------------------------------------------------+
| DROP TABLE sample.orders1,sample.orders2,sample.orders3;    |
+-------------------------------------------------------------+

PREPARE stmt FROM @droplike;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 

MySQL DROP All Tables

Similarly, you can drop all tables in your database. Just remove AND table_name LIKE @pattern from select statement in @droplike above

SET @schema = 'sample';
SELECT CONCAT('DROP TABLE IF EXISTS',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';')
     INTO @droplike
     FROM information_schema.tables
     WHERE @schema = database();

 

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!