13.1.21 CREATE TABLESPACE Statement语句

CREATE [UNDO] TABLESPACE tablespace_name
InnoDB and NDB:
    [ADD DATAFILE 'file_name']
    [AUTOEXTEND_SIZE [=] value]
InnoDB only:
    [FILE_BLOCK_SIZE = value]
    [ENCRYPTION [=] {'Y' | 'N'}]
NDB only:
    USE LOGFILE GROUP logfile_group
    [EXTENT_SIZE [=] extent_size]
    [INITIAL_SIZE [=] initial_size]
    [MAX_SIZE [=] max_size]
    [NODEGROUP [=] nodegroup_id]
    [WAIT]
    [COMMENT [=] 'string']
InnoDB and NDB:
    [ENGINE [=] engine_name]
Reserved for future use:
    [ENGINE_ATTRIBUTE [=] 'string']

This statement is used to create a tablespace. 此语句用于创建表空间。The precise syntax and semantics depend on the storage engine used. 精确的语法和语义取决于所使用的存储引擎。In standard MySQL releases, this is always an InnoDB tablespace. 在标准MySQL版本中，这始终是一个InnoDB表空间。MySQL NDB Cluster also supports tablespaces using the NDB storage engine.MySQL NDB集群还支持使用NDB存储引擎的表空间。

Considerations for InnoDBInnoDB的考虑因素

CREATE TABLESPACE syntax is used to create general tablespaces or undo tablespaces. CREATE TABLESPACE语法用于创建常规表空间或撤消表空间。The UNDO keyword, introduced in MySQL 8.0.14, must be specified to create an undo tablespace.必须指定MySQL 8.0.14中引入的UNDO关键字才能创建撤销表空间。

A general tablespace is a shared tablespace. 通用表空间是共享表空间。It can hold multiple tables, and supports all table row formats. 它可以容纳多个表，并支持所有表行格式。General tablespaces can be created in a location relative to or independent of the data directory.可以在相对于或独立于数据目录的位置创建常规表空间。

After creating an InnoDB general tablespace, use CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name to add tables to the tablespace. 创建InnoDB通用表空间后，使用CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name或ALTER TABLE tbl_name TABLESPACE [=] tablespace_name来将表添加到表空间。For more information, see Section 15.6.3.3, “General Tablespaces”.有关更多信息，请参阅第15.6.3.3节，“通用表空间”。

Undo tablespaces contain undo logs. Undo tablespaces can be created in a chosen location by specifying a fully qualified data file path. 撤消表空间包含撤消日志。通过指定完全限定的数据文件路径，可以在选定位置创建撤消表空间。For more information, see Section 15.6.3.4, “Undo Tablespaces”.有关更多信息，请参阅第15.6.3.4节，“撤消表空间”。

Considerations for NDB ClusterNDB集群的考虑因素

This statement is used to create a tablespace, which can contain one or more data files, providing storage space for NDB Cluster Disk Data tables (see Section 23.5.10, “NDB Cluster Disk Data Tables”). 此语句用于创建一个表空间，该表空间可以包含一个或多个数据文件，为NDB群集磁盘数据表提供存储空间（请参阅第23.5.10节，“NDB群集磁盘数据表”）。One data file is created and added to the tablespace using this statement. 使用此语句创建一个数据文件并将其添加到表空间。Additional data files may be added to the tablespace by using the ALTER TABLESPACE statement (see Section 13.1.10, “ALTER TABLESPACE Statement”).可以使用ALTER TABLESPACE语句将其他数据文件添加到表空间（请参阅第13.1.10节，“ALTER TABLESPACE语句”）。

Note注意

All NDB Cluster Disk Data objects share the same namespace. 所有NDB群集磁盘数据对象共享相同的命名空间。This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). 这意味着每个磁盘数据对象必须具有唯一的名称（而不仅仅是给定类型的每个磁盘数据对象）。For example, you cannot have a tablespace and a log file group with the same name, or a tablespace and a data file with the same name.例如，不能有同名的表空间和日志文件组，也不能有同名的表空间和数据文件。

