MySQL :: MySQL 8.4 参考手册 :: 15.1.21 创建表空间语句

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 15 条 SQL 语句 / 15.1 数据定义语句 / 15.1.21 创建表空间语句

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

15.1.21 CREATE TABLESPACE 语句15.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']

此语句用于创建表空间。具体的语法和语义取决于所用的存储引擎。在标准的 MySQL 版本中，这总是 InnoDB 表空间。MySQL NDB 集群还支持 NDB 存储引擎。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 NDB Cluster also supports tablespaces using the NDB storage engine.

InnoDB 的注意事项Considerations for InnoDB

CREATE TABLESPACE 语法用于创建通用表空间或撤销表空间。创建撤销表空间必须指定 UNDO 关键字。CREATE TABLESPACE syntax is used to create general tablespaces or undo tablespaces. The UNDO keyword must be specified to create an undo tablespace.

通用表空间是共享表空间。它可以存储多个表，并支持所有表行格式。通用表空间可以在相对于或独立于数据目录的位置创建。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或者ALTER TABLE tbl_name TABLESPACE [=] tablespace_name to add tables to the tablespace. For more information, see Section 17.6.3.3, “General Tablespaces”.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. For more information, see Section 17.6.3.3, “General Tablespaces”.

撤销表空间包含撤销日志。通过指定完全限定的数据文件路径，可以在选定位置创建撤销表空间。更多信息请参见第 17.6.3.4 节，“撤销表空间”。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 17.6.3.4, “Undo Tablespaces”.

NDB 集群Considerations for NDB Cluster

的考虑事项该语句用于创建表空间，表空间可包含一个或多个数据文件，为 NDB 集群磁盘表提供存储空间（参见第 25.6.11 节，“NDB 集群磁盘表”）。使用该语句创建并添加到表空间中。通过使用 ALTER TABLESPACE 语句添加额外的数据文件（参见第 15.1.10 节，“ALTER TABLESPACE Statement”）。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 25.6.11, “NDB Cluster Disk Data Tables”). 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 15.1.10, “ALTER TABLESPACE Statement”).

Note

所有 NDB 集群磁盘数据对象共享相同的命名空间。这意味着 每个磁盘数据对象 必须具有唯一名称（而不仅仅是每种类型的磁盘数据对象）。例如，不能具有同名的表空间和日志文件组，或者同名的表空间和数据文件。All NDB Cluster Disk Data objects share the same namespace. 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.

一个或多个日志文件的日志文件组UNDO必须分配给要使用该USE LOGFILE GROUP子句创建的表空间。logfile_group必须是通过以下方式创建的现有日志文件组CREATE LOGFILE GROUP（见第 15.1.16 节，“CREATE LOGFILE GROUP 语句”）。多个表空间可以使用相同的日志文件组进行UNDO日志记录。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. logfile_group must be an existing log file group created with CREATE LOGFILE GROUP (see Section 15.1.16, “CREATE LOGFILE GROUP Statement”). Multiple tablespaces may use the same log file group for UNDO logging.

当设置 EXTENT_SIZE 或 INITIAL_SIZE 时，你可以选择在数字后加上一个字母缩写，表示数量级，类似于 my.cnf。通常，这个字母是 M（表示兆字节）或 G（表示 GB）。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. Generally, this is one of the letters M (for megabytes) or G (for gigabytes).

INITIAL_SIZE和 EXTENT_SIZE 可以向下四舍五入：INITIAL_SIZE and EXTENT_SIZE are subject to rounding as follows:

EXTENT_SIZE 可以向上取整到 32K 的整数倍。EXTENT_SIZE is rounded up to the nearest whole multiple of 32K.
INITIAL_SIZE 被四舍五入缩至最近的整数倍数 32K;该结果在任意四舍五入后被向上取整至 EXTENT_SIZE数。INITIAL_SIZE is rounded down to the nearest whole multiple of 32K; this result is rounded up to the nearest whole multiple of EXTENT_SIZE (after any rounding).

Note

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

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

当 CREATE TABLESPACE 使用 ENGINE [=] NDB 时，每个集群数据节点都会创建一个表空间及相关数据文件。您可以通过查询信息模式 FILES 表。（见本节后面示例。）When CREATE TABLESPACE is used with ENGINE [=] NDB, a tablespace and associated data file are created on each Cluster data node. You can verify that the data files were created and obtain information about them by querying the Information Schema FILES table. (See the example later in this section.)

