Typically, if you want to add columns to a table, the entire table must be rebuilt.
This consumes a large amount of system resources. PolarDB for MySQL supports the
instant ADD COLUMN feature. When you add columns, you need to change only the definition of the table,
rather than the existing data. This allows you to instantly add columns to a table
regardless of the size of the table. This topic describes how to use the instant ADD
- A PolarDB for MySQL 5.7 cluster whose revision version is 184.108.40.206.6 or later. For information about how
to view the version of your cluster, see Query the engine version.
Note To use this feature in clusters of PolarDB for MySQL 5.7, you must first configure the innodb_support_instant_add_column parameter.
- PolarDB for MySQL 8.0.
Note This feature is provided out-of-the-box with PolarDB for MySQL 8.0 clusters. No additional configurations are required.
- A new column can be appended to only the last column of the table.
- Virtual columns are not supported (supported by PolarDB for MySQL 8.0).
- Partition tables are not supported (supported by PolarDB for MySQL 8.0).
- Tables with full-text indexes are not supported.
- Tables that have
Implicit primary keyenabled but do not have custom primary keys are not supported.
- You cannot perform the
instant ADD COLUMNoperation together with other DDL operations in the same SQL statement.
Use the instant ADD COLUMN feature
For clusters of PolarDB for MySQL 5.7, you must configure the innodb_support_instant_add_column parameter to enable the feature.Note For clusters of PolarDB for MySQL 8.0, you do not need to configure this parameter.
Parameter Level Description innodb_support_instant_add_column Global Specifies whether to enable the instant ADD COLUMN feature. Default value: OFF. Valid values:
- You can set
ALGORITHMto INSTANT to forcibly enable the feature. Example:
ALTER TABLE test.t ADD COLUMN test_column int, ALGORITHM=INSTANT;
If the instant algorithm is unavailable, the message
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.is returned. In this case, we recommend that you check whether the innodb_support_instant_add_column parameter is set to ON and check the rules in Limitations.
- If you set
ALGORITHMto DEFAULT or do not specify
ALGORITHM, PolarDB selects the fastest algorithm to add columns. Example:
ALTER TABLE test.t ADD COLUMN test_column int, ALGORITHM=DEFAULT; ALTER TABLE test.t ADD COLUMN test_column int;Note The priority of the algorithms in PolarDB is INSTANT > INPLACE > COPY.
- You can set
- View columns added by using the Instant algorithm
For PolarDB for MySQL 5.7, the
INNODB_SYS_INSTANT_COLUMNStable is created to the
INFORMATION_SCHEMAdatabase. The table records the information about instantly added columns, such as the column name, column sequence number, and default value (binary value). You can execute the following statement to view the details of the table to make sure that the column is added.
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INSTANT_COLUMNS;Note After you instantly add columns to a specified table, if you perform DDL operations that need to recreate the table, such as DROP COLUMN, the system deletes the information about the columns from the
INNODB_SYS_INSTANT_COLUMNStable.For PolarDB for MySQL 8.0, you can use the following statement to view columns of the table. If a column is added by using the instant algorithm, the
has_defaultvalue is 1.
SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID = (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = "test/t1");