A log file group of one or more UNDO log files must be assigned to the tablespace to be created with the USE LOGFILE GROUP clause. 必须将一个或多个UNDO日志文件的日志文件组分配给使用USE LOGFILE GROUP子句创建的表空间。logfile_group must be an existing log file group created with CREATE LOGFILE GROUP (see Section 13.1.16, “CREATE LOGFILE GROUP Statement”). logfile_group必须是使用CREATE LOGFILE GROUP创建的现有日志文件组（请参阅第13.1.16节，“CREATE LOGFILE GROUP语句”）。Multiple tablespaces may use the same log file group for UNDO logging.多个表空间可以使用相同的日志文件组来撤消日志记录。

When setting EXTENT_SIZE or INITIAL_SIZE, you may optionally follow the number with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf. 设置EXTENT_SIZE或INITIAL_SIZE时，您可以选择在数字后面加一个一个字母的缩写，表示数量级，类似于my.cnf中使用的缩写。Generally, this is one of the letters M (for megabytes) or G (for gigabytes).通常，这是字母M（表示兆字节）或G（表示千兆字节）中的一个。

INITIAL_SIZE and EXTENT_SIZE are subject to rounding as follows:INITIAL_SIZE和EXTENT_SIZE以四舍五入为准，如下所示：

Note注意

NDB reserves 4% of a tablespace for data node restart operations. NDB为数据节点重启操作保留了4%的表空间。This reserved space cannot be used for data storage.此保留空间不能用于数据存储。

The rounding just described is done explicitly, and a warning is issued by the MySQL Server when any such rounding is performed. 刚才描述的舍入是显式完成的，当执行任何此类舍入时，MySQL服务器会发出警告。The rounded values are also used by the NDB kernel for calculating INFORMATION_SCHEMA.FILES column values and other purposes. NDB内核还使用舍入值来计算INFORMATION_SCHEMA.FILES列值和其他用途。However, to avoid an unexpected result, we suggest that you always use whole multiples of 32K in specifying these options.但是，为了避免意外结果，我们建议您在指定这些选项时始终使用32K的整数倍。

When CREATE TABLESPACE is used with ENGINE [=] NDB, a tablespace and associated data file are created on each Cluster data node. 当CREATE TABLESPACE与ENGINE [=] NDB一起使用时，会在每个集群数据节点上创建一个表空间和关联的数据文件。You can verify that the data files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table. 您可以通过查询INFORMATION_SCHEMA.FILES表来验证数据文件是否已创建，并获取有关这些文件的信息。(See the example later in this section.)（请参见本节后面的示例。）

(See Section 26.3.15, “The INFORMATION_SCHEMA FILES Table”.)（请参阅第26.3.15节，“信息模式文件表”。）

Options选项

Notes

InnoDB ExamplesInnoDB示例

This example demonstrates creating a general tablespace and adding three uncompressed tables of different row formats.此示例演示如何创建常规表空间并添加三个不同行格式的未压缩表。

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=REDUNDANT;

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;

mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;

This example demonstrates creating a general tablespace and adding a compressed table. 此示例演示如何创建常规表空间和添加压缩表。The example assumes a default innodb_page_size value of 16K. 该示例假定默认的innodb_page_size值为16K。The FILE_BLOCK_SIZE of 8192 requires that the compressed table have a KEY_BLOCK_SIZE of 8.FILE_BLOCK_SIZE8192要求压缩表的KEY_BLOCK_SIZE为8。

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

This example demonstrates creating a general tablespace without specifying the ADD DATAFILE clause, which is optional as of MySQL 8.0.14.此示例演示如何在不指定ADD DATAFILE子句的情况下创建常规表空间，该子句在MySQL 8.0.14中是可选的。

mysql> CREATE TABLESPACE `ts3` ENGINE=INNODB;

