DDL operations

This topic describes common DDL operations in PolarDB for MySQL. You can refer to this topic to assess the risks of executing specific DDL operations and avoid their impact on your business.

Overview

In MySQL, DDL operations are a set of complex operations on indexes, primary keys, columns, tables, foreign keys, and generated columns. DDL operations not only consume a large amount of time and hardware resources, but also may lock tables. Inappropriate use may severely impact your business.

After years of development, PolarDB for MySQL has made great progress in terms of DDL performance and lock stability. This topic describes common DDL operations in various versions of PolarDB for MySQL in the following aspects:

Whether an operation permits concurrent DML: A DDL operation that locks table does not permit concurrent DML operations. However, if a DDL operation supports the online DDL feature, it locks tables only during the modification of metadata, which mostly lasts for less than one second, and allows for read and write operations on the tables when changing the table schema. This improves the responsiveness and the availability of the tables in production environments. In contrast, DDL operations that do not support the online DDL feature lock tables throughout the entire execution process, which prevents concurrent write operations. If the execution of such a DDL operation lasts for an extended period, a significant impact may be caused on your business.
Whether an operation rebuilds the table: An operation that rebuilds the table takes a long period of time, because it involves recreating the primary key and all secondary indexes based on the new table schema.
Note
Because PolarDB for MySQL supports parallel DDL, performing DDL operations on the cluster engine has better performance than using third-party tools such as gh-ost and pt-osc.
Whether an operation modifies metadata only: When a DDL operation only modifies metadata but not data in the table, it is executed instantly irrespective of the table size.
Whether parallel DDL is supported: In cases such as large table indexing and table rebuild, PolarDB for MySQL supports parallel execution of DDL operations and uses multiple threads to accelerate this process. This improves the performance of DDL operations by up to 15 to 20 times. For more information, see Parallel DDL.
Whether an operation affects performance (lock-free DDL): If you perform lock-free DDL operations, no transient connections occur. They do not interrupt your business during off-peak hours.
Note
We recommend that you perform such operations during off-peak hours. They may increase IOPS and CPU utilization.

DDL algorithms

PolarDB for MySQL supports the following DDL algorithms:

INSTANT: With ALGORITHM set to INSTANT, a DDL operation only modifies the metadata in the data dictionary. It does not modify or replicate data, nor does it recreate the table. Therefore, it is not affected by table size and is completed instantly.
INPLACE: With ALGORITHM set to INPLACE, data replication and reorganization are run by the cluster engine, making the execution of DDL operations more efficient. Most DDL operations would permit concurrent reads and writes, minimizing impact on your business. In addition, some DDL operations such as RENAME TABLE and ADD COMMENT only modifies the table's metadata and thus can be completed instantly.
COPY: With ALGORITHM set to COPY, a DDL operation replicates all of the table's data into a new table. During data replication, the SNW (SHARED_NO_WRITE) lock of the original table is held. Therefore, only reads are supported during execution of DDL operations. Concurrent writes are not allowed, which has a significant impact on your business.

DDL operations that permit concurrent reads and writes are referred to as online DDL. Online DDL has a relatively small impact on business activities. In most cases, you do not need to manually specify a DDL algorithm. PolarDB automatically selects the optimal algorithm by sequentially trying INSTANT, INPLACE, and COPY. You can specify the ALGORITHM and LOCK clauses of the ALTER TABLE statement to manage DDL operations in a fine-grained way.

ALGORITHM clause: Executes a DDL operation with a specified algorithm. Valid values are as follows: DEFAULT, INSTANT, INPLACE, and COPY. If a DDL operation does not support the specified ALGORITHM value, an error is returned.
LOCK clause: Controls concurrent access to the table during DDL execution. You can use the LOCK clause to control concurrent reads and writes when a table is being changed. Valid values are as follows:
- DEFAULT: Permits as many concurrent reads and writes as is permitted for specific DDL operations.
- NONE: Permits concurrent reads and writes during DDL execution. If such level of concurrent access is not permitted by a DDL operation, an error is returned.
- SHARED: Allows concurrent reads but blocks writes. If concurrent reads are not permitted, an error is returned.
- EXCLUSIVE: Blocks all concurrent reads and writes during DDL execution.

To ensure the availability of the table during the execution of ALTER TABLE, use the LOCK clause. If a DDL operation requires a more restrictive level of locking than is specified by the LOCK clause, the DDL operation will stop immediately.

Index operations

