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 COLUMN feature.

Prerequisites

The following clusters are used:
  • Clusters of ApsaraDB PolarDB MySQL-compatible edition 5.7 and the revision is 5.7.1.0.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.

Limits

  • 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 key enabled but do not have custom primary keys are not supported.
  • You cannot perform the instant ADD COLUMN operation together with other DDL operations in the same SQL statement.

How to instantly add columns

  • Parameters

    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.
  • Statements
    • You can specify ALGORITHM=INSTANT to 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 or set 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.
  • View columns added by the Instant algorithm
    The INNODB_SYS_INSTANT_COLUMNS table is created to the INFORMATION_SCHEMA database. 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_COLUMNS table.