Movatterモバイル変換

MySQL 9.3 Reference Manual

17.12.1 Online DDL Operations

Online support details, syntax examples, and usage notes for DDL operations are provided under the following topics in this section.

Index Operations

The following table provides an overview of online DDL support for index operations. An asterisk indicates additional information, an exception, or a dependency. For details, seeSyntax and Usage Notes.

Table 17.13 Online DDL Support for Index Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Creating or adding a secondary index	No	Yes	No	Yes	No
Dropping an index	No	Yes	No	Yes	Yes
Renaming an index	No	Yes	No	Yes	Yes
Adding a`FULLTEXT` index	No	Yes*	No*	No	No
Adding a`SPATIAL` index	No	Yes	No	No	No
Changing the index type	Yes	Yes	No	Yes	Yes

Syntax and Usage Notes

Creating or adding a secondary index
```
CREATE INDEXname ONtable (col_list);
```
```
ALTER TABLEtbl_name ADD INDEXname (col_list);
```
The table remains available for read and write operations while the index is being created. TheCREATE INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.
Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.
A newly created secondary index contains only the committed data in the table at the time theCREATE INDEX orALTER TABLE statement finishes executing. It does not contain any uncommitted values, old versions of values, or values marked for deletion but not yet removed from the old index.
Some factors affect the performance, space usage, and semantics of this operation. For details, seeSection 17.12.8, “Online DDL Limitations”.
Dropping an index
```
DROP INDEXname ONtable;
```
```
ALTER TABLEtbl_name DROP INDEXname;
```
The table remains available for read and write operations while the index is being dropped. TheDROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.

Renaming an index

ALTER TABLEtbl_name RENAME INDEXold_index_name TOnew_index_name, ALGORITHM=INPLACE, LOCK=NONE;

Adding aFULLTEXT index
```
CREATE FULLTEXT INDEXname ON table(column);
```
Adding the firstFULLTEXT index rebuilds the table if there is no user-definedFTS_DOC_ID column. AdditionalFULLTEXT indexes may be added without rebuilding the table.

Adding aSPATIAL index

CREATE TABLE geom (g GEOMETRY NOT NULL);ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

Changing the index type (USING {BTREE | HASH})

ALTER TABLEtbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INSTANT;

Primary Key Operations

The following table provides an overview of online DDL support for primary key operations. An asterisk indicates additional information, an exception, or a dependency. SeeSyntax and Usage Notes.

Table 17.14 Online DDL Support for Primary Key Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a primary key	No	Yes*	Yes*	Yes	No
Dropping a primary key	No	No	Yes	No	No
Dropping a primary key and adding another	No	Yes	Yes	Yes	No

Syntax and Usage Notes

Adding a primary key
```
ALTER TABLEtbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
```
Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted toNOT NULL.
Restructuring theclustered index always requires copying of table data. Thus, it is best to define theprimary key when you create a table, rather than issuingALTER TABLE ... ADD PRIMARY KEY later.
When you create aUNIQUE orPRIMARY KEY index, MySQL must do some extra work. ForUNIQUE indexes, MySQL checks that the table contains no duplicate values for the key. For aPRIMARY KEY index, MySQL also checks that none of thePRIMARY KEY columns contains aNULL.
When you add a primary key using theALGORITHM=COPY clause, MySQL convertsNULL values in the associated columns to default values: 0 for numbers, an empty string for character-based columns and BLOBs, and 0000-00-00 00:00:00 forDATETIME. This is a non-standard behavior that Oracle recommends you not rely on. Adding a primary key usingALGORITHM=INPLACE is only permitted when theSQL_MODE setting includes thestrict_trans_tables orstrict_all_tables flags; when theSQL_MODE setting is strict,ALGORITHM=INPLACE is permitted, but the statement can still fail if the requested primary key columns containNULL values. TheALGORITHM=INPLACE behavior is more standard-compliant.
If you create a table without a primary key,InnoDB chooses one for you, which can be the firstUNIQUE key defined onNOT NULL columns, or a system-generated key. To avoid uncertainty and the potential space requirement for an extra hidden column, specify thePRIMARY KEY clause as part of theCREATE TABLE statement.
MySQL creates a new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to the temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is renamed with the name of the original table, and the original table is dropped from the database.
The online performance enhancements that apply to operations on secondary indexes do not apply to the primary key index. The rows of an InnoDB table are stored in aclustered index organized based on theprimary key, forming what some database systems call an“index-organized table”. Because the table structure is closely tied to the primary key, redefining the primary key still requires copying the data.
When an operation on the primary key usesALGORITHM=INPLACE, even though the data is still copied, it is more efficient than usingALGORITHM=COPY because:
- No undo logging or associated redo logging is required forALGORITHM=INPLACE. These operations add overhead to DDL statements that useALGORITHM=COPY.
- The secondary index entries are pre-sorted, and so can be loaded in order.
- The change buffer is not used, because there are no random-access inserts into the secondary indexes.
Dropping a primary key
```
ALTER TABLEtbl_name DROP PRIMARY KEY, ALGORITHM=COPY;
```
OnlyALGORITHM=COPY supports dropping a primary key without adding a new one in the sameALTER TABLE statement.
Dropping a primary key and adding another
```
ALTER TABLEtbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
```
Data is reorganized substantially, making it an expensive operation.

