ALTER TABLESPACE tablespace
    ADD DATAFILE 'file'
    [INITIAL_SIZE [=] size]
    ENGINE [=] engine

ALTER TABLESPACE tablespace
    DROP DATAFILE 'file'
    ENGINE [=] engine

This statement can be used either to add a new data file, or to drop a data file from a tablespace.

The ADD DATAFILE variant allows you to specify an initial size using an INITIAL_SIZE clause, where size is measured in bytes; the default value is 128M (128 megabytes). You may optionally follow this integer value 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).

Once a data file has been created, its size cannot be changed; however, you can add more data files to the tablespace using additional ALTER TABLESPACE ... ADD DATAFILE statements.

Using DROP DATAFILE with ALTER TABLESPACE drops the data file 'file' from the tablespace. This file must already have been added to the tablespace using CREATE TABLESPACE or ALTER TABLESPACE; otherwise an error will result.

Both ALTER TABLESPACE ... ADD DATAFILE and ALTER TABLESPACE ... DROP DATAFILE require an ENGINE clause which specifies the storage engine used by the tablespace. In MySQL 5.1, the only accepted values for engine are NDB and NDBCLUSTER.

When ALTER TABLESPACE ... ADD DATAFILE is used with ENGINE = NDB, a data file is 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, the following query shows all data files belonging to the tablespace named newts:

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 |
| lg_3               | newdata2.dat | CLUSTER_NODE=3 |
| lg_3               | newdata2.dat | CLUSTER_NODE=4 |
+--------------------+--------------+----------------+
2 rows in set (0.03 sec)

See Section 22.21, “The INFORMATION_SCHEMA FILES Table”.

ALTER 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”.