Modify a table - ApsaraDB for OceanBase - Alibaba Cloud Documentation Center

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

Overview

Operations on columns. You can add, drop, and modify columns. For more information, see Operations on columns.
Operations on constraints and indexes. You can add and drop constraints and indexes. For more information, see Operations on constraints and indexes.
Operations on partitions. For more information, see Operations on partitions.
Modify table attributes. For more information, see Modify table attributes following this section.

Modify table attributes

Syntax

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]

alter_option: {
    table_options
   | RENAME [TO | AS] new_tbl_name
   | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
   | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
   | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
   | {DISCARD | IMPORT} TABLESPACE
   | FORCE
   | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
   | {WITHOUT | WITH} VALIDATION
}

table_option: {
    AUTOEXTEND_SIZE [=] value
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
  | UNION [=] (tbl_name[,tbl_name]...)
}

Supported operations

Rename tables.

alter table tablename rename to new_tablename;

Modify table comments.

ALTER TABLE t comment = 'table comment';

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.

Modify the ALGORITHM attribute.
```
ALTER TABLE t ALGORITHM = COPY;
```

Use CONVERT TO CHARACTER SET.

ALTER TABLE t CONVERT TO CHARACTER SET utf16;

Modify the CHARACTER SET or COLLATE attribute.
```
ALTER TABLE T DEFAULT CHARACTER SET utf8;
```
Specify the DISCARD | IMPORT TABLESPACE option.
Use ALTER TABLE FORCE.
Modify the LOCK attribute: LOCK = DEFAULT | NONE | SHARED | EXCLUSIVE.
Use ALTER TABLE WITHOUT | WITH VALIDATION.
Modify attributes except for COMMENT in table_option. For more information, see the table_option definition in the Syntax section of this topic.