PolarDB for MySQL 8.0.2

PolarDB for MySQL 8.0.1

PolarDB for MySQL 5.7

PolarDB for MySQL 5.6

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Create a secondary index	Yes	No	No	Yes
Drop a secondary index	Yes	No	Yes	N/A
Rename a secondary index	Yes	No	Yes	N/A
Add a FULLTEXT index	No	No Note When the first full-text index is being created on a table, the absence of a user-defined FTS_DOC_ID column results in an extra table rebuild.	No	No
Add a SPATIAL index	No	No	No	No

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Create a secondary index	Yes	No	No	Yes
Drop a secondary index	Yes	No	Yes	N/A
Rename a secondary index	Yes	No	Yes	N/A
Add a FULLTEXT index	No	No Note When the first full-text index is being created on a table, the absence of a user-defined FTS_DOC_ID column results in an extra table rebuild.	No	No
Add a SPATIAL index	No	No	No	No

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Create a secondary index	Yes	No	No	Yes
Drop a secondary index	Yes	No	Yes	N/A
Rename a secondary index	Yes	No	Yes	N/A
Add a FULLTEXT index	No	No Note When the first full-text index is being created on a table, the absence of a user-defined FTS_DOC_ID column results in an extra table rebuild.	No	No
Add a SPATIAL index	No	No	No	No

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Create a secondary index	Yes	No	No	No
Drop a secondary index	Yes	No	Yes	N/A
Rename a secondary index	Yes	No	Yes	N/A
Add a FULLTEXT index	No	No Note When the first full-text index is being created on a table, the absence of a user-defined FTS_DOC_ID column results in an extra table rebuild.	No	No
Add a SPATIAL index	No	No	No	No

Primary key operations

PolarDB for MySQL 8.0.2

PolarDB for MySQL 8.0.1

PolarDB for MySQL 5.7

PolarDB for MySQL 5.6

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Create a primary key	Yes	Yes	No	Yes
Drop a primary key	No	Yes	No	No
Drop a primary key and add another	Yes	Yes	No	Yes

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Create a primary key	Yes	Yes	No	Yes
Drop a primary key	No	Yes	No	No
Drop a primary key and add another	Yes	Yes	No	Yes

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Create a primary key	Yes	Yes	No	Yes
Drop a primary key	No	Yes	No	No
Drop a primary key and add another	Yes	Yes	No	Yes

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Create a primary key	Yes	Yes	No	No
Drop a primary key	No	Yes	No	No
Drop a primary key and add another	Yes	Yes	No	No

Column operations

PolarDB for MySQL 8.0.2

PolarDB for MySQL 8.0.1

PolarDB for MySQL 5.7

PolarDB for MySQL 5.6

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a column	Yes	No¹	Yes¹	Yes¹
Delete a column	Yes	Yes	No	Yes
Rename a field	Yes	No	Yes	N/A
Reorder columns	Yes	Yes	No	Yes
Specify a column default value	Yes	No	Yes	N/A
Modify a column comment	Yes	No	Yes	N/A
Modify the column data type	No	Yes	No	No
Extend VARCHAR column length	Yes²	No	Yes	N/A
Change the character set from UTF8mb3 to UTF8mb4	No	No³	Yes³	No
Drop the column default value	Yes	No	Yes	N/A
Modify the auto-increment value	Yes	No	Yes	N/A
Make a column NULL	Yes	Yes	No	Yes
Make a column NOT NULL	Yes	Yes	No	Yes
Modify the definition of an ENUM or SET column	Yes	No	Yes⁴	N/A