(参见第 28.3.15 节，“INFORMATION_SCHEMA FILES 表”。)(See Section 28.3.15, “The INFORMATION_SCHEMA FILES Table”.)

选项Options

ADD DATAFILE定义表空间数据文件的名称。创建 NDB的;对于 InnoDB，仅在创建撤销表空间时需要。file_name，包括任意指定路径，必须用单引号或双引号引号。文件名（不包括文件扩展名）和目录名必须至少一个字节长度。零长度的文件名和目录名不支持。ADD DATAFILE: Defines the name of a tablespace data file. This option is always required when creating an NDB tablespace; for InnoDB, it is required only when creating an undo tablespace. The file_name, including any specified path, must be quoted with single or double quotation marks. File names (not counting the file extension) and directory names must be at least one byte in length. Zero length file names and directory names are not supported.
由于 InnoDB 和 NDB 对数据文件的处理方式存在较大差异，以下讨论将分别介绍这两个存储引擎。Because there are considerable differences in how InnoDB and NDB treat data files, the two storage engines are covered separately in the discussion that follows.
InnoDB 数据文件。 InnoDB 表空间仅支持一个数据文件，其名称必须包含 .ibd 扩展名。InnoDB data files. An InnoDB tablespace supports only a single data file, whose name must include an .ibd extension.
要将 InnoDB 一般表空间数据文件放置在数据目录之外的位置，请包含一个完全限定路径或相对于数据目录的路径。撤销表空间只允许使用完全限定路径。如果不指定路径，则在数据目录中创建一个通用表空间。在未指定路径的情况下创建的撤销表空间，是在由 innodb_undo_directory的。如果未设置 innodb_undo_directory，则在数据目录中创建撤销表空间。To place an InnoDB general tablespace data file in a location outside of the data directory, include a fully qualified path or a path relative to the data directory. Only a fully qualified path is permitted for undo tablespaces. If you do not specify a path, a general tablespace is created in the data directory. An undo tablespace created without specifying a path is created in the directory defined by the innodb_undo_directory variable. If innodb_undo_directory is not set, undo tablespaces are created in the data directory.
为避免与隐式创建的逐表文件表空间冲突，不支持在数据目录下的子目录中创建 InnoDB 一般表空间。在创建通用表空间或撤销数据目录外的表空间时，该目录必须存在，并且在创建表空间前必须被 InnoDB晓。要让目录已知为 InnoDB，可以将其添加到 innodb_directories 值中，或者添加到附加于 innodb_directories中。innodb_directories 是只读变量。配置它需要重启服务器。To avoid conflicts with implicitly created file-per-table tablespaces, creating an InnoDB general tablespace in a subdirectory under the data directory is not supported. When creating a general tablespace or undo tablespace outside of the data directory, the directory must exist and must be known to InnoDB prior to creating the tablespace. To make a directory known to InnoDB, add it to the innodb_directories value or to one of the variables whose values are appended to the value of innodb_directories. innodb_directories is a read-only variable. Configuring it requires restarting the server.
如果在创建InnoDB表空间时未指定ADD DATAFILE子句，则会隐式创建一个具有唯一文件名的表空间数据文件。唯一文件名是一个 128 位 UUID，格式化为五组由破折号分隔的十六进制数字（aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee）。如果存储引擎需要，会添加文件扩展名。对于InnoDB通用表空间数据文件，会添加.ibd文件扩展名。在复制环境中，在复制源服务器上创建的数据文件名与在副本上创建的数据文件名不同。If the ADD DATAFILE clause is not specified when creating an InnoDB tablespace, a tablespace data file with a unique file name is created implicitly. The unique file name is a 128 bit UUID formatted into five groups of hexadecimal numbers separated by dashes (aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee). A file extension is added if required by the storage engine. An .ibd file extension is added for InnoDB general tablespace data files. In a replication environment, the data file name created on the replication source server is not the same as the data file name created on the replica.
在创建InnoDB表空间时，ADD DATAFILE子句不允许循环目录引用。例如，以下语句中的循环目录引用（/../）是不允许的：The ADD DATAFILE clause does not permit circular directory references when creating an InnoDB tablespace. For example, the circular directory reference (/../) in the following statement is not permitted:
```
CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd 'any_directory/../ts1.ibd';
```
在 Linux 上对此限制存在例外，如果前面的目录是符号链接，则允许循环目录引用。例如，如果any_directory是符号链接，则上例中的数据文件路径是允许的。（数据文件路径仍然允许以'../'开头。）An exception to this restriction exists on Linux, where a circular directory reference is permitted if the preceding directory is a symbolic link. For example, the data file path in the example above is permitted if any_directory is a symbolic link. (It is still permitted for data file paths to begin with '../'.)
NDB 数据文件。 NDB表空间支持多个数据文件，这些数据文件可以具有任何合法文件名；可以通过使用ALTER TABLESPACE语句在创建后向 NDB 集群表空间添加更多数据文件。NDB data files. An NDB tablespace supports multiple data files which can have any legal file names; more data files can be added to an NDB Cluster tablespace following its creation by using an ALTER TABLESPACE statement.
NDB表空间数据文件默认创建在数据节点文件系统目录——即数据节点数据目录下的ndb_nodeid_fs/TS目录 (DataDir)，其中nodeid是数据节点的NodeId。如需将数据文件放在默认位置之外，请包含绝对目录路径或相对于默认位置的路径。如果指定的目录不存在，NDB会尝试创建它；数据节点进程运行的系统用户帐户必须具有适当的权限。An NDB tablespace data file is created by default in the data node file system directory—that is, the directory named ndb_nodeid_fs/TS under the data node's data directory (DataDir), where nodeid is the data node's NodeId. To place the data file in a location other than the default, include an absolute directory path or a path relative to the default location. If the directory specified does not exist, NDB attempts to create it; the system user account under which the data node process is running must have the appropriate permissions to do so.
Note
确定数据文件使用的路径时，NDB不会扩展~（波浪号）字符。When determining the path used for a data file, NDB does not expand the ~ (tilde) character.
当在同一物理主机上运行多个数据节点时，需考虑以下事项：When multiple data nodes are run on the same physical host, the following considerations apply:
- 创建数据文件时，不能指定绝对路径。You cannot specify an absolute path when creating a data file.
- 除非每个数据节点都有单独的数据目录，否则无法在数据节点文件系统目录之外创建表空间数据文件。It is not possible to create tablespace data files outside the data node file system directory, unless each data node has a separate data directory.
- 如果每个数据节点都有自己的数据目录，则可以在该目录内的任何位置创建数据文件。If each data node has its own data directory, data files can be created anywhere within this directory.
- 如果每个数据节点都有自己的数据目录，也可以通过相对路径在节点数据目录之外创建数据文件，只要此路径对于主机上运行的每个数据节点在主机文件系统上解析到唯一的位置。If each data node has its own data directory, it may also be possible to create a data file outside the node's data directory using a relative path, as long as this path resolves to a unique location on the host file system for each data node running on that host.
FILE_BLOCK_SIZE：该选项特有于 InnoDB 的一般表空间，NDB此选项——定义了表空间数据文件的块大小。值可以用字节或千字节表示。例如，一个 8 KB 的文件块大小可以指定为 8192 或 8K。如果不指定该选项，FILE_BLOCK_SIZE默认为 innodb_page_size值。FILE_BLOCK_SIZE 是当你打算使用表空间存储压缩的 InnoDB 表（ROW_FORMAT=COMPRESSED的。在这种情况下，创建表空间时你必须定义表空间 FILE_BLOCK_SIZE。FILE_BLOCK_SIZE: This option—which is specific to InnoDB general tablespaces, and is ignored by NDB—defines the block size for the tablespace data file. Values can be specified in bytes or kilobytes. For example, an 8 kilobyte file block size can be specified as 8192 or 8K. If you do not specify this option, FILE_BLOCK_SIZE defaults to the innodb_page_size value. FILE_BLOCK_SIZE is required when you intend to use the tablespace for storing compressed InnoDB tables (ROW_FORMAT=COMPRESSED). In this case, you must define the tablespace FILE_BLOCK_SIZE when creating the tablespace.
如果 FILE_BLOCK_SIZE 等于 innodb_page_size 值，表空间只能包含未压缩行格式的表（COMPACTREDUNDANTDYNAMIC具有 COMPRESSED 行格式的表，其物理页面大小与未压缩表不同。因此，压缩表不能与未压缩表共存于同一表空间。If FILE_BLOCK_SIZE is equal the innodb_page_size value, the tablespace can contain only tables having an uncompressed row format (COMPACT, REDUNDANT, and DYNAMIC). Tables with a COMPRESSED row format have a different physical page size than uncompressed tables. Therefore, compressed tables cannot coexist in the same tablespace as uncompressed tables.
为了让一般表空间包含压缩表，必须指定FILE_BLOCK_SIZE，并且FILE_BLOCK_SIZE值必须是相对于innodb_page_size。此外，压缩表的物理页面大小（KEY_BLOCK_SIZE）必须等于FILE_BLOCK_SIZE/1024。例如，如果 innodb_page_size=16K，FILE_BLOCK_SIZE=8K，则表的KEY_BLOCK_SIZE必须是 8。更多信息请参见第 17.6.3.3 节，“通用表空间”。For a general tablespace to contain compressed tables, FILE_BLOCK_SIZE must be specified, and the FILE_BLOCK_SIZE value must be a valid compressed page size in relation to the innodb_page_size value. Also, the physical page size of the compressed table (KEY_BLOCK_SIZE) must be equal to FILE_BLOCK_SIZE/1024. For example, if innodb_page_size=16K, and FILE_BLOCK_SIZE=8K, the KEY_BLOCK_SIZE of the table must be 8. For more information, see Section 17.6.3.3, “General Tablespaces”.
USE LOGFILE GROUP：NDB，这是之前用 CREATE LOGFILE GROUP”。不支持 InnoDB，因错误而失败。USE LOGFILE GROUP: Required for NDB, this is the name of a log file group previously created using CREATE LOGFILE GROUP. Not supported for InnoDB, where it fails with an error.
EXTENT_SIZE：此选项特有于 NDB，InnoDB 不支持，在 InnoDB 中会失败。EXTENT_SIZE 设置任何属于表空间文件的字节大小。默认值为 1M。最小尺寸为 32K，理论最大为 2G，尽管实际最大大小取决于多种因素。在大多数情况下，改变范围大小对性能没有可测量的影响，且默认值建议除极其特殊情况外。EXTENT_SIZE: This option is specific to NDB, and is not supported by InnoDB, where it fails with an error. EXTENT_SIZE sets the size, in bytes, of the extents used by any files belonging to the tablespace. The default value is 1M. The minimum size is 32K, and theoretical maximum is 2G, although the practical maximum size depends on a number of factors. In most cases, changing the extent size does not have any measurable effect on performance, and the default value is recommended for all but the most unusual situations.
每六周extent is a unit of disk space allocation. One extent is filled with as much data as that extent can contain before another extent is used. In theory, up to 65,535 (64K) extents may used per data file; however, the recommended maximum is 32,768 (32K). The recommended maximum size for a single data file is 32G—that is, 32K extents × 1 MB per extent. In addition, once an extent is allocated to a given partition, it cannot be used to store data from a different partition; an extent cannot store data from more than one partition. This means, for example that a tablespace having a single datafile whose INITIAL_SIZE (described in the following item) is 256 MB and whose EXTENT_SIZE is 128M has just two extents, and so can be used to store data from at most two different disk data table partitions.An extent is a unit of disk space allocation. One extent is filled with as much data as that extent can contain before another extent is used. In theory, up to 65,535 (64K) extents may used per data file; however, the recommended maximum is 32,768 (32K). The recommended maximum size for a single data file is 32G—that is, 32K extents × 1 MB per extent. In addition, once an extent is allocated to a given partition, it cannot be used to store data from a different partition; an extent cannot store data from more than one partition. This means, for example that a tablespace having a single datafile whose INITIAL_SIZE (described in the following item) is 256 MB and whose EXTENT_SIZE is 128M has just two extents, and so can be used to store data from at most two different disk data table partitions.
您可以通过查询信息模式FILES表来查看给定数据文件中还剩多少个区间，从而推算文件中剩余空间的大致估计。有关更多讨论和示例，请参见第 28.3.15 节，“INFORMATION_SCHEMA FILES 表”.You can see how many extents remain free in a given data file by querying the Information Schema FILES table, and so derive an estimate for how much space remains free in the file. For further discussion and examples, see Section 28.3.15, “The INFORMATION_SCHEMA FILES Table”.
INITIAL_SIZE：此选项特有于 NDB，InnoDB，INITIAL_SIZE: This option is specific to NDB, and is not supported by InnoDB, where it fails with an error.
后者会错误。INITIAL_SIZE参数通过 ADD DATATFILE。文件创建后，其大小无法更改;然而，你可以使用 ALTER TABLESPACE ... ADD DATAFILE。The INITIAL_SIZE parameter sets the total size in bytes of the data file that was specific using ADD DATATFILE. Once this file has been created, its size cannot be changed; however, you can add more data files to the tablespace using ALTER TABLESPACE ... ADD DATAFILE.
INITIAL_SIZE为可选;其默认值为 134217728（128 MB）。INITIAL_SIZE is optional; its default value is 134217728 (128 MB).
在 32 位系统上，INITIAL_SIZE为 4294967296（4 GB）。On 32-bit systems, the maximum supported value for INITIAL_SIZE is 4294967296 (4 GB).
AUTOEXTEND_SIZE：定义InnoDB 在表空间满时扩展的幅度。设置必须是 4MB 的整数倍。默认设置为 0，这会根据隐含的默认行为扩展表空间。更多信息请参见第 17.6.3.9 节，“表空间 AUTOEXTEND_SIZE 配置”。AUTOEXTEND_SIZE: Defines the amount by which InnoDB extends the size of the tablespace when it becomes full. The setting must be a multiple of 4MB. The default setting is 0, which causes the tablespace to be extended according to the implicit default behavior. For more information, see Section 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”.
使用何种存储引擎，均不影响任何 MySQL NDB 集群版本。Has no effect in any release of MySQL NDB Cluster, regardless of the storage engine used.
MAX_SIZE：目前被 MySQL 忽略;保留以供未来可能使用。无论使用何种存储引擎，在任何 MySQL 或 MySQL NDB 集群的任何版本中均无影响。MAX_SIZE: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL or MySQL NDB Cluster, regardless of the storage engine used.
NODEGROUP目前被 MySQL 忽略;保留以供未来可能使用。无论使用何种存储引擎，在 MySQL 或 MySQL NDB 集群的任何版本中均无影响。NODEGROUP: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL or MySQL NDB Cluster, regardless of the storage engine used.
WAIT：目前被 MySQL 忽略;保留以供未来可能使用。无论使用何种存储引擎，在任何 MySQL 或 MySQL NDB 集群的任何版本中均无影响。WAIT: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL or MySQL NDB Cluster, regardless of the storage engine used.
COMMENT目前被 MySQL 忽略;保留以供未来可能使用。无论使用何种存储引擎，都不会影响任何 MySQL 或 MySQL NDB 集群的版本。COMMENT: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL or MySQL NDB Cluster, regardless of the storage engine used.
ENCRYPTION 子句启用或禁用 InnoDB 通用表空间的页级数据加密。The ENCRYPTION clause enables or disables page-level data encryption for an InnoDB general tablespace.
如果未指定 ENCRYPTION 子句，default_table_encryption 设置将控制是否启用加密。ENCRYPTION 子句会覆盖 default_table_encryption 设置。然而，如果启用了 table_encryption_privilege_check 变量，则需要 TABLE_ENCRYPTION_ADMIN 权限才能使用与 default_table_encryption 设置不同的 ENCRYPTION 子句设置。If the ENCRYPTION clause is not specified, the default_table_encryption setting controls whether encryption is enabled. The ENCRYPTION clause overrides the default_table_encryption setting. However, if the table_encryption_privilege_check variable is enabled, the TABLE_ENCRYPTION_ADMIN privilege is required to use an ENCRYPTION clause setting that differs from the default_table_encryption setting.
在创建启用加密的表空间之前，必须安装并配置密钥环插件。A keyring plugin must be installed and configured before an encryption-enabled tablespace can be created.
当通用表空间被加密时，所有存放在该表空间的表都会被加密。同样，在加密表空间中创建的表也是加密的。When a general tablespace is encrypted, all tables residing in the tablespace are encrypted. Likewise, a table created in an encrypted tablespace is encrypted.
有关更多信息，请参见第 17.13 节，“InnoDB 静态数据加密”For more information, see Section 17.13, “InnoDB Data-at-Rest Encryption”
ENGINE：定义使用该表空间的存储引擎，其中 engine_name 为存储引擎名称。目前，标准 MySQL 8.4 版本仅支持 InnoDB 存储引擎。MySQL NDB Cluster 支持 NDB 和 InnoDB 表空间。如果未指定该选项，将使用系统变量 default_storage_engine 的值作为 ENGINE。ENGINE: Defines the storage engine which uses the tablespace, where engine_name is the name of the storage engine. Currently, only the InnoDB storage engine is supported by standard MySQL 8.4 releases. MySQL NDB Cluster supports both NDB and InnoDB tablespaces. The value of the default_storage_engine system variable is used for ENGINE if the option is not specified.
ENGINE_ATTRIBUTE 选项用于为主存储引擎指定表空间属性。该选项为将来使用保留。The ENGINE_ATTRIBUTE option is used to specify tablespace attributes for primary storage engines. The option is reserved for future use.
分配给该选项的值必须是包含有效 JSON 文档的字符串字面量，或为空字符串 ('')。无效的 JSON 将被拒绝。The value assigned to this option must be a string literal containing a valid JSON document or an empty string (''). Invalid JSON is rejected.
```
CREATE TABLESPACE ts1 ENGINE_ATTRIBUTE='{"key":"value"}';
```
ENGINE_ATTRIBUTE 的值可以重复而不会报错。在这种情况下，将使用最后指定的值。ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.
ENGINE_ATTRIBUTE 的值不会被服务器检查，也不会在更改表的存储引擎时被清除。ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.

