CREATE TABLESPACE tablespace_name InnoDB and NDB: ADD DATAFILE 'file_name' InnoDB only: [FILE_BLOCK_SIZE = value] NDB only: USE LOGFILE GROUP logfile_group [EXTENT_SIZE [=] extent_size] [INITIAL_SIZE [=] initial_size] [AUTOEXTEND_SIZE [=] autoextend_size] [MAX_SIZE [=] max_size] [NODEGROUP [=] nodegroup_id] [WAIT] [COMMENT [=] 'string'] InnoDB and NDB: [ENGINE [=] engine_name]
This statement is used to create a tablespace. The precise syntax
and semantics depend on the storage engine used. In standard MySQL
5.7 releases, this is always an
InnoDB tablespace. MySQL NDB Cluster
7.5 also supports tablespaces using the
NDB storage engine in addition to
InnoDB tablespace created using
CREATE TABLESPACE is referred to as a
general tablespace. This is a shared
tablespace, similar to the system 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
MySQL data directory.
After creating an
InnoDB general tablespace,
you can use
tbl_name ... TABLESPACE [=]
to add tables
to the tablespace.
tbl_name TABLESPACE [=]
For more information, see Section 14.7.9, “InnoDB General Tablespaces”.
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 21.5.13, “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
statement (see Section 13.1.9, “ALTER TABLESPACE Syntax”).
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.
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
GROUP (see Section 13.1.15, “CREATE LOGFILE GROUP Syntax”).
Multiple tablespaces may use the same log file group for
INITIAL_SIZE, you may optionally follow the
number with a one-letter abbreviation for an order of magnitude,
similar to those used in
this is one of the letters
M (for megabytes) or
G (for gigabytes).
are subject to rounding as follows:
EXTENT_SIZEis rounded up to the nearest whole multiple of 32K.
INITIAL_SIZEis 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).
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
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 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.)
ADD DATAFILE: Defines the name of a tablespace data file; this option is always required. An
InnoDBtablespace supports only a single data file, whose name must include a
.ibdextension. An NDB Cluster 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 TABLESPACEis not supported by
To place the data file in a location outside of the MySQL data directory (
datadir), include an absolute directory path or a path relative to the MySQL data directory. If you do not specify a path, the tablespace is created in the MySQL data directory. An isl file is created in the MySQL data directory when an
InnoDBtablespace is created outside of the MySQL data directory.
To avoid conflicts with implicitly created file-per-table tablespaces, creating a general tablespace in a subdirectory under the MySQL data directory is not supported. Also, when creating a general tablespace outside of the MySQL data directory, the directory must exist prior to creating the tablespace.
, including the path (optional), must be quoted with single or double quotations marks. File names (not counting any “.ibd” extension for
InnoDBfiles) and directory names must be at least one byte in length. Zero length file names and directory names are not supported.
FILE_BLOCK_SIZE: This option—which is specific to
InnoDB, and is ignored by
NDB—defines the block size for the tablespace data file. If you do not specify this option,
FILE_BLOCK_SIZEis required when you intend to use the tablespace for storing compressed
innodb_page_size, the tablespace can contain only tables having an uncompressed row format (
DYNAMIC). The physical page size for tables using
COMPRESSEDdiffers from that of uncompressed tables; this means that compressed tables and uncompressed tables cannot coexist in the same tablespace.
For a general tablespace to contain compressed tables,
FILE_BLOCK_SIZEmust be specified, and the
FILE_BLOCK_SIZEvalue must be a valid compressed page size in relation to the
innodb_page_sizevalue. Also, the physical page size of the compressed table (
KEY_BLOCK_SIZE) must be equal to
FILE_BLOCK_SIZE/1024. For example, if
KEY_BLOCK_SIZEof the table must be 8. For more information, see Section 14.7.9, “InnoDB General Tablespaces”.
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: This option is specific to NDB, and is not supported by InnoDB, where it fails with an error.
EXTENT_SIZEsets 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.
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_SIZEis 128M has just two extents, and so can be used to store data from at most two different disk data table partitions.
You can see how many extents remain free in a given data file by querying the
INFORMATION_SCHEMA.FILEStable, and so derive an estimate for how much space remains free in the file. For further discussion and examples, see Section 24.8, “The INFORMATION_SCHEMA FILES Table”.
INITIAL_SIZE: This option is specific to
NDB, and is not supported by
InnoDB, where it fails with an error.
INITIAL_SIZEparameter 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_SIZEis optional; its default value is 134217728 (128 MB).
On 32-bit systems, the maximum supported value for
INITIAL_SIZEis 4294967296 (4 GB).
AUTOEXTEND_SIZE: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 5.7 or MySQL NDB Cluster 7.5, regardless of the storage engine used.
MAX_SIZE: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 5.7 or MySQL NDB Cluster 7.5, regardless of the storage engine used.
NODEGROUP: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 5.7 or MySQL NDB Cluster 7.5, regardless of the storage engine used.
WAIT: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 5.7 or MySQL NDB Cluster 7.5, regardless of the storage engine used.
COMMENT: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 5.7 or MySQL NDB Cluster 7.5, regardless of the storage engine used.
ENGINE: Defines the storage engine which uses the tablespace, where
engine_nameis the name of the storage engine. Currently, only the
InnoDBstorage engine is supported by standard MySQL 5.7 releases. MySQL NDB Cluster 7.5 supports both
InnoDBtablespaces. The value of the
default_storage_enginesystem variable is used for
ENGINEif the option is not specified.
For the rules covering the naming of MySQL tablespaces, see Section 9.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.
Tablespaces do not support temporary tables.
TABLESPACEoption may be used with
ALTER TABLEto assign
InnoDBtable partitions or subpartitions to a general tablespace, a separate file-per-table tablespace, or the system tablespace.
TABLESPACEoption support for table partitions and subpartitions was added in MySQL 5.7. All partitions must belong to the same storage engine. For more information, see Section 14.7.9, “InnoDB General Tablespaces”.
innodb_file_format_maxsettings have no influence on
innodb_file_per_tabledoes not need to be enabled. General tablespaces support all table row formats regardless of file format settings. Likewise, general tablespaces support the addition of tables of any row format using
CREATE TABLE ... TABLESPACE, regardless of file format settings.
innodb_strict_modeis not applicable to general tablespaces. Tablespace management rules are strictly enforced independently of
CREATE TABLESPACEparameters are incorrect or incompatible, the operation fails regardless of the
innodb_strict_modesetting. When a table is added to a general tablespace using
CREATE TABLE ... TABLESPACEor
ALTER TABLE ... TABLESPACE,
innodb_strict_modeis ignored but the statement is evaluated as if
DROP TABLESPACEto remove a tablespace. All tables must be dropped from a tablespace using
DROP TABLEprior 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 DATATFILEstatements. See Section 18.104.22.168, “NDB Cluster Disk Data Objects”.
All parts of an
InnoDBtable added to an
InnoDBgeneral tablespace reside in the general tablespace, including indexes and
NDBtable 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
NDBtables are always kept in memory.
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
InnoDBdata. 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.
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.
Tables stored in a general tablespace can only be opened in MySQL 5.7.6 or later due to the addition of new table flags.
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.
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; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) -> TABLESPACE ts1 -> ROW_FORMAT=REDUNDANT; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) -> TABLESPACE ts1 -> ROW_FORMAT=COMPACT; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) -> TABLESPACE ts1 -> ROW_FORMAT=DYNAMIC; Query OK, 0 rows affected (0.00 sec)
This example demonstrates creating a general tablespace and adding
a compressed table. The example assumes a default
innodb_page_size 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; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) -> TABLESPACE ts2 -> ROW_FORMAT=COMPRESSED -> KEY_BLOCK_SIZE=8; Query OK, 0 rows affected (0.00 sec)
Suppose that you wish to create an NDB Cluster Disk Data
myts using a datafile named
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
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
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)
It is important to note that only the
joined columns from
are actually stored on disk, due to the fact that the
fname columns are all indexed.
As mentioned previously, when
is used with
ENGINE [=] NDB, a tablespace and
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
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 22.214.171.124, “NDB Cluster Disk Data Objects”.