Create a column - ApsaraDB for OceanBase - Alibaba Cloud Documentation Center

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

Syntax

create_definition: 
    col_name column_definition

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [VISIBLE | INVISIBLE]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [ENGINE_ATTRIBUTE [=] 'string']
      [SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
      [STORAGE {DISK | MEMORY}]
      [reference_definition]
      [check_constraint_definition]
  | data_type
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [VISIBLE | INVISIBLE]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
      [check_constraint_definition]
}

Supported operations

Convert the data types. For more information about supported data types, see Data type conversion.
Specify the NULL or NOT NULL attribute.
Specify the DEFAULT attribute, which can be a constant or a function.
Specify the VISIBLE or INVISIBLE attribute.
Specify AUTO_INCREMENT.
Specify COMMENT.
Specify COLLATE.
Specify a generated column and specify the VIRTUAL or STORED attribute for the column.
Specify the UNIQUE KEY, PRIMARY KEY, or KEY attribute. For more information, see Create an index or a constraint.
Specify the CHECK constraint. For more information, see Create an index or a constraint.
Important
Only MySQL tenants of OceanBase Database V3.2.3 and later versions support this DDL operation.
Specify foreign keys. For more information, see Create an index or a constraint.

Ignored clauses and options

Note

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

COLUMN_FORMAT
ENGINE_ATTRIBUTE
SECONDARY_ENGINE_ATTRIBUTE
STORAGE

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:

MySQL tenants of OceanBase Database do not support specifying certain functions or expressions in the DEFAULT attribute.
MySQL tenants of OceanBase Database do not support specifying certain functions or expressions in generated columns.
MySQL tenants of OceanBase Database do not support specifying the UNIQUE KEY, PRIMARY KEY, or KEY attribute for certain field types.
MySQL tenants of OceanBase Database do not support certain collations.