This topic describes the supported conversion scope of ALTER TABLE DDL operations during data migration from a MySQL database to the MySQL compatible mode 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 the "Modify table attributes" section in this topic.
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 the comment of a table.
ALTER TABLE t comment = 'table comment';
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.
Modify the ALGORITHM attribute.
ALTER TABLE t ALGORITHM = COPY;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;DISCARD | IMPORT TABLESPACE.
ALTER TABLE FORCE.
Modify the LOCK attribute: LOCK = DEFAULT | NONE | SHARED | EXCLUSIVE.
ALTER TABLE WITHOUT | WITH VALIDATION.
Modify attributes other than comment in
table_option. For more information, see thetable_optiondefinition in the "Syntax" section of this topic.