Column Operations

The following table provides an overview of online DDL support for column operations. An asterisk indicates additional information, an exception, or a dependency. For details, seeSyntax and Usage Notes.

Table 17.15 Online DDL Support for Column Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes*	Yes	No*	Yes*	Yes
Dropping a column	Yes*	Yes	Yes	Yes	Yes
Renaming a column	Yes*	Yes	No	Yes*	Yes
Reordering columns	No	Yes	Yes	Yes	No
Setting a column default value	Yes	Yes	No	Yes	Yes
Changing the column data type	No	No	Yes	No	No
Extending`VARCHAR` column size	No	Yes	No	Yes	Yes
Dropping the column default value	Yes	Yes	No	Yes	Yes
Changing the auto-increment value	No	Yes	No	Yes	No*
Making a column`NULL`	No	Yes	Yes*	Yes	No
Making a column`NOT NULL`	No	Yes*	Yes*	Yes	No
Modifying the definition of an`ENUM` or`SET` column	Yes	Yes	No	Yes	Yes

Syntax and Usage Notes

Adding a column
```
ALTER TABLEtbl_name ADD COLUMNcolumn_namecolumn_definition, ALGORITHM=INSTANT;
```
INSTANT is the default algorithm in MySQL 9.3.
The following limitations apply when theINSTANT algorithm adds a column:
- A statement cannot combine the addition of a column with otherALTER TABLE actions that do not support theINSTANT algorithm.
- TheINSTANT algorithm can add a column at any position in the table.
- Columns cannot be added to tables that useROW_FORMAT=COMPRESSED, tables with aFULLTEXT index, tables that reside in the data dictionary tablespace, or temporary tables. Temporary tables only supportALGORITHM=COPY.
- MySQL checks the row size when theINSTANT algorithm adds a column, and throws the following error if the addition exceeds the limit.
  ERROR 4092 (HY000): Column can't be added with ALGORITHM=INSTANT as after this max possible row size crosses max permissible row size. Try ALGORITHM=INPLACE/COPY.
- The maximum number of columns in the internal representation of the table cannot exceed 1022 after column addition with theINSTANT algorithm. The error message is:
  ERROR 4158 (HY000): Column can't be added totbl_name with ALGORITHM=INSTANT anymore. Please try ALGORITHM=INPLACE/COPY