The instant ADD COLUMN feature allows you to add a column only as the last column of the table. If no primary key is specified for the table, make sure that you set the implicit_primary_key parameter to OFF. This prevents failure to add a column caused by an implicit primary key column at the end of the table. The instant ADD COLUMN feature is not supported on compressed tables (ROW_FORMAT=COMPRESSED), tables that have full-text indexes, or temporary tables. If a cluster does not support the instant ADD COLUMN feature, the system adds a column by using the INPLACE algorithm. This triggers the rebuilding of the table. During the table rebuilding process, concurrent read and write operations are allowed. You can use the parallel DDL feature to accelerate column addition.
If you add a column to a table that contains In-memory Column indexes (IMCIs), the IMCIs are rebuilt. Therefore, the instant ADD column feature is not supported for tables that contain IMCIs. The solution is to enable the instant ADD COLUMN feature by setting loose_imci_enable_add_column_instant_ddl to ON. This way, the IMCIs are rebuilt asynchronously in the background by PolarDB and become temporarily unavailable. For more information, see Execute DDL statements to dynamically create and delete an IMCI.
To instantly increase the length of a VARCHAR column, make sure that the number of bytes used to store VARCHAR column values remains the same. A VARCHAR column whose size is within the range of 0 to 255 bytes requires 1 byte of storage. A VARCHAR column whose size is 256 bytes or longer requires 2 bytes of storage. Only when you control the length of a VARCHAR column within the same range (0 to 255 bytes or 256 bytes or longer), the storage requirements remain the same. In these scenarios, only the metadata is updated when the ALTER TABLE statement is executed. If the length exceeds the 255-byte threshold, PolarDB automatically uses the COPY algorithm. This process involves table locking, during which only read operations are allowed.
When you are unsure whether the modified VARCHAR column length is within the same byte-size range, use the ALGORITHM=INPLACE clause for the DDL operation. If instant column extension is not supported, an error is returned. Example:
```
ALTER TABLE table_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);

ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
The space consumed by a VARCHAR value varies based on the actual length. Therefore, we recommend that you set the maximum length of a VARCHAR column to 256 bytes or longer to avoid a table copy (ALGORITHM=COPY) in column length extension.
If the following conditions are met, the character set of a column is changed from UTF8mb3 to UTF8mb4, and only the metadata is modified. If the conditions are not met, the COPY algorithm is used. This results in the locking and rebuilding of the table, during which only read operations are allowed.
- The column type is CHAR, VARCHAR, ENUM, or TEXT.
- No index exists on the column that you want to modify.
- The maximum length of the column remains consistent, either up to 255 bytes or greater than 255 bytes.
You can specify ALGORITHM=INPLACE to force DDL execution without the need for table rebuilding. If the DDL operation can be executed only by using the COPY algorithm, an error is returned. Example:
```
ALTER TABLE test modify column b char(1) CHARACTER SET utf8mb4 default null,algorithm = inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
Modifying the definition of a ENUM or SET column changes metadata only when the following conditions are met: the storage size of the data type does not change, and members are added to the end of the list. Otherwise, a table copy is required.

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a column	Yes	No¹	Yes¹	Yes¹
Delete a column	Yes	Yes	No	Yes
Rename a field	Yes	No	Yes	N/A
Reorder columns	Yes	Yes	No	Yes
Specify a column default value	Yes	No	Yes	N/A
Modify a column comment	Yes	No	Yes	N/A
Modify the column data type	No	Yes	No	No
Extend VARCHAR column length	Yes²	No	Yes	N/A
Change the character set from UTF8mb3 to UTF8mb4	No	No³	Yes³	No
Drop the column default value	Yes	No	Yes	N/A
Modify the auto-increment value	Yes	No	Yes	N/A
Make a column NULL	Yes	Yes	No	Yes
Make a column NOT NULL	Yes	Yes	No	Yes
Modify the definition of an ENUM or SET column	Yes	No	Yes⁴	N/A

