MySQL Drop Table Tutorial and Examples

In this article, we described how to drop an existing table using the DROP TABLE statement.

When we don’t need a table, we can drop this table. In MySQL, the DROP TABLE statement is used to drop a table.

Note: The DROP TABLE statement will permanently drop the table and the data in the table, please proceed with caution.

MySQL DROP TABLE syntax

The following is the basic syntax of the DROP TABLE statement:

DROP TABLE [IF EXISTS]
  table_name [, table_name] ...

Here:

  • The DROP TABLE keyword is followed by the name of the table to drop. If you want to drop multiple tables, separate the table names with commas.

  • The IF EXISTS option to avoid errors when dropping a table that does not exist. It is optional.

    When there are non-existing tables in the table list to be dropped:

    • No error will be reported for non-existing tables if has IF EXISTS option. This statement drops existing tables and gives hints for non-existing tables.
    • Without the IF EXISTSoption, the statement fails with an error indicating that a table that does not exist cannot be removed. This statement does not drop any tables.

  • DROP TABLE will drop the definition of the table and the data in the table, as well as triggers on the table.

  • You need DROP permission.

MySQL DROP TABLE Examples

To demonstrate the usage of DROP TABLE, we need to create several tables in the testdb database. Please follow the steps below:

  1. Log in to the MySQL database as the root user:

    mysql -u root -p

    Enter the password of the root user.

    Note: You can also log in as any other user with appropriate database privileges.

  2. Select the testdb database using the following statement:

    USE testdb;

    If you haven’t created a testdb database, create it first with the CREATE DATABASE statement:

    CREATE DATABASE testdb;

  3. Create a sample table using the CREATE TABLE statement.

    CREATE TABLE test1 (
  id INT NOT NULL AUTO_INCREMENT,
  v VARCHAR(45) NULL,
  PRIMARY KEY (id));

    Then, create several tables like the same definition using the following statements.

    CREATE TABLE test2 LIKE test1;
CREATE TABLE test3 LIKE test1;
CREATE TABLE test4 LIKE test1;
CREATE TABLE test5 LIKE test1;

  4. View all tables in the database using the following statement.

    SHOW TABLES;

    +------------------+
| Tables_in_testdb |
+------------------+
| test1            |
| test2            |
| test3            |
| test4            |
| test5            |
| user             |
| user_hobby       |
+------------------+
7 rows in set (0.00 sec)

Now that the demo table is ready, let’s look at a few concrete examples.

Drop a table using MySQL DROP TABLE statement

We drop the test5 table:

DROP TABLE test5;

Query OK, 0 rows affected (0.01 sec)

This shows that the test5 table has been dropped.

We verify the result of this DROP TABLE operation.

SHOW TABLES;

+------------------+
| Tables_in_testdb |
+------------------+
| test1            |
| test2            |
| test3            |
| test4            |
| user             |
| user_hobby       |
+------------------+
6 rows in set (0.00 sec)

Drop multiple tables using MySQL DROP TABLE statement

We drop the two tables test3 and test4 using the following statement:

DROP TABLE test3, test4;

Query OK, 0 rows affected (0.01 sec)

This shows that the two tables test3 and test4 have been successfully dropped.

We verify the result of this DROP TABLE operation.

SHOW TABLES;

+------------------+
| Tables_in_testdb |
+------------------+
| test1            |
| test2            |
| user             |
| user_hobby       |
+------------------+
4 rows in set (0.00 sec)

Drop a non-existing table using MySQL DROP TABLE statement

We try to drop a non-existing table named test5 using the follow statement:

DROP TABLE test5;

ERROR 1051 (42S02): Unknown table 'testdb.test5'

Because the test5 table does not exist, so this statement returns an error message.

Now we use the IF EXISTS option in the DROP TABLE statement:

DROP TABLE IF EXISTS test5;

Query OK, 0 rows affected, 1 warning (0.00 sec)

Here we used the IF EXISTS option and the statement did not return an error. However, there is one caveat to be noted 1 warning. We can see specific considerations using the following statement:

SHOW WARNINGS;

+-------+------+------------------------------+
| Level | Code | Message                      |
+-------+------+------------------------------+
| Note  | 1051 | Unknown table 'testdb.test5' |
+-------+------+------------------------------+
1 row in set (0.00 sec)

Here tell us the specific precautions are: Unknown table 'testdb.test5'.

Let’s look at another example:

DROP TABLE test2, test5;

ERROR 1051 (42S02): Unknown table 'testdb.test5'

Like the previous example, because the test5 table doesn’t exist. So this statement returns an error message.

Now we use the IF EXISTS option to drop:

DROP TABLE IF EXISTS test2, test5;

Query OK, 0 rows affected, 1 warning (0.00 sec)

Here we used the IF EXISTS option and the statement did not return an error. The result of the execution of this statement is: the test2 table has been dropped.

We verify the result of this DROP TABLE operation.

SHOW TABLES;

+------------------+
| Tables_in_testdb |
+------------------+
| test1            |
| user             |
| user_hobby       |
+------------------+
3 rows in set (0.00 sec)

It can be seen that DROP TABLE with the IF EXISTS option, it will ignore the non-existing table and drop the existing table.

Conclusion

In this article, we discussed using the usage of the DROP TABLE statement. The main points of this article are as follows:

  • The DROP TABLE keyword is followed by the name of the table to drop. If you want to drop multiple tables, separate the table names with commas.
  • The IF EXISTS option to avoid errors when dropping a table that does not exist. It is optional.

Always be aware that the DROP TABLE statement physically drops the table and the data in the table. This operation cannot be undone, please make a back up of the table before dropping.