Syntax
------

DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/]
  tbl_name [, tbl_name] ...
  [WAIT n|NOWAIT]
  [RESTRICT | CASCADE]

Description
-----------

DROP TABLE removes one or more tables. You must have the DROP privilege for
each table. All table data and the table definition are removed, as well as
triggers associated to the table, so be careful with this statement! If any of
the tables named in the argument list do not exist, MariaDB returns an error
indicating by name which non-existing tables it was unable to drop, but it
also drops all of the tables in the list that do exist.

Important: When a table is dropped, user privileges on the table are not
automatically dropped. See GRANT.

If another thread is using the table in an explicit transaction or an
autocommit transaction, then the thread acquires a metadata lock (MDL) on the
table. The DROP TABLE statement will wait in the "Waiting for table metadata
lock" thread state until the MDL is released. MDLs are released in the
following cases:

* If an MDL is acquired in an explicit transaction, then the MDL will be
released when the transaction ends.
* If an MDL is acquired in an autocommit transaction, then the MDL will be
released when the statement ends.
* Transactional and non-transactional tables are handled the same.

Note that for a partitioned table, DROP TABLE permanently removes the table
definition, all of its partitions, and all of the data which was stored in
those partitions. It also removes the partitioning definition (.par) file
associated with the dropped table.

For each referenced table, DROP TABLE drops a temporary table with that name,
if it exists. If it does not exist, and the TEMPORARY keyword is not used, it
drops a non-temporary table with the same name, if it exists. The TEMPORARY
keyword ensures that a non-temporary table will not accidentally be dropped.

Use IF EXISTS to prevent an error from occurring for tables that do not exist.
A NOTE is generated for each non-existent table when using IF EXISTS. See SHOW
WARNINGS.

If a foreign key references this table, the table cannot be dropped. In this
case, it is necessary to drop the foreign key first.

RESTRICT and CASCADE are allowed to make porting from other database systems
easier. In MariaDB, they do nothing.

The comment before the table names (/*COMMENT TO SAVE*/) is stored in the
binary log. That feature can be used by replication tools to send their
internal messages.

It is possible to specify table names as db_name.tab_name. This is useful to
delete tables from multiple databases with one statement. See Identifier
Qualifiers for details.

The DROP privilege is required to use DROP TABLE on non-temporary tables. For
temporary tables, no privilege is required, because such tables are only
visible for the current session.

Note: DROP TABLE automatically commits the current active transaction, unless
you use the TEMPORARY keyword.

MariaDB starting with 10.5.4
----------------------------
From MariaDB 10.5.4, DROP TABLE reliably deletes table remnants inside a
storage engine even if the .frm file is missing. Before then, a missing .frm
file would result in the statement failing.

MariaDB starting with 10.3.1
----------------------------

WAIT/NOWAIT
-----------

Set the lock wait timeout. See WAIT and NOWAIT.

DROP TABLE in replication
-------------------------

DROP TABLE has the following characteristics in replication:

* DROP TABLE IF EXISTS are always logged.
* DROP TABLE without IF EXISTS for tables that don't exist are not written to
the binary log.
* Dropping of TEMPORARY tables are prefixed in the log with TEMPORARY. These
drops are only logged when running statement or mixed mode replication.
* One DROP TABLE statement can be logged with up to 3 different DROP
statements:
DROP TEMPORARY TABLE list_of_non_transactional_temporary_tables
DROP TEMPORARY TABLE list_of_transactional_temporary_tables
DROP TABLE list_of_normal_tables

DROP TABLE on the primary is treated on the replica as DROP TABLE IF EXISTS.
You can change that by setting slave-ddl-exec-mode to STRICT.

Dropping an Internal #sql-... Table
-----------------------------------

From MariaDB 10.6, DROP TABLE is atomic and the following does not apply.
Until MariaDB 10.5, if the mariadbd/mysqld process is killed during an ALTER
TABLE you may find a table named #sql-... in your data directory. In MariaDB
10.3, InnoDB tables with this prefix will be deleted automatically during
startup. From MariaDB 10.4, these temporary tables will always be deleted
automatically.

If you want to delete one of these tables explicitly you can do so by using
the following syntax:

DROP TABLE `#mysql50##sql-...`;

When running an ALTER TABLE…ALGORITHM=INPLACE that rebuilds the table, InnoDB
will create an internal #sql-ib table. Until MariaDB 10.3.2, for these tables,
the .frm file will be called something else. In order to drop such a table
after a server crash, you must rename the #sql*.frm file to match the
#sql-ib*.ibd file.

From MariaDB 10.3.3, the same name as the .frm file is used for the
intermediate copy of the table. The #sql-ib names are used by TRUNCATE and
delayed DROP.

From MariaDB 10.2.19 and MariaDB 10.3.10, the #sql-ib tables will be deleted
automatically.

Dropping All Tables in a Database
---------------------------------

The best way to drop all tables in a database is by executing DROP DATABASE,
which will drop the database itself, and all tables in it.

However, if you want to drop all tables in the database, but you also want to
keep the database itself and any other non-table objects in it, then you would
need to execute DROP TABLE to drop each individual table. You can construct
these DROP TABLE commands by querying the TABLES table in the
information_schema database. For example:

SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

Atomic DROP TABLE
-----------------

MariaDB starting with 10.6.1
----------------------------
From MariaDB 10.6, DROP TABLE for a single table is atomic (MDEV-25180) for
most engines, including InnoDB, MyRocks, MyISAM and Aria.

This means that if there is a crash (server down or power outage) during DROP
TABLE, all tables that have been processed so far will be completely dropped,
including related trigger files and status entries, and the binary log will
include a DROP TABLE statement for the dropped tables. Tables for which the
drop had not started will be left intact.

In older MariaDB versions, there was a small chance that, during a server
crash happening in the middle of DROP TABLE, some storage engines that were
using multiple storage files, like MyISAM, could have only a part of its
internal files dropped.

In MariaDB 10.5, DROP TABLE was extended to be able to delete a table that was
only partly dropped (MDEV-11412) as explained above. Atomic DROP TABLE is the
final piece to make DROP TABLE fully reliable.

Dropping multiple tables is crash-safe.

See Atomic DDL for more information.

Examples
--------

DROP TABLE Employees, Customers;

Notes
-----

Beware that DROP TABLE can drop both tables and sequences. This is mainly done
to allow old tools like mariadb-dump (previously mysqldump) to work with
sequences.

URL: https://mariadb.com/kb/en/drop-table/