The Ultimate Guide to High Availability with MariaDB

DROP TABLE

You are viewing an old version of this article. View the current version here.

Syntax

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

Contents

  1. Syntax
  2. Description
  3. DROP TABLE in replication
  4. Examples
  5. Notes
  6. See also

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 connection is using the table, a metadata lock is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.

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.

Since MariaDB 5.5.27, the comment before the tablenames (that /*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.

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

Starting from MariaDB 10.0.8, DROP TABLE on the master is treated on the slave as DROP TABLE IF EXISTS. You can change that by setting slave-ddl-exec-mode to STRICT.

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 mysqldump to work with sequences.

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.