Movatterモバイル変換

ALTER [UNDO] TABLESPACEtablespace_nameNDB only:    {ADD | DROP} DATAFILE 'file_name'    [INITIAL_SIZE [=] size]    [WAIT]InnoDB and NDB:    [RENAME TOtablespace_name]InnoDB only:    [AUTOEXTEND_SIZE [=] 'value']    [SET {ACTIVE | INACTIVE}]    [ENCRYPTION [=] {'Y' | 'N'}]InnoDB and NDB:    [ENGINE [=]engine_name]Reserved for future use:    [ENGINE_ATTRIBUTE [=] 'string']

This statement is used withNDB andInnoDB tablespaces. It can be used to add a new data file to, or to drop a data file from anNDB tablespace. It can also be used to rename an NDB Cluster Disk Data tablespace, rename anInnoDB general tablespace, encrypt anInnoDB general tablespace, or mark anInnoDB undo tablespace as active or inactive.

TheUNDO keyword, introduced in MySQL 8.0.14, is used with theSET {ACTIVE | INACTIVE} clause to mark anInnoDB undo tablespace as active or inactive. For more information, seeSection 17.6.3.4, “Undo Tablespaces”.

TheADD DATAFILE variant enables you to specify an initial size for anNDB Disk Data tablespace using anINITIAL_SIZE clause, wheresize is measured in bytes; the default value is 134217728 (128 MB). You may optionally followsize with a one-letter abbreviation for an order of magnitude, similar to those used inmy.cnf. Generally, this is one of the lettersM (megabytes) orG (gigabytes).

On 32-bit systems, the maximum supported value forINITIAL_SIZE is 4294967296 (4 GB). (Bug #29186)

INITIAL_SIZE is rounded, explicitly, as forCREATE TABLESPACE.

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

WhenALTER TABLESPACE ... ADD DATAFILE is used withENGINE = NDB, a data file is created on each Cluster data node, but only one row is generated in the Information SchemaFILES table. See the description of this table, as well asSection 25.6.11.1, “NDB Cluster Disk Data Objects”, for more information.ADD DATAFILE is not supported withInnoDB tablespaces.

UsingDROP DATAFILE withALTER TABLESPACE drops the data file 'file_name' from an NDB tablespace. You cannot drop a data file from a tablespace which is in use by any table; in other words, the data file must be empty (no extents used). SeeSection 25.6.11.1, “NDB Cluster Disk Data Objects”. In addition, any data file to be dropped must previously have been added to the tablespace withCREATE TABLESPACE orALTER TABLESPACE.DROP DATAFILE is not supported withInnoDB tablespaces.

WAIT is parsed but otherwise ignored. It is intended for future expansion.

TheENGINE clause, which specifies the storage engine used by the tablespace, is deprecated; expect it to be removed in a future release. The tablespace storage engine is known by the data dictionary, making theENGINE clause obsolete. If the storage engine is specified, it must match the tablespace storage engine defined in the data dictionary. The only values forengine_name compatible withNDB tablespaces areNDB andNDBCLUSTER.

RENAME TO operations are implicitly performed inautocommit mode, regardless of theautocommit setting.

ARENAME TO operation cannot be performed whileLOCK TABLES orFLUSH TABLES WITH READ LOCK is in effect for tables that reside in the tablespace.

Exclusivemetadata locks are taken on tables that reside in a general tablespace while the tablespace is renamed, which prevents concurrent DDL. Concurrent DML is supported.

TheCREATE TABLESPACE privilege is required to rename anInnoDB general tablespace.

TheAUTOEXTEND_SIZE option defines the amount by whichInnoDB extends the size of a tablespace when it becomes full. Introduced in MySQL 8.0.23. 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, seeSection 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”.

TheENCRYPTION clause enables or disables page-level data encryption for anInnoDB general tablespace or themysql system tablespace. Encryption support for general tablespaces was introduced in MySQL 8.0.13. Encryption support for themysql system tablespace was introduced in MySQL 8.0.16.

A keyring plugin must be installed and configured before encryption can be enabled.

As of MySQL 8.0.16, if thetable_encryption_privilege_check variable is enabled, theTABLE_ENCRYPTION_ADMIN privilege is required to alter a general tablespace with anENCRYPTION clause setting that differs from thedefault_table_encryption setting.

Enabling encryption for a general tablespace fails if any table in the tablespace belongs to a schema defined withDEFAULT ENCRYPTION='N'. Similarly, disabling encryption fails if any table in the general tablespace belongs to a schema defined withDEFAULT ENCRYPTION='Y'. TheDEFAULT ENCRYPTION schema option was introduced in MySQL 8.0.16.

If anALTER TABLESPACE statement executed on a general tablespace does not include anENCRYPTION clause, the tablespace retains its current encryption status, regardless of thedefault_table_encryption setting.

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

TheINPLACE algorithm is used when altering theENCRYPTION attribute of a general tablespace or themysql system tablespace. TheINPLACE algorithm permits concurrent DML on tables that reside in the tablespace. Concurrent DDL is blocked.

For more information, seeSection 17.13, “InnoDB Data-at-Rest Encryption”.

TheENGINE_ATTRIBUTE option (available as of MySQL 8.0.21) is used to specify tablespace attributes for primary storage engines. The option is reserved for future use.

Permitted values are a string literal containing a validJSON document or an empty string (''). InvalidJSON is rejected.

ALTER TABLESPACE ts1 ENGINE_ATTRIBUTE='{"key":"value"}';

ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.

ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.

It is not permitted to alter an individual element of a JSON attribute value. You can only add or replace an attribute.