DROP [UNDO] TABLESPACE tablespace_name

该语句用于删除先前使用 CREATE TABLESPACE 创建的表空间。它由 NDB 和 InnoDB 存储引擎支持。This statement drops a tablespace that was previously created using CREATE TABLESPACE. It is supported by the NDB and InnoDB storage engines.

必须指定 UNDO 关键字来删除撤销表空间。只有使用 CREATE UNDO TABLESPACE 语法创建的撤销表空间才能被删除。撤销表空间必须处于 empty 状态才能被删除。更多信息请参见 第 17.6.3.4 节，“撤销表空间”。The UNDO keyword must be specified to drop an undo tablespace. Only undo tablespaces created using CREATE UNDO TABLESPACE syntax can be dropped. An undo tablespace must be in an empty state before it can be dropped. For more information, see Section 17.6.3.4, “Undo Tablespaces”.

tablespace_name 是 MySQL 中区分大小写的标识符。tablespace_name is a case-sensitive identifier in MySQL.

对于 InnoDB 常规表空间，在执行 DROP TABLESPACE 操作之前，必须先删除表空间中的所有表。如果表空间不为空，DROP TABLESPACE 会返回错误。For an InnoDB general tablespace, 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.

要删除的 NDB 表空间不能包含任何数据文件；换句话说，要删除 NDB 表空间，必须先使用 ALTER TABLESPACE ... DROP DATAFILE 删除其每个数据文件。An NDB tablespace to be dropped must not contain any data files; in other words, before you can drop an NDB tablespace, you must first drop each of its data files using ALTER TABLESPACE ... DROP DATAFILE.

注意Notes

InnoDB 示例InnoDB Examples

此示例演示如何删除一个 InnoDB 通用表空间。通用表空间 ts1 随着单个表一起创建。在删除表空间之前，必须先删除该表。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;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;

mysql> DROP TABLE t1;

mysql> DROP TABLESPACE ts1;

此示例演示删除一个撤销表空间。撤销表空间必须处于 empty 状态才能删除。有关更多信息，请参见 第 17.6.3.4 节，“撤销表空间”。This example demonstrates dropping an undo tablespace. An undo tablespace must be in an empty state before it can be dropped. For more information, see Section 17.6.3.4, “Undo Tablespaces”.

mysql> DROP UNDO TABLESPACE undo_003;

NDB 示例NDB Example

此示例演示如何在首先创建表空间后，删除一个具有名为 mydata-1.dat 的数据文件的 NDB 表空间 myts，并假设已存在名为 mylg 的日志文件组（参见 第 15.1.16 节，“CREATE LOGFILE GROUP 语句”）。This example shows how to drop an NDB tablespace myts having a data file named mydata-1.dat after first creating the tablespace, and assumes the existence of a log file group named mylg (see Section 15.1.16, “CREATE LOGFILE GROUP Statement”).

mysql> CREATE TABLESPACE myts
    ->     ADD DATAFILE 'mydata-1.dat'
    ->     USE LOGFILE GROUP mylg
    ->     ENGINE=NDB;

必须使用 ALTER TABLESPACE 移除表空间中的所有数据文件，如下所示，然后才能删除表空间：You must remove all data files from the tablespace using ALTER TABLESPACE, as shown here, before it can be dropped:

mysql> ALTER TABLESPACE myts
    ->     DROP DATAFILE 'mydata-1.dat';

mysql> DROP TABLESPACE myts;