注意事项Notes

有关 MySQL 表空间命名规则，请参见第 11.2 节，“模式对象名称”。除了这些规则外，不允许使用斜杠字符（“/”），且不能使用以 innodb_ 开头的名称，因为该前缀保留给系统使用。For the rules covering the naming of MySQL tablespaces, see Section 11.2, “Schema Object Names”. In addition to these rules, the slash character (“/”) is not permitted, nor can you use names beginning with innodb_, as this prefix is reserved for system use.
不支持创建临时通用表空间。Creation of temporary general tablespaces is not supported.
通用表空间不支持临时表。General tablespaces do not support temporary tables.
TABLESPACE选项可与 CREATE TABLE 或 ALTER TABLE“来将 InnoDB 表分区或子分区分配给每张文件的表空间。所有分区必须属于同一个存储引擎。不支持将表分区分配到共享的 InnoDB 表空间。共享表空间包括 InnoDB 系统表空间和通用表空间。The TABLESPACE option may be used with CREATE TABLE or ALTER TABLE to assign an InnoDB table partition or subpartition to a file-per-table tablespace. All partitions must belong to the same storage engine. Assigning table partitions to shared InnoDB tablespaces is not supported. Shared tablespaces include the InnoDB system tablespace and general tablespaces.
通用表空间支持添加任意行格式的表，使用 CREATE TABLE ... TABLESPACE。innodb_file_per_table 不需要General tablespaces support the addition of tables of any row format using CREATE TABLE ... TABLESPACE. innodb_file_per_table does not need to be enabled.
启用。innodb_strict_mode 不适用于通用表空间。表空间管理规则严格独立执行 innodb_strict_mode。如果 CREATE TABLESPACE参数错误或不兼容，操作无论innodb_strict_mode。当表使用 CREATE TABLE ... TABLESPACE 或 ALTER TABLE ... TABLESPACEinnodb_strict_mode 被忽略，但该语句被评估为 innodb_strict_mode。innodb_strict_mode is not applicable to general tablespaces. Tablespace management rules are strictly enforced independently of innodb_strict_mode. If CREATE TABLESPACE parameters are incorrect or incompatible, the operation fails regardless of the innodb_strict_mode setting. When a table is added to a general tablespace using CREATE TABLE ... TABLESPACE or ALTER TABLE ... TABLESPACE, innodb_strict_mode is ignored but the statement is evaluated as if innodb_strict_mode is enabled.
使用 DROP TABLESPACE 来移除表空间。所有表必须在丢弃表空间前，使用 DROP TABLE“，然后才丢弃表空间。在丢弃 NDB 集群表空间之前，你还必须使用 ALTER TABLESPACE ... DROP DATATFILE 语句。参见第 25.6.11.1 节，“NDB 集群磁盘数据对象”。Use DROP TABLESPACE to remove a tablespace. All tables must be dropped from a tablespace using DROP TABLE prior to dropping the tablespace. Before dropping an NDB Cluster tablespace you must also remove all its data files using one or more ALTER TABLESPACE ... DROP DATATFILE statements. See Section 25.6.11.1, “NDB Cluster Disk Data Objects”.
InnoDB 表添加到 InnoDB 的一般表空间中，包括索引和 BLOB 页面。All parts of an InnoDB table added to an InnoDB general tablespace reside in the general tablespace, including indexes and BLOB pages.
对于分配给表空间的NDB表，只有未被索引的列存储在磁盘上，并且实际使用表空间的数据文件。所有NDB始终保存在内存中。For an NDB table assigned to a tablespace, only those columns which are not indexed are stored on disk, and actually use the tablespace data files. Indexes and indexed columns for all NDB tables are always kept in memory.
类似于系统表空间，截断或丢弃存储在一般表空间中的表会在通用表空间内部创建空闲空间.ibd data file，只能用于新的 InnoDB 数据。空间不会像文件对应表空间那样释放回操作系统。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.
一般表空间不关联任何数据库或模式。A general tablespace is not associated with any database or schema.
ALTER TABLE ... DISCARD TABLESPACE 和 ALTER TABLE ...IMPORT TABLESPACE 不支持属于一般表空间的表。ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ...IMPORT TABLESPACE are not supported for tables that belong to a general tablespace.
服务器对引用一般表空间的 DDL 使用表空间级别的元数据锁定。相比之下，服务器对引用逐表表空间的 DDL 使用表层元数据锁定。The server uses tablespace-level metadata locking for DDL that references general tablespaces. By comparison, the server uses table-level metadata locking for DDL that references file-per-table tablespaces.
生成或现有的表空间不能更改为通用表空间。A generated or existing tablespace cannot be changed to a general tablespace.
常规表空间名称与按表文件表空间名称之间不存在冲突。该“/”字符，在每表文件表空间名称中存在，不允许出现在通用表空间名称中。There is no conflict between general tablespace names and file-per-table tablespace names. The “/” character, which is present in file-per-table tablespace names, is not permitted in general tablespace names.
mysqldump 不会转储 InnoDB CREATE TABLESPACE 语句。mysqldump does not dump InnoDB CREATE TABLESPACE statements.