- TheINSTANT algorithm can not add or drop columns to system schema tables, such as the internalmysql table.
- A column with a functional index cannot be dropped using theINSTANT algorithm.
Multiple columns may be added in the sameALTER TABLE statement. For example:
```
ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;
```
A new row version is created after eachALTER TABLE ... ALGORITHM=INSTANT operation that adds one or more columns, drops one or more columns, or adds and drops one or more columns in the same operation. TheINFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 0.
```
mysql>  SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES         WHERE NAME LIKE 'test/t1';+---------+--------------------+| NAME    | TOTAL_ROW_VERSIONS |+---------+--------------------+| test/t1 |                  0 |+---------+--------------------+
```
When a table with instantly added or dropped columns is rebuilt by table-rebuildingALTER TABLE orOPTIMIZE TABLE operation, theTOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 255 in MySQL 9.1.0 and later, as each row version requires additional space for table metadata. When the row version limit is reached,ADD COLUMN andDROP COLUMN operations usingALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using theCOPY orINPLACE algorithm.
ERROR 4092 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
The followingINFORMATION_SCHEMA columns provide additional metadata for instantly added columns. Refer to the descriptions of those columns for more information. SeeSection 28.4.9, “The INFORMATION_SCHEMA INNODB_COLUMNS Table”, andSection 28.4.23, “The INFORMATION_SCHEMA INNODB_TABLES Table”.
- INNODB_COLUMNS.DEFAULT_VALUE
- INNODB_COLUMNS.HAS_DEFAULT
- INNODB_TABLES.INSTANT_COLS
Concurrent DML is not permitted when adding anauto-increment column. Data is reorganized substantially, making it an expensive operation. At a minimum,ALGORITHM=INPLACE, LOCK=SHARED is required.
The table is rebuilt ifALGORITHM=INPLACE is used to add a column.
Dropping a column
```
ALTER TABLEtbl_name DROP COLUMNcolumn_name, ALGORITHM=INSTANT;
```
INSTANT is the default algorithm in MySQL 9.3.
The following limitations apply when theINSTANT algorithm is used to drop a column:
- Dropping a column cannot be combined in the same statement with otherALTER TABLE actions that do not supportALGORITHM=INSTANT.
- Columns cannot be dropped from tables that useROW_FORMAT=COMPRESSED, tables with aFULLTEXT index, tables that reside in the data dictionary tablespace, or temporary tables. Temporary tables only supportALGORITHM=COPY.
Multiple columns may be dropped in the sameALTER TABLE statement; for example:
```
ALTER TABLE t1 DROP COLUMN c4, DROP COLUMN c5, ALGORITHM=INSTANT;
```
Each time a column is added or dropped usingALGORITHM=INSTANT, a new row version is created. TheINFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 0.
```
mysql>  SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES         WHERE NAME LIKE 'test/t1';+---------+--------------------+| NAME    | TOTAL_ROW_VERSIONS |+---------+--------------------+| test/t1 |                  0 |+---------+--------------------+
```
When a table with instantly added or dropped columns is rebuilt by table-rebuildingALTER TABLE orOPTIMIZE TABLE operation, theTOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 255 in MySQL 9.1.0 and later, as each row version requires additional space for table metadata. When the row version limit is reached,ADD COLUMN andDROP COLUMN operations usingALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using theCOPY orINPLACE algorithm.
ERROR 4092 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
If an algorithm other thanALGORITHM=INSTANT is used, data is reorganized substantially, making it an expensive operation.
Renaming a column
```
ALTER TABLEtbl CHANGEold_col_namenew_col_namedata_type, ALGORITHM=INSTANT;
```
To permit concurrent DML, keep the same data type and only change the column name.
When you keep the same data type and[NOT] NULL attribute, only changing the column name, the operation can always be performed online.
Renaming a column referenced from another table is only permitted withALGORITHM=INPLACE. If you useALGORITHM=INSTANT,ALGORITHM=COPY, or some other condition that causes the operation to use those algorithms, theALTER TABLE statement fails.
ALGORITHM=INSTANT supports renaming a virtual column;ALGORITHM=INPLACE does not.
ALGORITHM=INSTANT andALGORITHM=INPLACE do not support renaming a column when adding or dropping a virtual column in the same statement. In this case, onlyALGORITHM=COPY is supported.
Reordering columns
To reorder columns, useFIRST orAFTER inCHANGE orMODIFY operations.
```
ALTER TABLEtbl_name MODIFY COLUMNcol_namecolumn_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;
```
Data is reorganized substantially, making it an expensive operation.
Changing the column data type
```
ALTER TABLEtbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;
```
Changing the column data type is only supported withALGORITHM=COPY.
ExtendingVARCHAR column size
```
ALTER TABLEtbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;
```
The number of length bytes required by aVARCHAR column must remain the same. ForVARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. ForVARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-placeALTER TABLE only supports increasingVARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-placeALTER TABLE does not support increasing the size of aVARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to changeVARCHAR column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-placeALTER TABLE returns this error:
```
ALTER TABLEtbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot changecolumn type INPLACE. Try ALGORITHM=COPY.
```
Note
The byte length of aVARCHAR column is dependant on the byte length of the character set.
DecreasingVARCHAR size using in-placeALTER TABLE is not supported. DecreasingVARCHAR size requires a table copy (ALGORITHM=COPY).
Setting a column default value
```
ALTER TABLEtbl_name ALTER COLUMNcol SET DEFAULTliteral, ALGORITHM=INSTANT;
```
Only modifies table metadata. Default column values are stored in thedata dictionary.

Dropping a column default value

ALTER TABLEtbl ALTER COLUMNcol DROP DEFAULT, ALGORITHM=INSTANT;

