CREATE TABLE |V3.2.4|OceanBase Database| docs|Distributed Database

CREATE TABLE

Last Updated：2023-10-24 09:23:03 Updated

Purpose

You can use this statement to create a table in the database.

Syntax

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

CREATE 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
    | [CONSTRAINT [constraint_name]] FOREIGN KEY 
            [index_name] index_desc 
            REFERENCES reference_definition 
            [match_action][opt_reference_option_list]
    | {INDEX | KEY} [index_name] 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
   | column_name data_type
         [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
         [opt_generated_column_attribute] 

index_desc:
   (column_desc_list) [index_type] [index_option_list]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option...]

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULLX | NO ACTION | SET DEFAULT}

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
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|cluster"
    | LOCALITY [=] "locality description"
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | PCTFREE [=] num
    | parallel_clause

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

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
PRIMARY KEY	The primary key of the created table. If this parameter is not specified, a hidden primary key is used. OceanBase Database does not allow you to modify the primary key of a table or use the `ALTER TABLE` statement to add a primary key to a table. Therefore, we recommend that you specify a primary key when creating a table.
FOREIGN KEY	The foreign key of the created table. If you do not specify the foreign key name, the foreign key name is a combination of the table name, `OBFK`, and the creation time. For example, the name of the foreign key created for the table `t1` at 00:00:00 on August 1, 2021 is `t1_OBFK_1627747200000000`.
KEY \| INDEX	The key or index of the created table. If you do not specify the index name, the name of the first column referenced by the index is used as the index name. If a duplicate index name exists, the index name is a combination of an underscore (_) and a sequence number. For example, an index created on the column `c1` is named `c1_2` if a duplicate name exists. You can execute the `SHOW INDEX` statement to view the indexes of a table.
DUPLICATE_SCOPE	The nature of a replica table. Valid values: none, zone, region, and cluster. `none`: indicates that the table is a normal table. `cluster`: indicates that the table is a replica table. The leader must copy transactions to all F and R replicas in this cluster. If the `DUPLICATE_SCOPE` parameter is not specified, the default value `none` is used. Currently, OceanBase Database supports only `cluster`-level replica tables.
ROW_FORMAT	Specifies whether to enable the encoding storage format. `redundant`: indicates that the encoding storage format is not enabled. `compact`: indicates that the encoding storage format is not enabled. `dynamic`: an encoding storage format. `compressed`: an encoding storage format. `default`: This value is equivalent to `dynamic`.
[GENERATED ALWAYS] AS (expr) [VIRTUAL \| STORED]	Creates a generated column. `expr` specifies the expression used to calculate the column value. `VIRTUAL`: indicates that column values are not stored, but are immediately calculated after any `BEFORE` trigger when a row is read. Virtual columns do not occupy storage space. `STORED`: evaluates and stores column values when you insert or update a row. Stored columns occupy storage space and can be indexed.
BLOCK_SIZE	The microblock size of the table.
COMPRESSION	The compression algorithm of the table. Valid values: `none`: indicates that no compression algorithm is used. `lz4_1.0`: indicates that the `lz4` compression algorithm is used. `zstd_1.0`: indicates that the `zstd` compression algorithm is used. `snappy_1.0`: indicates that the `snappy` compression algorithm is used.
CHARSET \| CHARACTER SET	The default character set of columns in the table. Valid values: `utf8` `utf8mb4` `gbk` `utf16` `gb18030`
COLLATE	The default collation of 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 leader resides.
table_tablegroup	The table group to which the table belongs.
AUTO_INCREMENT	The initial value of auto-increment columns in the table. OceanBase Database allows you to use an auto-increment column as the partitioning key.
comment	The comment.
LOCALITY	The distribution of replicas across zones. For example, `F@z1`, `F@z2`, `F@z3`, and `R@z4` indicate that `z1`, `z2`, and `z3` are full-featured replicas, and `z4` is a read-only replica.
PCTFREE	The percentage of space reserved for macroblocks.
parallel_clause	The degree of parallelism (DOP) at the table level. `NOPARALLEL`: sets the DOP to `1`, which is the default value. `PARALLEL integer`: sets the DOP to an `integer` greater than or equal to `1`.

Examples

Create a database table.

obclient> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(50)) REPLICA_NUM = 3, 
       PRIMARY_ZONE = 'zone1';
Query OK, 0 rows affected

Create a replica table.

obclient> CREATE TABLE item(id INT, name VARCHAR(10)) DUPLICATE_SCOPE='cluster';
Query OK, 0 rows affected

Create an indexed table.

obclient> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX i1 (c2));
Query OK, 0 rows affected

Create a HASH-partitioned table with eight partitions.

obclient> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY HASH(c1) PARTITIONS 8;
Query OK, 0 rows affected

Create a RANGE-KEY-subpartitioned table.

obclient> 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));
Query OK, 0 rows affected

Create a table that uses GBK for one column and UTF8 for another column.

obclient> CREATE TABLE t1 (c1 VARCHAR(10),
                 c2 VARCHAR(10) CHARSET GBK COLLATE gbk_bin)
             DEFAULT CHARSET utf8 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected

Enable encoding and zstd compression for the table and reserve 5% of space for macroblocks.

obclient> CREATE TABLE t1 (c1 INT, c2 INT, c3 VARCHAR(64))
      COMPRESSION 'zstd_1.0'
      ROW_FORMAT DYNAMIC
      PCTFREE 5;
Query OK, 0 rows affected

Create a table named t1 and set the DOP to 3.

obclient> CREATE TABLE tbl1(c1 INT PRIMARY KEY, c2 INT) PARALLEL 3;
Query OK, 0 rows affected

Use an auto-increment column as the partitioning key.

obclient> CREATE TABLE tbl2(inv_id BIGINT NOT NULL AUTO_INCREMENT,c1 BIGINT, 
   PRIMARY KEY (inv_id) ) PARTITION BY HASH(inv_id) PARTITIONS 8;
Query OK, 0 rows affected