Operations on columns - ApsaraDB for OceanBase - Alibaba Cloud Documentation Center

This topic describes the supported conversion scope of ALTER TABLE DDL operations on columns during data migration from a MySQL database to a MySQL tenant of OceanBase Database.

Syntax

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]

alter_option: {
  ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ALTER [COLUMN] col_name {
        SET DEFAULT {literal | (expr)}
      | SET {VISIBLE | INVISIBLE}
      | DROP DEFAULT
    }
  | 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]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
}

Supported DDL operations

Use ADD COLUMN to add one or more columns. For more information about how to define a column, see Create a column. You can also specify the FIRST | AFTER keyword.
Use ALTER COLUMN SET DEFAULT to modify the default value of a column.
Use ALTER COLUMN DROP DEFAULT to drop the default value of a column.
Use CHANGE COLUMN or MODIFY COLUMN to change or modify a column.
Use DROP COLUMN to drop a column.

Unsupported operations

Important

If a DDL statement to be synchronized contains the following unsupported definitions, the conversion will fail and the output will be an empty string.

Use ALTER COLUMN SET VISIBLE | INVISIBLE to modify the visibility of a column.
Use ORDER BY col_name.
Use RENAME COLUMN to rename a column.
```
ALTER TABLE t RENAME COLUMN d TO g;
```

Ignored clauses and options

Important

The following clauses and options will be ignored and will not be resolved or converted when they are specified in the synchronized DDL statements.

FIRST | AFTER attribute (If an ALTER TABLE DDL statement contains the MODIFY COLUMN or CHANGE COLUMN clause, this attribute will be ignored.)

Limits

Important

Due to the limits of MySQL tenants of OceanBase Database, an error may be returned when the synchronized DDL statements are converted and executed in a MySQL tenant of OceanBase Database in the following scenarios.

Using the MODIFY COLUMN or CHANGE COLUMN clause to modify the field type may fail. This is because certain field types are not supported in MySQL tenants of OceanBase Database.
Using the MODIFY COLUMN or CHANGE COLUMN statement to modify the field length may fail.
Using the ALTER COLUMN SET DEFAULT statement to set a default value may fail. This is because certain functions or expressions are not supported in MySQL tenants of OceanBase Database.
You cannot drop columns that are used as primary keys, unique keys, or normal indexes, or columns with FOREIGN KEY constraints.