Changing the auto-increment value
```
ALTER TABLEtable AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;
```
Modifies a value stored in memory, not the data file.
In a distributed system using replication or sharding, you sometimes reset the auto-increment counter for a table to a specific value. The next row inserted into the table uses the specified value for its auto-increment column. You might also use this technique in a data warehousing environment where you periodically empty all the tables and reload them, and restart the auto-increment sequence from 1.
Making a columnNULL
```
ALTER TABLE tbl_name MODIFY COLUMNcolumn_namedata_type NULL, ALGORITHM=INPLACE, LOCK=NONE;
```
Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
Making a columnNOT NULL
```
ALTER TABLEtbl_name MODIFY COLUMNcolumn_namedata_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
```
Rebuilds the table in place.STRICT_ALL_TABLES orSTRICT_TRANS_TABLESSQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. SeeSection 15.1.10, “ALTER TABLE Statement”. Data is reorganized substantially, making it an expensive operation.
Modifying the definition of anENUM orSET column
```
CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INSTANT;
```
Modifying the definition of anENUM orSET column by adding new enumeration or set members to theend of the list of valid member values may be performed instantly or in place, as long as the storage size of the data type does not change. For example, adding a member to aSET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.

Generated Column Operations

The following table provides an overview of online DDL support for generated column operations. For details, seeSyntax and Usage Notes.

Table 17.16 Online DDL Support for Generated Column Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a`STORED` column	No	No	Yes	No	No
Modifying`STORED` column order	No	No	Yes	No	No
Dropping a`STORED` column	No	Yes	Yes	Yes	No
Adding a`VIRTUAL` column	Yes	Yes	No	Yes	Yes
Modifying`VIRTUAL` column order	No	No	Yes	No	No
Dropping a`VIRTUAL` column	Yes	Yes	No	Yes	Yes

Syntax and Usage Notes

Adding aSTORED column
```
ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;
```
ADD COLUMN is not an in-place operation for stored columns (done without using a temporary table) because the expression must be evaluated by the server.

ModifyingSTORED column order

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;

Rebuilds the table in place.

Dropping aSTORED column

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

Rebuilds the table in place.

Adding aVIRTUAL column
```
ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INSTANT;
```
Adding a virtual column can be performed instantly or in place for non-partitioned tables.
Adding aVIRTUAL is not an in-place operation for partitioned tables.

ModifyingVIRTUAL column order

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;

Dropping aVIRTUAL column
```
ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
```
Dropping aVIRTUAL column can be performed instantly or in place for non-partitioned tables.

Foreign Key Operations

The following table provides an overview of online DDL support for foreign key operations. An asterisk indicates additional information, an exception, or a dependency. For details, seeSyntax and Usage Notes.

Table 17.17 Online DDL Support for Foreign Key Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a foreign key constraint	No	Yes*	No	Yes	Yes
Dropping a foreign key constraint	No	Yes	No	Yes	Yes

Syntax and Usage Notes

Adding a foreign key constraint
TheINPLACE algorithm is supported whenforeign_key_checks is disabled. Otherwise, only theCOPY algorithm is supported.
```
ALTER TABLEtbl1 ADD CONSTRAINTfk_name FOREIGN KEYindex (col1)  REFERENCEStbl2(col2)referential_actions;
```
Dropping a foreign key constraint
```
ALTER TABLEtbl DROP FOREIGN KEYfk_name;
```
Dropping a foreign key can be performed online with theforeign_key_checks option enabled or disabled.
If you do not know the names of the foreign key constraints on a particular table, issue the following statement and find the constraint name in theCONSTRAINT clause for each foreign key:
```
SHOW CREATE TABLEtable\G
```
Or, query the Information SchemaTABLE_CONSTRAINTS table and use theCONSTRAINT_NAME andCONSTRAINT_TYPE columns to identify the foreign key names.
You can also drop a foreign key and its associated index in a single statement:
```
ALTER TABLEtable DROP FOREIGN KEYconstraint, DROP INDEXindex;
```

Note

Ifforeign keys are already present in the table being altered (that is, it is achild table containing aFOREIGN KEY ... REFERENCE clause), additional restrictions apply to online DDL operations, even those not directly involving the foreign key columns:

