DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/] tbl_name [, tbl_name] ... [WAIT n|NOWAIT] [RESTRICT | CASCADE]
DROP TABLE removes one or more tables. You must have the
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
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.
IF EXISTS to prevent an error from occurring for tables that do not
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.
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
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.
DROP TABLE automatically commits the current active transaction,
unless you use the
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.
DROP TABLE in replication
DROP TABLE has the following characteristics in replication:
DROP TABLE IF EXISTSare always logged.
IF EXISTSfor tables that don't exist are not written to the binary log.
- Dropping of
TEMPORARYtables are prefixed in the log with
TEMPORARY. These drops are only logged when running statement or mixed mode replication.
DROP TABLEstatement can be logged with up to 3 different
DROP TEMPORARY TABLE list_of_non_transactional_temporary_tables
DROP TEMPORARY TABLE list_of_transactional_temporary_tables
DROP TABLE list_of_normal_tables
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 de 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. 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
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.0
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.
See MDEV-17567 "Atomic DDL" for more information.
DROP TABLE Employees, Customers;