PolarDB:Instant ADD COLUMN

Prerequisites

Limits

• You can use the instant ADD COLUMN feature to add a column only to the end of a table.

• You can use the instant ADD COLUMN feature to add virtual columns to a table only in PolarDB for MySQL 8.0.

• You cannot use the instant ADD COLUMN feature to add columns to a partitioned table in PolarDB for MySQL 5.6. You can use the instant ADD COLUMN feature to add columns to a partitioned table in PolarDB for MySQL 5.7 and PolarDB for MySQL 8.0.

  Note

  The feature that allows you to instantly add columns to a partitioned table is in canary release for PolarDB for MySQL 5.7. To use the feature in PolarDB for MySQL 5.7, go to Quota Center, find the quota whose ID is polarM_57_iac_on_partition_table, and then click Apply.

• You cannot use the instant ADD COLUMN feature to add columns to a table in which full-text indexes or in-memory column indexes (IMCIs) are created.

• You cannot use the instant ADD COLUMN feature to add columns to a table that has the Implicit primary key option enabled but does not have custom primary key columns.

• You cannot include the instant ADD COLUMN operation and other DDL operations in the same SQL statement.

Usage

• Configure parameters

  To use the instant ADD COLUMN feature in PolarDB for MySQL 5.6 or PolarDB for MySQL 5.7 clusters, you must set the loose_innodb_support_instant_add_column parameter to ON. For information about how to configure parameters for a cluster, see Configure cluster and node parameters.

  Note

  By default, PolarDB for MySQL 8.0 clusters support the instant ADD COLUMN column feature. You do not need to configure parameters to enable the feature.

  Parameter	Level	Description
  loose_innodb_support_instant_add_column	Global	Specifies whether to enable the instant ADD COLUMN feature. Valid values: ON OFF

• Execute statements

  • You can specify the ALGORITHM=INSTANT clause to forcibly enable the instant ADD COLUMN feature. Example:

    ALTER TABLE test.t ADD COLUMN test_column int, ALGORITHM=INSTANT;

    If a column cannot be added by using the instant ADD COLUMN feature, the ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. error message is returned. In this case, we recommend that you check whether the loose_innodb_support_instant_add_column parameter is set to ON and determine whether the operation is restricted. For information about the limits of the instant ADD COLUMN feature, see the "Limits" section of this topic.

  • If you do not specify an algorithm or specify the default algorithm, PolarDB selects the fastest algorithm for adding columns. Example:

    ALTER TABLE test.t ADD COLUMN test_column int, ALGORITHM=DEFAULT;
    ALTER TABLE test.t ADD COLUMN test_column int;

    Note

    PolarDB selects the algorithms for adding columns in the following priority order: INSTANT > INPLACE > COPY.

• View columns added by using the INSTANT algorithm

  A table named INNODB_SYS_INSTANT_COLUMNS table is added to the INFORMATION_SCHEMA database in PolarDB for MySQL 5.6 and PolarDB for MySQL 5.7 clusters. The table records the information about instantly added columns, including the column name, column sequence number, and default value (binary value). You can execute the following statement to view information about the new instantly added columns.

  SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INSTANT_COLUMNS;

  Note

  If you perform DDL operations that need to recreate a table, such as DROP COLUMN, after you instantly add columns to the table, the system deletes the information about the columns from the INNODB_SYS_INSTANT_COLUMNS table.

  You can use the following statement in PolarDB for MySQL 8.0 to view the columns of a table. If a column is added by using the instant ADD COLUMN feature, the value of the has_default parameter is 1.

  SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID = (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = "test/t1");

Contact Us

If you have any questions about DDL operations, please feel free to Contact us.