AnALTER TABLE on the child table could wait for another transaction to commit, if a change to the parent table causes associated changes in the child table through anON UPDATE orON DELETE clause using theCASCADE orSET NULL parameters.
In the same way, if a table is theparent table in a foreign key relationship, even though it does not contain anyFOREIGN KEY clauses, it could wait for theALTER TABLE to complete if anINSERT,UPDATE, orDELETE statement causes anON UPDATE orON DELETE action in the child table.

Table Operations

The following table provides an overview of online DDL support for table operations. An asterisk indicates additional information, an exception, or a dependency. For details, seeSyntax and Usage Notes.

Table 17.18 Online DDL Support for Table Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Changing the`ROW_FORMAT`	No	Yes	Yes	Yes	No
Changing the`KEY_BLOCK_SIZE`	No	Yes	Yes	Yes	No
Setting persistent table statistics	No	Yes	No	Yes	Yes
Specifying a character set	No	Yes	Yes*	Yes	No
Converting a character set	No	No	Yes*	No	No
Optimizing a table	No	Yes*	Yes	Yes	No
Rebuilding with the`FORCE` option	No	Yes*	Yes	Yes	No
Performing a null rebuild	No	Yes*	Yes	Yes	No
Renaming a table	Yes	Yes	No	Yes	Yes

Syntax and Usage Notes

Changing theROW_FORMAT
```
ALTER TABLEtbl_name ROW_FORMAT =row_format, ALGORITHM=INPLACE, LOCK=NONE;
```
Data is reorganized substantially, making it an expensive operation.
For additional information about theROW_FORMAT option, seeTable Options.
Changing theKEY_BLOCK_SIZE
```
ALTER TABLEtbl_name KEY_BLOCK_SIZE =value, ALGORITHM=INPLACE, LOCK=NONE;
```
Data is reorganized substantially, making it an expensive operation.
For additional information about theKEY_BLOCK_SIZE option, seeTable Options.
Setting persistent table statistics options
```
ALTER TABLEtbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;
```
Only modifies table metadata.
Persistent statistics includeSTATS_PERSISTENT,STATS_AUTO_RECALC, andSTATS_SAMPLE_PAGES. For more information, seeSection 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.
Specifying a character set
```
ALTER TABLEtbl_name CHARACTER SET =charset_name, ALGORITHM=INPLACE, LOCK=NONE;
```
Rebuilds the table if the new character encoding is different.
Converting a character set
```
ALTER TABLEtbl_name CONVERT TO CHARACTER SETcharset_name, ALGORITHM=COPY;
```
Rebuilds the table if the new character encoding is different.
Optimizing a table
```
OPTIMIZE TABLEtbl_name;
```
In-place operation is not supported for tables withFULLTEXT indexes. The operation uses theINPLACE algorithm, butALGORITHM andLOCK syntax is not permitted.
Rebuilding a table with theFORCE option
```
ALTER TABLEtbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;
```
UsesALGORITHM=INPLACE as of MySQL 5.6.17.ALGORITHM=INPLACE is not supported for tables withFULLTEXT indexes.
Performing a "null" rebuild
```
ALTER TABLEtbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
```
UsesALGORITHM=INPLACE as of MySQL 5.6.17.ALGORITHM=INPLACE is not supported for tables withFULLTEXT indexes.
Renaming a table
```
ALTER TABLEold_tbl_name RENAME TOnew_tbl_name, ALGORITHM=INSTANT;
```
Renaming a table can be performed instantly or in place. MySQL renames files that correspond to the tabletbl_name without making a copy. (You can also use theRENAME TABLE statement to rename tables. SeeSection 15.1.39, “RENAME TABLE Statement”.) Privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.

Tablespace Operations

The following table provides an overview of online DDL support for tablespace operations. For details, seeSyntax and Usage Notes.

Table 17.19 Online DDL Support for Tablespace Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Renaming a general tablespace	No	Yes	No	Yes	Yes
Enabling or disabling general tablespace encryption	No	Yes	No	Yes	No
Enabling or disabling file-per-table tablespace encryption	No	No	Yes	No	No

Syntax and Usage Notes

Renaming a general tablespace
```
ALTER TABLESPACEtablespace_name RENAME TOnew_tablespace_name;
```
ALTER TABLESPACE ... RENAME TO uses theINPLACE algorithm but does not support theALGORITHM clause.
Enabling or disabling general tablespace encryption
```
ALTER TABLESPACEtablespace_name ENCRYPTION='Y';
```
ALTER TABLESPACE ... ENCRYPTION uses theINPLACE algorithm but does not support theALGORITHM clause.
For related information, seeSection 17.13, “InnoDB Data-at-Rest Encryption”.
Enabling or disabling file-per-table tablespace encryption
```
ALTER TABLEtbl_name ENCRYPTION='Y', ALGORITHM=COPY;
```
For related information, seeSection 17.13, “InnoDB Data-at-Rest Encryption”.

