Create a table

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]

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

Limitations

• MySQL tenants of OceanBase Database support only certain character sets. For more information, see 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.