Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.4Mb
PDF (A4) - 33.4Mb
PDF (RPM) - 31.3Mb
HTML Download (TGZ) - 7.9Mb
HTML Download (Zip) - 8.0Mb
HTML Download (RPM) - 6.8Mb
Man Pages (TGZ) - 145.7Kb
Man Pages (Zip) - 206.7Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


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

Pre-General Availability Draft: 2017-09-22

13.1.27 DROP TABLESPACE Syntax

DROP TABLESPACE tablespace_name
   [ENGINE [=] engine_name]

This statement is used to drop an InnoDB general tablespace created using CREATE TABLESPACE syntax. (see Section 13.1.17, “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.

Note

The ENGINE clause is deprecated and will be removed in a future release. The tablespace storage engine is known by the data dictionary, making the ENGINE clause obsolete.

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.