This topic describes the supported conversion scope of CREATE INDEX DDL operations during data migration from a MySQL database to a MySQL-compatible tenant 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 MySQL-compatible tenants of OceanBase Database, an error may be returned when the synchronized DDL statements are converted and executed in a MySQL-compatible tenant of OceanBase Database in the following scenarios.
MySQL-compatible tenants of OceanBase Database do not support creating indexes on specific types of fields. For more information, see DDL incompatibilities between a MySQL database and a MySQL-compatible tenant of OceanBase Database.
