CREATE INDEX - ApsaraDB for OceanBase - Alibaba Cloud Documentation Center

This topic describes the supported conversion scope of CREATE INDEX DDL operations during data migration from a MySQL database to a MySQL 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.
Important
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

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.

Create function-based indexes.

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.

ASC | DESC
KEY_BLOCK_SIZE
WITH PARSER
VISIBLE | INVISIBLE
ENGINE_ATTRIBUTE
SECONDARY_ENGINE_ATTRIBUTE
ALGORITHM = DEFAULT | INPLACE | COPY
LOCK = DEFAULT | NONE | SHARED | EXCLUSIVE

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 creating indexes on specific types of fields. For more information, see DDL incompatibilities between a MySQL database and a MySQL tenant of OceanBase Database.