Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.2Mb
PDF (A4) - 38.3Mb
PDF (RPM) - 33.1Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 133.2Kb
Man Pages (Zip) - 189.3Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

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


CREATE TABLESPACE tablespace_name
    ADD DATAFILE 'file_name'
    [FILE_BLOCK_SIZE = value]
    [ENCRYPTION [=] {'Y' | 'N'}]
    [ENGINE [=] engine_name]

This statement is used to create an InnoDB tablespace. An InnoDB tablespace created using CREATE TABLESPACE is referred to as general tablespace.

A general tablespace is a shared tablespace, similar to the system tablespace. It can hold multiple tables, and supports all table row formats. General tablespaces can also be created in a location relative to or independent of the MySQL data directory.

After creating an InnoDB general tablespace, you can 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 15.7.10, “InnoDB General Tablespaces”.


CREATE TABLESPACE is supported with InnoDB. In earlier releases, CREATE TABLESPACE only supported NDB, which is the MySQL NDB Cluster storage engine.


  • ADD DATAFILE: Defines the name of the tablespace data file. A data file must be specified with the CREATE TABLESPACE statement, and the data file name must have a .ibd extension. An InnoDB general tablespace only supports a single data file.

    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 general tablespace is created in 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 and must be known to InnoDB prior to creating the tablespace. To make an unknown directory known to InnoDB, add the directory to the innodb_directories argument value. innodb_directories is a read-only startup option. Configuring it requires restarting the server.

    The file_name, including the path (optional), must be quoted with single or double quotations marks. File names (not counting the .ibd extension) and directory names must be at least one byte in length. Zero length file names and directory names are not supported.

  • FILE_BLOCK_SIZE: 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 innodb_page_size. 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.

    If FILE_BLOCK_SIZE is equal innodb_page_size, the tablespace can contain only tables having an uncompressed row format (COMPACT, REDUNDANT, and DYNAMIC row formats). 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.

    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 15.7.10, “InnoDB General Tablespaces”.

  • The ENCRYPTION option is used to enable or disable page-level data encryption for an InnoDB general tablespace. Option values are not case-sensitive. Encryption support for general tablespaces was introduced in MySQL 8.0.13. A keyring plugin must be installed and configured to use the ENCRYPTION option.

    When a general tablespace is encrypted, all tables residing in the tablespace are encrypted. Likewise, a table created in an encrypted general tablespace is encrypted.

    For more information, see Section 15.7.11, “InnoDB Tablespace Encryption”

  • 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. ENGINE = InnoDB must be defined as part of the CREATE TABLESPACE statement or InnoDB must be defined as the default storage engine (default_storage_engine=InnoDB).


  • tablespace_name is a case-sensitive identifier for the tablespace. It may be quoted or unquoted. The forward slash character (/) is not permitted. Names beginning with innodb_ are either not permitted or are reserved for special use.

  • Creation of temporary general tablespaces is not supported.

  • General tablespaces do not support temporary tables.

  • 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 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.

  • Use DROP TABLESPACE to remove a general tablespace. All tables must be dropped from a general tablespace using DROP TABLE prior to dropping the tablespace.

  • All parts of a table added to a general tablespace reside in the general tablespace, including indexes and BLOB pages.

  • 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 and ALTER TABLE ...IMPORT TABLESPACE are not supported for tables that belong to a general tablespace.

  • 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 and mysqlpump do not dump InnoDB CREATE TABLESPACE statements.


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)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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)

Query OK, 0 rows affected (0.00 sec)

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.