All Products
Search
Document Center

CREATE TABLE

Last Updated: Jun 18, 2021

Description

You can execute the CREATE TABLE statement to create a table in a database.

Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
      (table_definition_list) [table_option_list] [partition_option] [AS] select;

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
      LIKE table_name;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
    | [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
    | [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
    | {INDEX | KEY} [index_name] index_desc
    | FULLTEXT [INDEX | KEY] [index_name] fulltext_index_desc

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [DEFAULT const_value] [AUTO_INCREMENT]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment

index_desc:
   (column_desc_list) [index_type] [index_option_list]

fulltext_index_desc:
   (column_desc_list) CTXCAT(column_desc_list) [index_option_list]

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [ASC | DESC]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
      [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | primary_zone
    | replica_num
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
    | LOCALITY [=] "locality description"
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | PCTFREE [=] num

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] PARTITIONS partition_count

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)
    | SUBPARTITION BY LIST(expression)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

Parameters

Parameter

Description

DUPLICATE_SCOPE

The attribute of the replicated table. Valid values:

  • none: The table is a standard table.

  • zone: The table is a replicated table. The replica leader must replicate transactions to all FULL (F) replicas and READONLY (R) replicas in the current zone.

  • region: The table is a replicated table. The replica leader must replicate transactions to all F replicas and R replicas in the current region.

  • cluster: The table is a replicated table. The replica leader must replicate transactions to all F replicas and R replicas in the current cluster.

If you do not specify DUPLICATE_SCOPE, the default value is none.

ROW_FORMAT

Specifies whether to enable data encoding for the table.

  • redundant

    • Disable data encoding.

  • compact

    • Disable data encoding.

  • dynamic

    • Enable data encoding.

  • compressed

    • Enable data encoding.

  • default

    • If you set the value to default, the dynamic mode is used.

BLOCK_SIZE

The micro-block size of the table.

COMPRESSION

The compression algorithm used by the table. Valid values:

  1. none: No compression algorithms are used.

  2. lz4_1.0: The lz4 compression algorithm is used.

  3. zstd_1.0: The zstd compression algorithm is used.

  4. snappy_1.0: The snappy compression algorithm is used.

CHARSET | CHARACTER SET

The default character set of the columns in the table. Valid values: utf8, utf8mb4, gbk, utf16, and gb18030.

COLLATE

The default comparison rules of the columns in the table. Valid values:

utf8_bin, utf8_general_ci, utf8_unicode_ci, gbk_bin, gbk_chinese_ci, utf8mb4_general_ci, utf8mb4__general_cs, utf8mb4_bin, utf8mb4_unicode_ci, utf16_general_ci, utf16_bin, utf16_unicode_ci, gb18030_chinese_ci, gb18030_bin

primary_zone

The primary zone where the replica leader resides.

replica_num

The number of replicas.

table_tablegroup

The table group to which the table belongs.

AUTO_INCREMENT

The initial value of the auto-increment column in the table.

comment

The comments.

LOCALITY

A description about the distribution of replicas across zones. For example, the F@z1,F@z2,F@z3,R@z4 value specifies that replicas in the z1, z2, and z3 zones are F replicas and the replicas in the z4 zone are R replicas.

PCTFREE

The percentage of the idle space reserved in a macro-block.

Examples

  • Create a table in a database.

CREATE TABLE test (c1 int primary key, c2 VARCHAR(50)) REPLICA_NUM = 3, PRIMARY_ZONE = 'zone1';
  • Create a table and enable vertical partitioning for the table. The first partition contains only the c3 column. The second partition contains the c1 and c2 columns. The third partition contains the c4 and c5 columns that are not listed.

CREATE TABLE t1(c1 int, 
                c2 int,
                c3 int, 
                c4 int,
                c5 int)
                PARTITION BY CLOUMN ( c3, (c1, c2));

  • Create a replicated table.

CREATE TABLE item() locality = 'F,R{all_server}@hz1, F,R{all_server}@hz2, 
F,R{all_server}@hz3' DUPLICATE_SCOPE="cluster"
  • Create a table that has an index.

create table t1 (c1 int primary key, c2 int, c3 int, index i1 (c2));
  • Create an eight-partition table that uses hash partitioning.

create table t1 (c1 int primary key, c2 int) partition by hash(c1) partitions 8;
  • Create a table that uses range partitioning to determine the partitions and uses key partitioning to determine the subpartitions.

 create table t1 (c1 int, c2 int, c3 int)
     partition by range(c1) subpartition by key(c2, c3) subpartitions 5
     (partition p0 values less than(0), partition p1 values less than(100));
  • Create a table in which one column uses the GBK character set and the other column uses the UTF-8 character set.

create table t1 (c1 varchar(10),
                 c2 varchar(10) charset gbk collate gbk_bin)
  default charset utf8 collate utf8mb4_general_ci;
  • Enable encoding, use the zstd compression algorithm, and reserve five percent of the space in a macro-block.

create table t1 (c1 int, c2 int, c3 varchar(64))
  compression 'zstd_1.0'
  ROW_FORMAT dynamic
  pctfree 5;