Create a table

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

Syntax

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

• When you use the CREATE TABLE statement to create a table, you can specify the IF NOT EXISTS keyword and the LIKE option. Example:

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  { LIKE old_tbl_name | (LIKE old_tbl_name) }
  

• You cannot use the CREATE TABLE statement to create a temporary table. The TEMPORARY keyword will be ignored.

  Note

  The MySQL compatible mode of OceanBase Database V3.2.3 and later allow you to create temporary tables.

• You cannot use the CREATE TABLE AS SELECT statement to create a table based on the result set of a query. This statement will be rejected and an empty string will be returned.

Table options

• You can specify the table-level COMMENT option.

• You can specify the CHARACTER SET option.

• Other table options cannot be parsed and will be ignored.

The following code shows the definitions of table options:

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

Limitations

• The MySQL compatible mode of OceanBase Database supports only specific character sets. For more information, see Character sets.

• DDL operations are not supported for statements involving the latin1 character set. If you forcibly specify an unsupported character set, DDL synchronization will not convert this character set, and an error will be returned when it is executed in the target database.