This topic describes the supported conversion scope of CREATE TABLE DDL operations for creating columns during data migration from a MySQL database to the MySQL compatible mode 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
Creating columns of all supported data types. For more information about the data types supported in data migration from a MySQL database to the MySQL compatible mode of OceanBase Database, see Data type conversion.
Specifying the NULL or NOT NULL attribute.
Specifying the DEFAULT attribute, which can be a constant or a function.
Specifying the VISIBLE or INVISIBLE attribute.
Specifying AUTO_INCREMENT.
Specifying COMMENT.
Specifying COLLATE.
Specifying a generated column and specifying the VIRTUAL or STORED attribute for the column.
Specifying the UNIQUE KEY, PRIMARY KEY, or KEY attribute. For more information, see Create an index or a constraint.
Specifying the CHECK constraint. For more information, see Create an index or a constraint.
Notice
You can specify the CHECK constraint only in the MySQL compatible mode of OceanBase Database V3.2.3 or later.
Specifying 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
Limitations
Warning
Due to the limitations of the MySQL compatible mode of OceanBase Database, an error may be returned when the synchronized DDL statements are converted and executed in the MySQL compatible mode of OceanBase Database in the following scenarios.
The MySQL compatible mode of OceanBase Database does not support specifying certain functions or expressions in the DEFAULT attribute.
The MySQL compatible mode of OceanBase Database does not support specifying certain functions or expressions in generated columns.
The MySQL compatible mode of OceanBase Database does not support specifying the UNIQUE KEY, PRIMARY KEY, or KEY attribute for certain field types.
The MySQL compatible mode of OceanBase Database does not support certain collations. For more information, see Collations.