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

Operations that modify column types block DML operations. PolarDB-X provides the Online Modify Column (OMC) feature that allows you to change column types without locking tables online.

Supported versions

To change column types without locking tables, your instance must be of V5.4.18-17108394 or later.
For operations that only add or delete columns without locking tables, your instance must be of V5.4.20-20241224 or later.
To perform operations without locking tables using Data Management (DMS), your instance must be of V5.4.20-20241224 or later.

Note

For information about the rules of instance versions, see Release notes.
For information about how to view instance versions, see View and update the version of an instance.

Precautions

You cannot modify the names of columns that are used as shard keys, partition keys, or primary keys. However, you can modify the types of these columns.
You cannot delete columns that are included in shard keys, partition keys, or primary keys.
You cannot add keywords, such as UNIQUE or KEY, to the definition of a newly created column.
You cannot modify generated columns or the columns that are referenced in the expression of a generated column.
Only MODIFY COLUMN, CHANGE COLUMN, DROP COLUMN, and ADD COLUMN operations are supported. Other operations are not supported, such as ADD INDEX, DROP INDEX, or SET DEFAULT.

Benefits

Databases in AUTO and DRDS modes are supported.
You can change the types of multiple columns at the same time.
You can change column types and add or delete columns using the same statement.

Syntax

The ALTER TABLE statement is executed without locking tables only when ALGORITHM=OMC is specified in PolarDB-X. If the keyword is not specified, the standard MySQL algorithm is used by default (which may triggers metadata locks that block DML operations).

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

Examples

The examples demonstrate how to change column types without locking tables in a database in AUTO mode:

Create the table t1.

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

Change the types of columns b and c in table t1.

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

Change the name and type of column b in table t1, and add a new column e of the BIGINT type.
```
ALTER TABLE t1 CHANGE COLUMN b d INT, ADD COLUMN e BIGINT AFTER d, ALGORITHM=OMC;
```