Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 32.8Mb
PDF (A4) - 32.8Mb
PDF (RPM) - 30.8Mb
HTML Download (TGZ) - 7.8Mb
HTML Download (Zip) - 7.9Mb
HTML Download (RPM) - 6.7Mb
Man Pages (TGZ) - 143.5Kb
Man Pages (Zip) - 203.6Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


MySQL 8.0 Reference Manual  /  ...  /  DROP TABLESPACE Syntax

Pre-General Availability Draft: 2017-07-24

13.1.26 DROP TABLESPACE Syntax

DROP TABLESPACE tablespace_name
   [ENGINE [=] engine_name]

This statement is used to drop an InnoDB general tablespace that was created using CREATE TABLESPACE (see Section 13.1.16, “CREATE TABLESPACE Syntax”).

All tables must be dropped from the tablespace prior to a DROP TABLESPACE operation. If the tablespace is not empty, DROP TABLESPACE returns an error.

tablespace_name is a case-sensitive identifier in MySQL.

ENGINE: Defines the storage engine that uses the tablespace, where engine_name is the name of the storage engine. Currently, only the InnoDB storage engine is supported. You do not need to specify ENGINE = InnoDB if InnoDB is defined as the default storage engine (default_storage_engine=InnoDB).

DROP TABLESPACE is supported with InnoDB. In earlier releases, DROP TABLESPACE supports NDB, which is the MySQL Cluster storage engine. DROP TABLESPACE will support NDB in MySQL 5.7 when MySQL Cluster is branched from the MyQL 5.7 code base. The latest version of MySQL Cluster is based on MySQL 5.6.

Notes

  • A general InnoDB tablespace is not deleted automatically when the last table in the tablespace is dropped. The tablespace must be dropped explicitly using DROP TABLESPACE tablespace_name.

  • A DROP DATABASE operation can drop tables that belong to a general tablespace but it cannot drop the tablespace, even if the operation drops all tables that belong to the tablespace. The tablespace must be dropped explicitly using DROP TABLESPACE tablespace_name.

  • Similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace .ibd data file which can only be used for new InnoDB data. Space is not released back to the operating system as it is for file-per-table tablespaces.

Example

This example demonstrates how to drop an InnoDB general tablespace. The general tablespace ts1 is created with a single table. Before dropping the tablespace, the table must be dropped.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts10 Engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLESPACE ts1;
Query OK, 0 rows affected (0.01 sec)

User Comments
Sign Up Login You must be logged in to post a comment.