The instant ADD COLUMN feature allows you to add a column only as the last column of the table. If no primary key is specified for the table, make sure that you set the implicit_primary_key parameter to OFF. This prevents failure to add a column caused by an implicit primary key column at the end of the table. The instant ADD COLUMN feature is not supported on compressed tables (ROW_FORMAT=COMPRESSED), tables that have full-text indexes, or temporary tables. If a cluster does not support the instant ADD COLUMN feature, the system adds a column by using the INPLACE algorithm. This triggers the rebuilding of the table. During the table rebuilding process, concurrent read and write operations are allowed. You can use the parallel DDL feature to accelerate column addition.
If you add a column to a table that contains In-memory Column indexes (IMCIs), the IMCIs are rebuilt. Therefore, the instant ADD column feature is not supported for tables that contain IMCIs. The solution is to enable the instant ADD COLUMN feature by setting loose_imci_enable_add_column_instant_ddl to ON. This way, the IMCIs are rebuilt asynchronously in the background by PolarDB and become temporarily unavailable. For more information, see Execute DDL statements to dynamically create and delete an IMCI.
To instantly increase the length of a VARCHAR column, make sure that the number of bytes used to store VARCHAR column values remains the same. A VARCHAR column whose size is within the range of 0 to 255 bytes requires 1 byte of storage. A VARCHAR column whose size is 256 bytes or longer requires 2 bytes of storage. Only when you control the length of a VARCHAR column within the same range (0 to 255 bytes or 256 bytes or longer), the storage requirements remain the same. In these scenarios, only the metadata is updated when the ALTER TABLE statement is executed. If the length exceeds the 255-byte threshold, PolarDB automatically uses the COPY algorithm. This process involves table locking, during which only read operations are allowed.
When you are unsure whether the modified VARCHAR column length is within the same byte-size range, use the ALGORITHM=INPLACE clause for the DDL operation. If instant column extension is not supported, an error is returned. Example:
```
ALTER TABLE table_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);

ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
The space consumed by a VARCHAR value varies based on the actual length. Therefore, we recommend that you set the maximum length of a VARCHAR column to 256 bytes or longer to avoid a table copy (ALGORITHM=COPY) in column length extension.
When loose_innodb_support_instant_modify_charset is set to ON and the following conditions are met, you can change the character set of a column from UTF8mb3 to UTF8mb4 with only metadata being modified. If the conditions are not met, the COPY algorithm is used. This results in the locking and rebuilding of the table, during which only read operations are allowed.
- The column type is CHAR, VARCHAR, ENUM, or TEXT.
- No index exists on the column that you want to modify.
- The maximum length of the column remains consistent, either up to 255 bytes or greater than 255 bytes.
You can specify ALGORITHM=INPLACE to force DDL execution without the need for table rebuilding. If the DDL operation can be executed only by using the COPY algorithm, an error is returned. Example:
```
ALTER TABLE test modify column b char(1) CHARACTER SET utf8mb4 default null,algorithm = inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
Modifying the definition of a ENUM or SET column changes metadata only when the following conditions are met: the storage size of the data type does not change, and members are added to the end of the list. Otherwise, a table copy is required.

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a column	Yes	No¹	Yes¹	Yes¹
Delete a column	Yes	Yes	No	Yes
Rename a field	Yes	No	Yes	N/A
Reorder columns	Yes	Yes	No	Yes
Specify a column default value	Yes	No	Yes	N/A
Modify a column comment	Yes	No	Yes	N/A
Modify the column data type	No	Yes	No	No
Extend VARCHAR column length	Yes²	No	Yes	N/A
Change the character set from UTF8mb3 to UTF8mb4	No	Yes	No	No
Drop the column default value	Yes	No	Yes	N/A
Modify the auto-increment value	Yes	No	Yes	N/A
Make a column NULL	Yes	Yes	No	Yes
Make a column NOT NULL	Yes	Yes	No	Yes
Modify the definition of an ENUM or SET column	Yes	No	Yes³	N/A

