DROP TABLE

Use this MySQL statement to delete a MySQL table from a database, including its data.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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

Explanation

Use this statement to delete a table from a database, including its data. You can delete multiple tables in the same statement by naming them in a comma-separated list. If some tables given exist and other don't, the ones that exist will be deleted and an error message will be generated for the non-existent ones. The addition of the IF EXISTS flag prevents the error message from being displayed if a table doesn't exist. Instead, a NOTE is generated and not displayed, but can retrieved with the SHOW WARNINGS statement. If the TEMPORARY flag is given, only temporary tables matching the table names given will be deleted. This statement will cause a commit of the current transaction, except when the TEMPORARY flag is used.

The DROP privilege is required for this statement. This privilege isn't checked when the TEMPORARY flag is used because the statement will only apply to temporary tables, and they are visible and usable only by the user of the current session who created them.

The RESTRICT and CASCADE flags are for future versions and are related to compatibility with other systems.

If a table is dropped, any specific user privileges to the table (e.g., privileges listed in the tables_priv table of the mysql database) are not automatically deleted. Therefore, if a table is later created with the same name, those user privileges will apply to the new table, a potential security risk.

Examples

DROP TABLE IF EXISTS repairs, clientss_old;
Query OK, 0 rows affected (0.00 sec)

SHOW WARNINGS;

+-------+------+------------------------------+
| Level | Code | Message                      |
+-------+------+------------------------------+
| Note  | 1051 | Unknown table 'clientss_old' |
+-------+------+------------------------------+

In this example, we tried to delete both the repairs and the clients_old tables, but misspelled clients_old. Because the IF EXISTS flag was included, the statement didn't return an error message. Starting with version 4.1 of MySQL, a note is created that can be retrieved using the SHOW WARNINGS statement, as shown in this example. Notice that the number of tables deleted is not returned, although the repairs table was deleted.