PolarDB:Change the data type of a column without locking the table

Supported versions

For version naming rules, see Release notes. To check or upgrade your instance version, see View and update the version of an instance.

Supported operations

OMC supports the following operations in AUTO mode and DRDS mode databases:

Multiple supported operations can be combined in a single ALTER TABLE statement.

Limitations

• Cannot rename columns used as shard keys, partition keys, or primary keys. Their types can be changed.

• Cannot delete columns that are part of a shard key, partition key, or primary key.

• Cannot add UNIQUE or KEY constraints to a newly created column.

• Cannot modify generated columns or columns referenced in a generated column expression.

Syntax

Specify ALGORITHM=OMC at the end of your ALTER TABLE statement to run the operation without locking the table. Without this keyword, PolarDB-X falls back to the standard MySQL algorithm, which may acquire metadata locks that block DML.

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]
}

ALGORITHM=OMC applies to all alter_option clauses in the same statement.

Examples

The following examples use a table in AUTO mode. All statements use ALGORITHM=OMC to run without locking the table.

Step 1. Create table t1.

CREATE TABLE t1(a INT PRIMARY KEY, b TINYINT, c VARCHAR(10)) PARTITION BY KEY(a);

Step 2. Change the types of columns b and c in a single statement.

ALTER TABLE t1 MODIFY COLUMN b INT, MODIFY COLUMN c VARCHAR(30), ALGORITHM=OMC;

Step 3. Rename column b to d, change its type, and add a new column e of type BIGINT — all in one statement.

ALTER TABLE t1 CHANGE COLUMN b d INT, ADD COLUMN e BIGINT AFTER d, ALGORITHM=OMC;

What's next

• Online DDL

• View and update the version of an instance