Before the Instant ADD COLUMN feature is released, if you want to add columns to a
table, the entire table must be rebuilt. This consumes a large amount of system resources.
ApsaraDB PolarDB MySQL-compatible edition 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
- Clusters of ApsaraDB PolarDB MySQL-compatible edition 5.7 and the revision is 18.104.22.168.6 or later. For more information about the revision
of the cluster, see Query the kernel version number.
Note To use this feature in clusters of ApsaraDB PolarDB MySQL-compatible edition 5.7, you must first specify the innodb_support_instant_add_column parameter.
- Clusters of ApsaraDB PolarDB MySQL-compatible edition 8.0.
Note By default, clusters of ApsaraDB PolarDB MySQL-compatible edition 8.0 support this feature. You do not need to specify parameters.
- A new column can be appended to only the last column of the table.
- Virtual columns are not supported (supported by ApsaraDB PolarDB MySQL-compatible edition 8.0).
- Partition tables are not supported (supported by ApsaraDB PolarDB MySQL-compatible edition 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.
How to instantly add columns
For clusters of ApsaraDB PolarDB MySQL-compatible edition 5.7, you must specify the innodb_support_instant_add_column parameter to enable the feature.Note For clusters of ApsaraDB PolarDB MySQL-compatible edition 8.0, you do not need to specify this parameter.
Parameter Level Description innodb_support_instant_add_column Global Specify whether to enable the instant ADD COLUMN feature. Valid values:
- ON: enable the feature.
- OFF: disable the feature. This is the default value.
- You can specify
ALGORITHM=INSTANTto forcibly enable the feature. Example:
ALTER TABLE test.t ADD COLUMN test_column int, ALGORITHM=INSTANT;
If the message
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.is returned after you execute the preceding statement, it indicates that the instant algorithm is unavailable. In this case, we recommend that you check whether the innodb_support_instant_add_column is set to ON and check the rules in Limits.
- If you do not specify
ALGORITHM=DEFAULT, 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 specify
- View columns added by the Instant algorithm
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