CREATE TABLESPACE tablespace
    ADD DATAFILE 'file'
    USE LOGFILE GROUP logfile_group
    [EXTENT_SIZE [=] extent_size]
    [INITIAL_SIZE [=] initial_size]
    ENGINE [=] engine

This statement is used to create a tablespace, which can contain one or more data files, providing storage space for 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 13.1.4, “ALTER TABLESPACE Syntax”).

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 13.1.9, “CREATE LOGFILE GROUP Syntax”). Multiple tablespaces may use the same log file group for UNDO logging.

The EXTENT_SIZE sets the size, in bytes, of the extents used by any files belonging to the tablespace. The default value is 4 bytes.

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. You can see how many extents for a given file remain free 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 22.21, “The INFORMATION_SCHEMA FILES Table”.

The INITIAL_SIZE parameter sets the data file's total size in bytes. Once the file has been created, its size cannot be changed; however, you can add more data files to the tablespace using ALTER TABLESPACE ... ADD DATAFILE. See Section 13.1.4, “ALTER TABLESPACE Syntax”.

INITIAL_SIZE is optional; its default value is 128M.

When setting EXTENT_SIZE or INITIAL_SIZE (either or both), 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).

The ENGINE parameter determines the storage engine which uses this tablespace, with engine being the name of the storage engine. In MySQL 5.1, engine must be one of the values NDB or NDBCLUSTER.

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. For example:

mysql> SELECT LOGFILE_GROUP_NAME, FILE_NAME, EXTRA 
    -> FROM INFORMATION_SCHEMA.FILES
    -> WHERE TABLESPACE_NAME = 'newts' AND FILE_TYPE = 'DATAFILE';
+--------------------+-------------+----------------+
| LOGFILE_GROUP_NAME | FILE_NAME   | EXTRA          |
+--------------------+-------------+----------------+
| lg_3               | newdata.dat | CLUSTER_NODE=3 |
| lg_3               | newdata.dat | CLUSTER_NODE=4 |
+--------------------+-------------+----------------+
2 rows in set (0.01 sec)

(See Section 22.21, “The INFORMATION_SCHEMA FILES Table”.)

CREATE TABLESPACE was added in MySQL 5.1.6. In MySQL 5.1, it is useful only with Disk Data storage for MySQL Cluster. See Section 15.11, “MySQL Cluster Disk Data Tables”.