This topic describes the supported conversion scope of CREATE INDEX DDL operations during data migration from a MySQL database to the MySQL compatible mode of OceanBase Database.
Syntax
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[index_type]
ON tbl_name (key_part,...)
[index_option]
[algorithm_option | lock_option] ...
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
| ENGINE_ATTRIBUTE [=] 'string'
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
index_type:
USING {BTREE | HASH}
algorithm_option:
ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
Supported operations
Create normal indexes.
Create unique indexes.
Create SPATIAL indexes.
Notice
Only OceanBase Database V3.2.4 and V4.1.0 support this DDL operation.
Create prefixed indexes.
CREATE INDEX i ON t(c1(2));Specify only comment in index_option.
Unsupported operations
Warning
If a DDL statement to be synchronized contains the following unsupported definitions, the conversion will fail and the output will be an empty string.
Create function-based indexes.
Ignored clauses and options
Warning
The following clauses and options will be ignored and will not be resolved or converted when they are specified in the synchronized DDL statements.
ASC | DESC
KEY_BLOCK_SIZEWITH PARSERVISIBLE | INVISIBLEENGINE_ATTRIBUTESECONDARY_ENGINE_ATTRIBUTEALGORITHM = DEFAULT | INPLACE | COPYLOCK = DEFAULT | NONE | SHARED | EXCLUSIVE
Limitations
Notice
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 in the following scenarios.
The MySQL compatible mode of OceanBase Database does not support creating indexes on certain specific field types. For more information, see DDL incompatibilities between a MySQL database and the MySQL compatible mode of OceanBase Database.