To enable the Instant ADD COLUMN feature, set loose_innodb_support_instant_add_column to ON. This feature allows you to add a column only as the last column of the table. If no primary key is specified for the table, make sure that you set the implicit_primary_key parameter to OFF. This prevents failure to add a column caused by an implicit primary key column at the end of the table. The instant ADD COLUMN feature is not supported on compressed tables (ROW_FORMAT=COMPRESSED), tables that have full-text indexes, or temporary tables. If a cluster does not support the instant ADD COLUMN feature, the system adds a column by using the INPLACE algorithm. This triggers the rebuilding of the table. During the table rebuilding process, concurrent read and write operations are allowed. You can use the parallel DDL feature to accelerate column addition.
To instantly increase the length of a VARCHAR column, make sure that the number of bytes used to store VARCHAR column values remains the same. A VARCHAR column whose size is within the range of 0 to 255 bytes requires 1 byte of storage. A VARCHAR column whose size is 256 bytes or longer requires 2 bytes of storage. Only when you control the length of a VARCHAR column within the same range (0 to 255 bytes or 256 bytes or longer), the storage requirements remain the same. In these scenarios, only the metadata is updated when the ALTER TABLE statement is executed. If the length exceeds the 255-byte threshold, PolarDB automatically uses the COPY algorithm. This process involves table locking, during which only read operations are allowed.
When you are unsure whether the modified VARCHAR column length is within the same byte-size range, use the ALGORITHM=INPLACE clause for the DDL operation. If instant column extension is not supported, an error is returned. Example:
```
ALTER TABLE table_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);

ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
The space consumed by a VARCHAR value varies based on the actual length. Therefore, we recommend that you set the maximum length of a VARCHAR column to 256 bytes or longer to avoid a table copy (ALGORITHM=COPY) in column length extension.
Modifying the definition of a ENUM or SET column changes metadata only when the following conditions are met: the storage size of the data type does not change, and members are added to the end of the list. Otherwise, a table copy is required.

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a column	Yes	No¹	Yes¹	No
Delete a column	Yes	Yes	No	No
Rename a field	Yes	No	Yes	N/A
Reorder columns	Yes	Yes	No	No
Specify a column default value	Yes	No	Yes	N/A
Modify a column comment	Yes	No	Yes	N/A
Modify the column data type	No	Yes	No	No
Extend VARCHAR column length	No	Yes	No	No
Change the character set from UTF8mb3 to UTF8mb4	No	Yes	No	No
Drop the column default value	Yes	No	Yes	N/A
Modify the auto-increment value	Yes	No	Yes	N/A
Make a column NULL	Yes	Yes	No	No
Make a column NOT NULL	Yes	Yes	No	No
Modify the definition of an ENUM or SET column	Yes	No	Yes²	N/A

To enable the Instant ADD COLUMN feature, set loose_innodb_support_instant_add_column to ON. This feature allows you to add a column only as the last column of the table. If no primary key is specified for the table, make sure that you set the implicit_primary_key parameter to OFF. This prevents failure to add a column caused by an implicit primary key column at the end of the table. The instant ADD COLUMN feature is not supported on compressed tables (ROW_FORMAT=COMPRESSED), tables that have full-text indexes, temporary tables, or partitioned tables. If a cluster does not support the instant ADD COLUMN feature, the system adds a column by using the INPLACE algorithm. This triggers the rebuilding of the table. During the table rebuilding process, concurrent read and write operations are allowed.
Note
For PolarDB for MySQL 5.6, the instant ADD COLUMN feature is in canary release. To use this feature, go to Quota Center, find the quota name corresponding to the Quota ID polardb_mysql_iac_56, and then click Apply.
Modifying the definition of a ENUM or SET column changes metadata only when the following conditions are met: the storage size of the data type does not change, and members are added to the end of the list. Otherwise, a table copy is required.

Table operations

PolarDB for MySQL 8.0.2

PolarDB for MySQL 8.0.1

PolarDB for MySQL 5.7

PolarDB for MySQL 5.6

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Modify the ROW_FORMAT attribute	Yes	Yes	No	Yes
Modify the KEY_BLOCK_SIZE attribute	Yes	Yes	No	Yes
Configure persistent statistics	Yes	No	Yes	N/A
Specify a character set	Yes	No	Yes	N/A
Change the character set	No	Yes	No	No
Optimize a table	Yes	Yes Note An in-place defragmentation operation using the command `ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE` is not applicable to a table that contains a full-text index.	No	Yes
Rebuild a table	Yes	Yes	No	Yes
Rename a table	Yes	No	Yes	N/A
Modify a table comment	Yes	No	Yes	N/A

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Modify the ROW_FORMAT attribute	Yes	Yes	No	Yes
Modify the KEY_BLOCK_SIZE attribute	Yes	Yes	No	Yes
Configure persistent statistics	Yes	No	Yes	N/A
Specify a character set	Yes	No	Yes	N/A
Change the character set	No	Yes	No	No
Optimize a table	Yes	Yes Note An in-place defragmentation operation using the command `ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE` is not applicable to a table that contains a full-text index.	No	Yes
Rebuild a table	Yes	Yes	No	Yes
Rename a table	Yes	No	Yes	N/A
Modify a table comment	Yes	No	Yes	N/A

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Modify the ROW_FORMAT attribute	Yes	Yes	No	Yes
Modify the KEY_BLOCK_SIZE attribute	Yes	Yes	No	Yes
Configure persistent statistics	Yes	No	Yes	N/A
Specify a character set	Yes	No	Yes	N/A
Change the character set	No	Yes	No	No
Optimize a table	Yes	Yes Note An in-place defragmentation operation using the command `ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE` is not applicable to a table that contains a full-text index.	No	Yes
Rebuild a table	Yes	Yes	No	Yes
Rename a table	Yes	No	Yes	N/A
Modify a table comment	Yes	No	Yes	N/A

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Modify the ROW_FORMAT attribute	Yes	Yes	No	No
Modify the KEY_BLOCK_SIZE attribute	Yes	Yes	No	No
Configure persistent statistics	Yes	No	Yes	N/A
Specify a character set	Yes	No	Yes	N/A
Change the character set	No	Yes	No	No
Optimize a table	Yes	Yes Note An in-place defragmentation operation using the command `ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE` is not applicable to a table that contains a full-text index.	No	No
Rebuild a table	Yes	Yes	No	No
Rename a table	Yes	No	Yes	N/A
Modify a table comment	Yes	No	Yes	N/A

Generated column operations

PolarDB for MySQL 8.0.2

PolarDB for MySQL 8.0.1

PolarDB for MySQL 5.7

PolarDB for MySQL 5.6

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a STORED column	No Note Adding a STORED column through Online DDL is not supported because the operation involves the SQL/Server layer.	Yes	No	No
Modify STORED column order	No	Yes	No	No
Drop a STORED column	Yes	Yes	No	Yes
Add a VIRTUAL column	Yes	No	Yes	N/A
Modify the order of VIRTUAL columns	No	Yes	No	No
Drop a VIRTUAL column	Yes	No	Yes	N/A

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a STORED column	No Note Adding a STORED column through Online DDL is not supported because the operation involves the SQL/Server layer.	Yes	No	No
Modify STORED column order	No	Yes	No	No
Drop a STORED column	Yes	Yes	No	Yes
Add a VIRTUAL column	Yes	No	Yes	N/A
Modify the order of VIRTUAL columns	No	Yes	No	No
Drop a VIRTUAL column	Yes	No	Yes	N/A

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a STORED column	No Note Adding a STORED column through Online DDL is not supported because the operation involves the SQL/Server layer.	Yes	No	No
Modify STORED column order	No	Yes	No	No
Drop a STORED column	Yes	Yes	No	Yes
Add a VIRTUAL column	Yes	No	Yes	N/A
Modify the order of VIRTUAL columns	No	Yes	No	No
Drop a VIRTUAL column	Yes	No	Yes	N/A

PolarDB for MySQL 5.6 does not support generated column operations.

Foreign key operations

PolarDB for MySQL 8.0.2

PolarDB for MySQL 8.0.1

PolarDB for MySQL 5.7

PolarDB for MySQL 5.6

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a foreign key	Yes¹	No¹	Yes¹	N/A
Drop a foreign key	Yes¹	No¹	Yes¹	N/A

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a foreign key	Yes¹	No¹	Yes¹	N/A
Drop a foreign key	Yes¹	No¹	Yes¹	N/A

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a foreign key	Yes¹	No¹	Yes¹	N/A
Drop a foreign key	Yes¹	No¹	Yes¹	N/A

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a foreign key	Yes¹	No¹	Yes¹	N/A
Drop a foreign key	Yes¹	No¹	Yes¹	N/A

1. In-place foreign key operations are supported when the foreign_key_checks option is disabled and only metadata is modified. When those conditions are not met, in-place foreign key operations require table copying and locking.

Partitioning operations

PolarDB for MySQL 8.0.2

PolarDB for MySQL 8.0.1

PolarDB for MySQL 5.7

PolarDB for MySQL 5.6

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a partition	Yes¹	No²	Yes	No
Drop a partition	Yes¹	No²	No	No
Discard the space of a partitioned table	No	No	No	No
Import the space of a partitioned table	No	No	No	No
Truncate a partition	Yes	No	No	No
Coalesce partitions	No	Yes³	No	No
Reorganize partitions	Yes¹	No⁷	No	No
Exchange partitions	Yes¹	No	Yes	No
Analyze a partition	Yes	No	No⁸	No
Check a partition	Yes	No	No ⁹	No
Optimize a partition	Yes⁴	Yes⁴	No	Yes⁴
Rebuild a partition	Yes¹	No⁷	No	No
Repair a partition	Yes	No¹⁰	No	No
Partition a regular table	No	Yes	Yes⁵	No
Remove partitioning	No	Yes	No	No
Create a partial index	Yes	No⁶	No	Yes

After setting loose_partition_level_mdl_enabled to true to enable metadata locking (MDL) for partitions, DDL operations do not affect DML operations on irrelevant partitions. For more information, see Online partition maintenance.
Using ADD PARTITION or DROP PARTITION with RANGE or LIST partitions does not require a table rebuild. Using ADD PARTITION or DROP PARTITION with HASH or KEY partitions requires a table rebuild. DROP PARTITION cannot be used with HASH or KEY partitions.
COALESCE PARTITION can only be used with HASH and KEY partitions.
Performing OPTIMIZE PARTITION on an InnoDB table triggers the rebuilding of the entire partitioned table. Read and write operations are permitted during table rebuilding. To accelerate table rebuilding by using parallel DDL, set the innodb_parallel_build_primary_index parameter to ON. For more information, see Parallel DDL.
A regular table can be instantly converted only into a range-partitioned table. For more information, see Switch a common table to a range partitioned table.
PolarDB for MySQL allows you to create and drop an index for a specific partition. For more information, see Partial indexes.
REORGANIZE PARTITION and REBUILD PARTITION affect specified partitions only.
ANALYZE PARTITION only modifies the statistics, not the table's metadata and data.
CHECK PARTITION does not modify table metadata and data.
REPAIR PARTITION rebuilds specified partitions only.

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a partition	No	No¹	Yes	No
Drop a partition	No	No²	No	No
Discard the space of a partitioned table	No	No	No	No
Import the space of a partitioned table	No	No	No	No
Truncate a partition	Yes	No	No	No
Coalesce partitions	No	Yes	No	No
Reorganize partitions	No	No⁴	No	No
Exchange partitions	Yes	Yes	Yes	No
Analyze a partition	Yes	Yes	No⁵	No
Check a partition	Yes	No	No⁶	No
Optimize a partition	Yes³	Yes³	No	Yes³
Rebuild a partition	No	No⁴	No	No
Repair a partition	Yes	No⁴	No	No
Partition a regular table	No	Yes	No	No
Remove partitioning	No	Yes	No	No

Using ADD PARTITION or DROP PARTITION with RANGE or LIST partitions does not require a table rebuild. Using ADD PARTITION with HASH and KEY partitions requires a table rebuild.
DROP PARTITION cannot be used with HASH or KEY partitions.
Performing OPTIMIZE PARTITION on an InnoDB table triggers the rebuilding of the entire partitioned table. Read and write operations are permitted during table rebuilding. To accelerate table rebuilding by using parallel DDL, set the innodb_parallel_build_primary_index parameter to ON. For more information, see Parallel DDL.
REORGANIZE PARTITION, REBUILD PARTITION, and REPAIR PARTITION affect specified partitions only.
ANALYZE PARTITION only modifies the statistics, not the table's metadata and data.
CHECK PARTITION does not modify table metadata and data.

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a partition	No	No¹	Yes	No
Drop a partition	No	No¹	No	No
Discard the space of a partitioned table	No	No	No	No
Import the space of a partitioned table	No	No	No	No
Truncate a partition	Yes	No	No	No
Coalesce partitions	No	Yes	No	No
Reorganize partitions	No	No²	No	No
Exchange partitions	Yes	No	Yes	No
Analyze a partition	Yes	No	No³	No
Check a partition	Yes	No	No⁴	No
Optimize a partition	No	Yes	No	No
Rebuild a partition	No	No²	No	No
Repair a partition	Yes	No²	No	No
Partition a regular table	No	Yes	No	No
Remove partitioning	No	Yes	No	No

Using ADD PARTITION or DROP PARTITION with RANGE or LIST partitions does not require a table rebuild. Using ADD PARTITION or DROP PARTITION with HASH or KEY partitions requires a table rebuild. DROP PARTITION cannot be used with HASH or KEY partitions.
REORGANIZE PARTITION, REBUILD PARTITION, and REPAIR PARTITION affect specified partitions only.
ANALYZE PARTITION only modifies the statistics, not the table's metadata and data.
CHECK PARTITION does not modify table metadata and data.

Syntax	Permits concurrent DML	Rebuilds table	Modifies metadata only	Supports parallel DDL
Add a partition	No	No¹	Yes	No
Drop a partition	No	No¹	No	No
Discard the space of a partitioned table	No	No	No	No
Import the space of a partitioned table	No	No	No	No
Truncate a partition	Yes	No	No	No
Coalesce partitions	No	Yes	No	No
Reorganize partitions	No	No²	No	No
Exchange partitions	Yes	No	Yes	No
Analyze a partition	Yes	No	No³	No
Check a partition	Yes	No	No⁴	No
Optimize a partition	No	Yes	No	No
Rebuild a partition	No	No²	No	No
Repair a partition	Yes	No²	No	No
Partition a regular table	No	Yes	No	No
Remove partitioning	No	Yes	No	No

Using ADD PARTITION or DROP PARTITION with RANGE or LIST partitions does not require a table rebuild. Using ADD PARTITION or DROP PARTITION with HASH or KEY partitions requires a table rebuild. DROP PARTITION cannot be used with HASH or KEY partitions.
REORGANIZE PARTITION, REBUILD PARTITION, and REPAIR PARTITION affect specified partitions only.
ANALYZE PARTITION only modifies the statistics, not the table's metadata and data.
CHECK PARTITION does not modify table metadata and data.

DDL execution methods

When PolarDB for MySQL uses the INPLACE or INSTANT algorithm, we recommend that you use online DDL. This method provides faster execution speed and higher stability.
However, if PolarDB for MySQL uses the COPY algorithm, DDL execution requires table locking and prevents read or write operations. In this case, we recommend that you use Data Management Service (DMS) or third-party tools such as gh-ost, which allow read and write operations during DDL execution. However, such tools are less efficient and are prone to failure in dealing with huge data increments in large tables or high-concurrency scenarios.

The following table compares the two DDL execution methods.

Invocation Mode	Permits concurrent reads and writes	Execution speed	Requires binary logging	Supports parallel acceleration

Invocation Mode	Permits concurrent reads and writes	Execution speed	Requires binary logging	Supports parallel acceleration
Online DDL	Yes	Fast	No	Yes
DMS or third-party tools such as gh-ost	Yes	Slow	Yes	No

Note

Using tools to perform DDL operations acquires the MDL-X lock in switching tables and changing metadata, which means the table will be locked temporarily. For information about how to prevent table locking, see Nonblocking DDL statements and Preemptible DDL.

Contact us

If you have any questions about DDL operations, contact us.

DDL operations

Overview

DDL algorithms

DDL operations

Index operations

Primary key operations

Column operations

Table operations

Generated column operations

Foreign key operations

Partitioning operations

DDL execution methods

Contact us

Sales Support

Technical Support

Connect & Report Abuse

About Alibaba Cloud

Our Global Network

Quick Start

Global Offices

Olympic Games Paris 2024 New

Stade Roland Garros – Glitz from the Past New

Place de la Concorde – “Breaking” the Barriers New

Vaires-sur-Marne Nautical Stadium – Sports with Sustainability New

International Broadcast Center – Images, Sounds, and Data that Captivate Billions New

Customer Success Stories New

Trust Center

Security & Compliance Center

Cloud Compliance Resources

Security Compliance FAQs

Product & Feature Update New

Cloud Forward

Press Room

Alibaba Cloud e-Magazine New

Alibaba Cloud in Analyst Research

Notice

Go Global Service New

Go Global Alliance with Alibaba Cloud

Asia Accelerator Hot

Information Compliance

China Gateway - MLPS 2.0 Compliance New

China Gateway - Networking

China Gateway - Global Application Acceleration New

China Gateway - Security

China Gateway - Data Security New

ICP Support Hot

China Gateway - Omnichannel Data Mid-End New

China Gateway - Organizational Data Mid-End New

China Gateway - Business Mid-End New

China Gateway - AI Service for Conversational Chatbots New

China Gateway - Online Education

China Gateway - Domain Registration

Work at Alibaba Cloud

Experienced Professionals

Students and Graduates

Free Trial

Pricing

Promo Center

Price Reduction

Pay Less and Deploy More

FinOps

Elastic Compute Service (ECS)

Simple Application Server (SAS)

Elastic GPU Service

Elastic Desktop Service (EDS)

Object Storage Service (OSS)

Cloud Enterprise Network (CEN)

Web Application Firewall (WAF)

Domain Names

Lingma

Container Compute Service (ACS)

Secure Access Service Edge (SASE)

Intelligent Media Services(IMS)

Edge Security Acceleration (ESA)(Original DCDN)

Intelligent Media Management

DingTalk Enterprise

YiDA

Alibaba Cloud Model Studio

Apsara Prime - For Easy Cloud Product Selection

Alibaba Cloud ECS - Cater All Your Cloud Hosting Needs

1TB CDN—Get Free 1 TB Outbound Traffic Plan Now

Security—Under Attack? Get Free Security Support

Short Message Service - Free Testing is Available

Elastic Compute Service (ECS) Hot

CloudBox

Compute Nest

Dedicated Host Hot

ECS Bare Metal Instance

Elastic GPU Service Featured

Simple Application Server (SAS) Hot

Auto Scaling

Cloud Phone Beta

Elastic Desktop Service (EDS) Featured

Batch Compute

Elastic High Performance Computing (E-HPC)

Super Computing Cluster (SCC)