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

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

Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)   
[table_options]
[partition_options]

Syntax	Description
`create_definition`	You can create a column, an index, or a constraint. For more information, see Create a column and Create an index or a constraint.
`table_options`	For more information, see table_options following this section.
`partition_options`	For more information, see Create a partition.

When you use the CREATE TABLE statement to create a table, you can specify the IF NOT EXISTS keywords and the LIKE option. Example:
```
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
```
You cannot use this statement to create a temporary table. The TEMPORARY keyword will be ignored.
Note
You can create temporary tables in MySQL tenants of OceanBase Database V3.2.3.
You cannot use the CREATE TABLE AS SELECT statement to create a table based on the result set of a SELECT statement. This is because the output will be an empty string in this scenario.

table_options

You can specify table-level comments.
You can specify CHARACTER SET.
Other table options cannot be parsed and will be ignored.

The following sample code describes the table options in detail:

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]...)
}

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 supports a limited number of character sets.
Do not use unsupported character sets, such as Latin-1. If you forcibly specify an unsupported character set for a MySQL tenant of OceanBase Database, this character set will not be converted during DDL synchronization to the destination database and an error will be returned.