This example demonstrates creating an undo tablespace.此示例演示如何创建撤消表空间。

mysql> CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';

NDB ExampleNDB示例

Suppose that you wish to create an NDB Cluster Disk Data tablespace named myts using a datafile named mydata-1.dat. 假设您希望使用名为mydata-1.dat的数据文件创建名为myts的NDB群集磁盘数据表空间。An NDB tablespace always requires the use of a log file group consisting of one or more undo log files. NDB表空间始终需要使用由一个或多个撤消日志文件组成的日志文件组。For this example, we first create a log file group named mylg that contains one undo long file named myundo-1.dat, using the CREATE LOGFILE GROUP statement shown here:对于本例，我们首先创建一个名为mylg的日志文件组，其中包含一个名为myundo-1.dat的撤销长文件，使用如下所示的CREATE LOGFILE GROUP语句：

mysql> CREATE LOGFILE GROUP myg1
    ->     ADD UNDOFILE 'myundo-1.dat'
    ->     ENGINE=NDB;
Query OK, 0 rows affected (3.29 sec)

Now you can create the tablespace previously described using the following statement:现在可以使用以下语句创建前面描述的表空间：

mysql> CREATE TABLESPACE myts
    ->     ADD DATAFILE 'mydata-1.dat'
    ->     USE LOGFILE GROUP mylg
    ->     ENGINE=NDB;
Query OK, 0 rows affected (2.98 sec)

You can now create a Disk Data table using a CREATE TABLE statement with the TABLESPACE and STORAGE DISK options, similar to what is shown here:现在，您可以使用CREATE TABLE语句创建磁盘数据表，该语句带有TABLESPACE和STORAGE DISK选项，如下所示：

mysql> CREATE TABLE mytable (
    ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     lname VARCHAR(50) NOT NULL,
    ->     fname VARCHAR(50) NOT NULL,
    ->     dob DATE NOT NULL,
    ->     joined DATE NOT NULL,
    ->     INDEX(last_name, first_name)
    -> )
    ->     TABLESPACE myts STORAGE DISK
    ->     ENGINE=NDB;
Query OK, 0 rows affected (1.41 sec)

It is important to note that only the dob and joined columns from mytable are actually stored on disk, due to the fact that the id, lname, and fname columns are all indexed.需要注意的是，由于id、lname和fname列都已编制索引，因此实际上只有mytable中的dob和joined列存储在磁盘上。

As mentioned previously, when CREATE TABLESPACE is used with ENGINE [=] NDB, a tablespace and associated data file are created on each NDB Cluster data node. 如前所述，当CREATE TABLESPACE与ENGINE [=] NDB一起使用时，会在每个NDB集群数据节点上创建一个表空间和关联的数据文件。You can verify that the data files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table, as shown here:您可以通过查询INFORMATION_SCHEMA.FILES表来验证数据文件是否已创建，并获取有关这些文件的信息，如下所示：

mysql> SELECT FILE_NAME, FILE_TYPE, LOGFILE_GROUP_NAME, STATUS, EXTRA
    ->     FROM INFORMATION_SCHEMA.FILES
    ->     WHERE TABLESPACE_NAME = 'myts';

+--------------+------------+--------------------+--------+----------------+
| file_name    | file_type  | logfile_group_name | status | extra          |
+--------------+------------+--------------------+--------+----------------+
| mydata-1.dat | DATAFILE   | mylg               | NORMAL | CLUSTER_NODE=5 |
| mydata-1.dat | DATAFILE   | mylg               | NORMAL | CLUSTER_NODE=6 |
| NULL         | TABLESPACE | mylg               | NORMAL | NULL           |
+--------------+------------+--------------------+--------+----------------+
3 rows in set (0.01 sec)

For additional information and examples, see Section 23.5.10.1, “NDB Cluster Disk Data Objects”.有关更多信息和示例，请参阅第23.5.10.1节，“NDB群集磁盘数据对象”。