Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 nonexistent 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 be 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 apply only 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 for 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:

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 try to delete both the repairs and the clients_old tables, but we misspell clients_old. Because the IF EXISTS flag is included, the statement doesn’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 is deleted.