All Products
Search

This Product

    PolarDB:Instant ADD COLUMN

    Document Center

    PolarDB:Instant ADD COLUMN

    Last Updated:Sep 02, 2024

    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.6 cluster whose revision version is 5.6.1.0.43 or later. For information about how to view the version of your cluster, see Query the engine version.

      Note

      This feature is in canary release for PolarDB for MySQL 5.6. To use this feature, go to Quota Center. Enter the polardb_mysql_iac_56 quota ID to find the quota name. Click Apply in the Actions column.

    • 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.6 and 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 Configure cluster and node parameters.

      Note

      For clusters of PolarDB for MySQL 8.0, you do not need to configure this parameter.

      Parameter

      Level

      Description

      loose_innodb_support_instant_add_column

      Global

      Specifies 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 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 using the Instant algorithm

      For PolarDB for MySQL 5.6 and 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");

    Contact Us

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