Partitioning Operations

With the exception of someALTER TABLE partitioning clauses, online DDL operations for partitionedInnoDB tables follow the same rules that apply to regularInnoDB tables.

SomeALTER TABLE partitioning clauses do not go through the same internal online DDL API as regular non-partitionedInnoDB tables. As a result, online support forALTER TABLE partitioning clauses varies.

The following table shows the online status for eachALTER TABLE partitioning statement. Regardless of the online DDL API that is used, MySQL attempts to minimize data copying and locking where possible.

ALTER TABLE partitioning options that useALGORITHM=COPY or that only permit“ALGORITHM=DEFAULT, LOCK=DEFAULT”, repartition the table using theCOPY algorithm. In other words, a new partitioned table is created with the new partitioning scheme. The newly created table includes any changes applied by theALTER TABLE statement, and table data is copied into the new table structure.

Table 17.20 Online DDL Support for Partitioning Operations

Partitioning Clause	Instant	In Place	Permits DML	Notes
`PARTITION BY`	No	No	No	Permits`ALGORITHM=COPY`,`LOCK={DEFAULT\|SHARED\|EXCLUSIVE}`
`ADD PARTITION`	No	Yes*	Yes*	`ALGORITHM=INPLACE, LOCK={DEFAULT\|NONE\|SHARED\|EXCLUSISVE}` is supported for`RANGE` and`LIST` partitions,`ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSISVE}` for`HASH` and`KEY` partitions, and`ALGORITHM=COPY, LOCK={SHARED\|EXCLUSIVE}` for all partition types. Does not copy existing data for tables partitioned by`RANGE` or`LIST`. Concurrent queries are permitted with`ALGORITHM=COPY` for tables partitioned by`HASH` or`LIST`, as MySQL copies the data while holding a shared lock.
`DROP PARTITION`	No	Yes*	Yes*	`ALGORITHM=INPLACE, LOCK={DEFAULT\|NONE\|SHARED\|EXCLUSIVE}` is supported. Does not copy data for tables partitioned by`RANGE` or`LIST`. `DROP PARTITION` with`ALGORITHM=INPLACE` deletes data stored in the partition and drops the partition. However,`DROP PARTITION` with`ALGORITHM=COPY` or`old_alter_table=ON` rebuilds the partitioned table and attempts to move data from the dropped partition to another partition with a compatible`PARTITION ... VALUES` definition. Data that cannot be moved to another partition is deleted.
`DISCARD PARTITION`	No	No	No	Only permits`ALGORITHM=DEFAULT`,`LOCK=DEFAULT`
`IMPORT PARTITION`	No	No	No	Only permits`ALGORITHM=DEFAULT`,`LOCK=DEFAULT`
`TRUNCATE PARTITION`	No	Yes	Yes	Does not copy existing data. It merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.
`COALESCE PARTITION`	No	Yes*	No	`ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}` is supported.
`REORGANIZE PARTITION`	No	Yes*	No	`ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}` is supported.
`EXCHANGE PARTITION`	No	Yes	Yes
`ANALYZE PARTITION`	No	Yes	Yes
`CHECK PARTITION`	No	Yes	Yes
`OPTIMIZE PARTITION`	No	No	No	`ALGORITHM` and`LOCK` clauses are ignored. Rebuilds the entire table. SeeSection 26.3.4, “Maintenance of Partitions”.
`REBUILD PARTITION`	No	Yes*	No	`ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}` is supported.
`REPAIR PARTITION`	No	Yes	Yes
`REMOVE PARTITIONING`	No	No	No	Permits`ALGORITHM=COPY`,`LOCK={DEFAULT\|SHARED\|EXCLUSIVE}`

Non-partitioning onlineALTER TABLE operations on partitioned tables follow the same rules that apply to regular tables. However,ALTER TABLE performs online operations on each table partition, which causes increased demand on system resources due to operations being performed on multiple partitions.

For additional information aboutALTER TABLE partitioning clauses, seePartitioning Options, andSection 15.1.10.1, “ALTER TABLE Partition Operations”. For information about partitioning in general, seeChapter 26,Partitioning.

PREV HOME UP NEXT