CREATE TABLE

2024-05-29 07:17:41  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] [table_column_group_option] [AS] select;

CREATE TABLE [IF NOT EXISTS] table_name
      LIKE table_name;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition_list
    | [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_type] (key_part,...)
      [index_option_list] [index_column_group_option]
    | [CONSTRAINT [constraint_name]] CHECK(expression) constraint_state

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 string_value] [SKIP_INDEX(skip_index_option_list)]
   | column_name data_type
         [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
         [opt_generated_column_attribute]

skip_index_option_list:
    skip_index_option [,skip_index_option ...]

skip_index_option:
    MIN_MAX
    | SUM

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 NULL | NO ACTION | SET DEFAULT}


key_part:
    {index_col_name [(length)] | (expr)} [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 string_value

table_option_list:
    table_option [ table_option ...]

table_option:
      [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | table_tablegroup
    | block_size
    | lob_inrow_threshold [=] num
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | COMMENT string_value
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | PCTFREE [=] num
    | parallel_clause
    | DUPLICATE_SCOPE [=] 'none|cluster'

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

table_column_group_option/index_column_group_option:
      WITH COLUMN GROUP(all columns)
    | WITH COLUMN GROUP(each column)
    | WITH COLUMN GROUP(all columns, each column)

Parameters

Parameter Description
IF NOT EXISTS If you specify IF NOT EXISTS and the table to be created already exists, no error will be reported. If you do not specify this parameter and the table to be created already exists, the system reports an error.
PRIMARY KEY The primary key of the created table. If this parameter is not specified, a hidden primary key is used. OceanBase Database allows you to modify the primary key of a table or use the ALTER TABLE statement to add a primary key to a table. For more information, see ALTER TABLE.
FOREIGN KEY The foreign key of the created table. If you do not specify the name of the foreign key, it will be named in the format of table name + OBFK + time when the foreign key was created. For example, the foreign key created for table t1 at 00:00:00 on August 1, 2021 is named as t1_OBFK_1627747200000000. A foreign key enables one table (child table) to reference data from another table (parent table). When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified in the ON UPDATE or ON DELETE clause. Valid referential actions:
  • CASCADE: deletes or updates the affected row in the parent table and automatically deletes or updates the matching rows in the child table.
  • SET NULL: deletes or updates the affected row in the parent table and sets the foreign key column in the child table to NULL.
  • RESTRICT: rejects the delete or update operation on the parent table.
  • NO ACTION: defers the check.
The SET DEFAULT action is also supported.
KEY | INDEX The key or index of the created table. If you do not specify the name of the index, the name of the first column referenced by the index is used as the index name. If duplicate index names exist, the index will be named in the format of underscore (_) + sequence number. For example, if the name of the index created based on column c1 conflicts with an existing index name, the index will be named as c1_2. You can execute the SHOW INDEX statement to query the indexes of a table.
key_part Creates a normal or function-based index.
index_col_name The column name of the index. You can add ASC (ascending order) to the end of each column name. DESC (descending order) is not supported. By default, the columns are sorted in ascending order. Index-based sorting method: Data is first sorted by the values in the first column of index_col_name and by the values in the next column for the records with the same values in the first column.
expr A valid function-based index expression. A Boolean expression, such as c1=c1, is allowed.

Notice

Currently, you cannot create function-based indexes on generated columns in OceanBase Database.
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 for the table.
lob_inrow_threshold Sets the INROW threshold. LOB data sized greater than this threshold is stored in OUTROW mode in the LOB meta table. The default value is 4KB.
COMPRESSION The compression algorithm for 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 for columns in the table. For more information, see Character sets.
COLLATE The default collation for columns in the table. For more information, see Collations.
table_tablegroup The table group to which the table belongs.
AUTO_INCREMENT The start value of an auto-increment column in the table. OceanBase Database allows you to use auto-increment columns as the partitioning key.
COMMENT The comment. The value is case-insensitive.
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.
DUPLICATE_SCOPE The replicated table attribute. Valid values:
  • none: specifies that the table is a normal table. This is the default value.
  • cluster: specifies that the table is a replicated table. The leader needs to replicate transactions to all full-featured replicas and read-only replicas of the current tenant.
Currently, OceanBase Database supports only cluster-level replicated tables.
CHECK Specifies to restrict the range of values in the column.
  • If you define a CHECK constraint on a single column, you can write this column-level constraint in the column definition and specify a name for this constraint.
  • If you define a CHECK constraint on a table, this constraint is applied to multiple columns in the table and can appear before a column definition. When you drop the table, the CHECK constraint on the table is also dropped.
You can view constraint information in the following ways:
  • Use the SHOW CREATE TABLE statement.
  • Query the information_schema.TABLE_CONSTRAINTS view.
  • Query the information_schema.CHECK_CONSTRAINTS view.
    		•
    constraint_name The name of the constraint, which contains at most 64 characters.
    • Spaces are allowed at the beginning, in the middle, and at the end of a constraint name. However, the beginning and end of the constraint name must be identified with a backtick (`).
    • A constraint name can contain the dollar sign character ($).
    • If a constraint name is a reserved word, it must be identified with a backtick (`). Otherwise, an error is returned.
    • CHECK constraint names must be unique in the same database.
    expression The expression of the constraint.
    • expression cannot be empty.
    • The result of expression must be of the Boolean data type.
    • expression cannot contain a column that does not exist.
    table_column_group_option/index_column_group_option The columnstore options for the table or index. The following options are supported:
    • WITH COLUMN GROUP(all columns, each column): specifies to create a rowstore-columnstore redundant table or index.
    • WITH COLUMN GROUP(all columns): specifies to create a rowstore table or index.
    • WITH COLUMN GROUP(each column): specifies to create a columnstore table or index.
    SKIP_INDEX The skip index attribute of the column. Valid values:
    • MIN_MAX: the most common skip index type. A skip index of this type stores the maximum value, minimum value, and null count of the indexed column at the index node granularity. This type of skip indexes can accelerate the pushdown of filters and MIN/MAX aggregation.
    • SUM: the skip index type that is used to accelerate the pushdown of SUM aggregation for numeric values.

    Notice

    • You cannot create a skip index for a JSON column or a spatial column.
    • You cannot create a skip index for a generated column.

    Examples

    • Create a database table.

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

    • Create an indexed table.

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

    • Create a table named tbl3 with a function-based index.

      obclient> CREATE TABLE tbl3 (c1 INT, c2 INT, INDEX i1 ((c1+1)), UNIQUE KEY ((c1+c2)));
Query OK, 0 rows affected

obclient> SHOW CREATE TABLE tbl3;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl3    | CREATE TABLE `tbl3` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
UNIQUE KEY `functional_index` ((`c1` + `c2`)) BLOCK_SIZE 16384 LOCAL,
KEY `i1` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    • Create a HASH-partitioned table with eight partitions.

      obclient> CREATE TABLE tbl4 (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 tbl5 (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 the other column.

      obclient> CREATE TABLE tbl6 (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 in each macroblock.

      obclient> CREATE TABLE tbl7 (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 tbl8 and set the DOP to 3.

      obclient> CREATE TABLE tbl8(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 tbl9(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

    • Specify a foreign key for the ref_t2 table. Specify to execute the SET NULL action when an UPDATE operation affects a key value in the parent table that has matching rows in the child table.

      obclient> CREATE TABLE ref_t1(c1 INT PRIMARY KEY, C2 INT);
Query OK, 0 rows affected

obclient> CREATE TABLE ref_t2(c1 INT PRIMARY KEY, C2 INT,FOREIGN KEY(c2) REFERENCES ref_t1(c1) ON UPDATE SET NULL);
Query OK, 0 rows affected

    • Create a table named tbl10 with the CHECK constraint and view the constraint information.

      obclient> CREATE TABLE tbl10 (col1 INT, col2 INT, col3 INT, CONSTRAINT equal_check1 CHECK(col1 = col3 * 2));
Query OK, 0 rows affected

obclient> SHOW CREATE TABLE tbl10;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl10  | CREATE TABLE `tbl10` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  CONSTRAINT `equal_check1` CHECK ((`col1` = (`col3` * 2)))
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

    • Create a table named tbl11 based on the tbl10 table and view the table information.

      obclient> CREATE TABLE tbl11 LIKE tbl10;
Query OK, 0 rows affected

obclient> SHOW CREATE TABLE tbl11;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl11  | CREATE TABLE `tbl11` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBCHECK_1650793233327894` CHECK ((`col1` = (`col3` * 2)))
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

    • Create a cluster-level replicated table named dup_t1.

      1. Log on to the sys tenant and create a unit.

        obclient> CREATE RESOURCE UNIT IF NOT EXISTS 2c5g MAX_CPU 2, MEMORY_SIZE '5G';
Query OK, 0 rows affected

      2. Create a resource pool and set UNIT_NUM to 2.

        obclient> CREATE RESOURCE POOL tenant_pool UNIT = '2c5g', UNIT_NUM = 2, ZONE_LIST = ('z1', 'z2', 'z3');
Query OK, 0 rows affected

      3. Create a user tenant named obmysql and specify the locality.

        obclient> CREATE TENANT obmysql resource_pool_list=('tenant_pool'), LOCALITY = "F@z1, F@z2, R@z3", PRIMARY_ZONE = "z1" SET ob_tcp_invited_nodes='%';
Query OK, 0 rows affected

      4. Log on to the obmysql user tenant created in step 3 and create a replicated table.

        obclient> USE test;
Database changed
obclient> CREATE TABLE dup_t1(c1 INT) DUPLICATE_SCOPE = 'cluster';
Query OK, 0 rows affected

      5. (Optional) View the broadcast log stream. The replicated table is created on this log stream.

        obclient> SELECT * FROM oceanbase.DBA_OB_LS WHERE FLAG LIKE "%DUPLICATE%";
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
| LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG      |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
|  1003 | NORMAL | z1;z2        |             0 |           0 | 1683267390195713284 |     NULL | 1683337744205408139 | 1683337744205408139 | DUPLICATE |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
1 row in set

      6. (Optional) View the replica distribution of the replicated table in the sys tenant. The REPLICA_TYPE field indicates the replica type.

        obclient> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TABLE_NAME = "dup_t1";
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
| TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP         | SVR_PORT | ROLE     | REPLICA_TYPE | DUPLICATE_SCOPE |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z1   | 11.xxx.xxx.xxx |    36125 | LEADER   | FULL         | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z1   | 11.xxx.xxx.xxx  |    36124 | FOLLOWER | READONLY     | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z2   | 11.xxx.xxx.xxx |    36127 | FOLLOWER | FULL         | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z2   | 11.xxx.xxx.xxx |    36126 | FOLLOWER | READONLY     | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z3   | 11.xxx.xxx.xxx |    36128 | FOLLOWER | READONLY     | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z3   | 11.xxx.xxx.xxx  |    36129 | FOLLOWER | READONLY     | CLUSTER         |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
6 rows in set

      7. Insert data into, read data from, and write data to the replicated table. If you connect to the database by using an OceanBase Database Proxy (ODP), the read request may be routed to any OBServer node. If you directly connect to an OBServer node, the read request is executed on the connected OBServer node as long as the local replica is readable.

        obclient> INSERT INTO dup_t1 VALUES(1);
Query OK, 1 row affected

obclient> SELECT * FROM dup_t1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set

    • Create a columnstore table named tbl1_cg.

      CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(each column);

    • Create a table with a columnstore index.

      CREATE TABLE tbl2_cg (col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX i1 (col2) WITH COLUMN GROUP(each column));

    • Create a columnstore table with a columnstore index.

      CREATE TABLE tbl3_cg (col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX i1 (col2) WITH COLUMN GROUP(each column)) WITH COLUMN GROUP(each column);

    • Create a table and specify the skip index attribute for a column.

      CREATE TABLE test_skidx(
  col1 INT SKIP_INDEX(MIN_MAX, SUM),
  col2 FLOAT SKIP_INDEX(MIN_MAX),
  col3 VARCHAR(1024) SKIP_INDEX(MIN_MAX),
  col4 CHAR(10)
);

    References

    Last
    Next

    OceanBaseOceanBase

    OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

    Company

    About OceanBaseContact UsPartnerTrust Center

    Product

    OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

    Resources

    DocsBlogWhite Paper

    Follow us

    LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

    © OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

    Contact Us