PolarDB:Change the column types without locking tables

Prerequisites

The kernel version of your PolarDB-X instance must be V5.4.18-17108394 or later.

Limits

• OMC only supports PolarDB-X instances whose kernel version is V5.4.18-17108394 or later.

• You cannot set ALGORITHM to OMC if you want to only add or remove columns.

• You cannot modify the names of the shard key, partition key, or primary key columns. However, you can modify the types of these columns.

• You cannot delete a shard key, partition key, or primary key column.

• You cannot add keywords, such as UNIQUE and KEY, to the definition of a newly created column.

• Only the MODIFY COLUMN, CHANGE COLUMN, DROP COLUMN, and ADD COLUMN operations are supported. Other operations, such as ADD INDEX, DROP INDEX, and SET DEFAULT, are not supported.

• You cannot modify a generated column and the columns that are referenced in the expression of a generated column.

Syntax

The following sample code describes the syntax of using OMC. You can use OMC to change the types of multiple columns at a time. You can also use OMC to change column types and add or remove columns at the same time.

ALTER TABLE tbl_name
    alter_option [, alter_option] ...
    ALGORITHM = OMC

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
}

Use OMC

If no ALGORITHM clause is specified or you set ALGORITHM to other values, such as INPLACE or COPY, in the ALTER TABLE statement, the statement blocks DML operations. OMC takes effect only when you set ALGORITHM to OMC.

Examples

• Execute the following statement to create a test table named t1:

  CREATE TABLE t1(a int primary key, b tinyint, c varchar(10)) partition by key(a);

• Execute the following statement to modify the types of column b and column c in the t1 table:

  ALTER TABLE t1 MODIFY COLUMN b int, MODIFY COLUMN c varchar(30), ALGORITHM=OMC;

• Execute the following statement to modify the name and type of column b in the t1 table and add column e of the BIGINT type:

  ALTER TABLE t1 CHANGE COLUMN b d int, ADD COLUMN e bigint AFTER d, ALGORITHM=OMC;