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

Prerequisites

Your cluster meets one of the following version requirements:
  • A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.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 loose_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.

Limitations

  • 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 that use full-text indexes or IMCIs 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.

Use the instant ADD COLUMN feature

  • Parameters

    For clusters of PolarDB for MySQL 5.7, you must configure the loose_innodb_support_instant_add_column parameter to enable the feature. For more information about how to set parameters, see Specify cluster and node parameters.

    Note For clusters of PolarDB for MySQL 8.0, you do not need to configure this parameter.
    ParameterLevelDescription
    loose_innodb_support_instant_add_columnGlobalSpecifies whether to enable the instant ADD COLUMN feature. Default value: OFF. Valid values:
    • ON
    • OFF.
  • Statements
    • You can set ALGORITHM to 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 loose_innodb_support_instant_add_column parameter is set to ON and check the rules in Limitations.

    • If you set ALGORITHM to DEFAULT or do not specify ALGORITHM, PolarDB for MySQL 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 for MySQL is INSTANT > INPLACE > COPY.
  • View columns added by using the Instant algorithm
    For PolarDB for MySQL 5.7, 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.
    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_default value is 1. 
    SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID = (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = "test/t1");