InnoDB 示例InnoDB Examples

：创建通用表空间并添加三个不同行格式的未压缩表。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;

本示例演示创建通用表空间并添加压缩表。该示例假设默认值为 16Kinnodb_page_size值为 16K。8192 中的 FILE_BLOCK_SIZE 要求压缩表的 KEY_BLOCK_SIZE为 8。This example demonstrates creating a general tablespace and adding a compressed table. The example assumes a default innodb_page_size value of 16K. The FILE_BLOCK_SIZE of 8192 requires that the compressed table have a KEY_BLOCK_SIZE of 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;

本示例演示了创建通用表空间时不指定 ADD DATAFILE 子句，该子句为可选This example demonstrates creating a general tablespace without specifying the ADD DATAFILE clause, which is optional:

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 示例NDB Example

假设您希望使用名为 myts。NDB 表空间总是需要使用由一个或多个撤销日志文件组成的日志文件组。在这个例子中，我们首先创建一个名为 mylg，里面包含一个名为 myundo-1.dat，使用 CREATE LOGFILE GROUP句：Suppose that you wish to create an NDB Cluster Disk Data tablespace named myts using a datafile named mydata-1.dat. An NDB tablespace always requires the use of a log file group consisting of one or more undo log files. 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:

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)

你现在可以用 CREATE TABLE句创建磁盘表，TABLESPACE，STORAGE DISK 选项，类似于此处所示：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:

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)

需要注意的是，只有 dob 和 joined column 来自 mytable实际上存储在磁盘上，因为 id，lname，fname 列都是索引的。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.

如前所述，当 CREATE TABLESPACE 与 ENGINE [=] NDB 一起使用时，每个 NDB 集群数据节点都会创建一个表空间及其相关数据文件。您可以通过查询信息模式FILES 表，如此处所示：As mentioned previously, when CREATE TABLESPACE is used with ENGINE [=] NDB, a tablespace and its associated data file are created on each NDB Cluster data node. You can verify that the data files were created and obtain information about them by querying the Information Schema FILES table, as shown here:

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)

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

首页上一级下一节