CREATE TABLE

Purpose

You can execute this statement to create a table in a database.

• If you want to create a table by copying data from an existing table, you can use the CREATE TABLE ... AS SELECT statement. The data in the created table may not have the same structure as the source table and will lose constraints, indexes, default values, and partitions.

• If you want to create a table by copying the structure from an existing table, you can use the CREATE TABLE ... LIKE statement. The created table will have the same structure as the source table but will not contain any data.

• If you want to create an external table, you must use the CREATE EXTERNAL TABLE statement. For more information, see CREATE EXTERNAL TABLE.

Privilege requirements

To execute the CREATE TABLE statement, the current user must have the CREATE privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Syntax

create_table_stmt:
    CREATE TABLE [IF NOT EXISTS] table_name
        (table_definition_list)
        [table_option_list]
        [partition_option];

create_table_select_stmt:
    CREATE TABLE [IF NOT EXISTS] table_name
        [(table_definition_list)]
        [table_option_list]
        [partition_option]
        [AS] select_stmt;

create_table_like_stmt:
    CREATE TABLE [IF NOT EXISTS] new_table_name LIKE existing_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} [unique_index_name] index_desc
    | [CONSTRAINT [constraint_name]] FOREIGN KEY [foreign_index_name] index_desc REFERENCES source_tbl_name (source_key_part) [match_action] [opt_reference_option_list]
    | [CONSTRAINT [constraint_name]] CHECK(expr) constranit_state
    | [SPATIAL] {INDEX | KEY} [index_name] [index_type] (key_part, ...) [index_option_list]

column_definition:
    column_name data_type [DEFAULT const_value] [AUTO_INCREMENT] [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] [[DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name] [[DEFAULT] COLLATE [=] collation_name] [COMMENT 'string']
    | 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 NULL | NO ACTION | SET DEFAULT}

index_type:
    USING BTREE

key_part:
    {index_col_name [(length)] 
    | (expr)} [ASC]

index_option_list:
    index_option [index_option ...]

index_option:
    GLOBAL
    | LOCAL
    | BLOCK_SIZE integer
    | STORING(column_name_list)
    | COMMENT 'string'

column_name_list:
    column_name [, column_name ...]

table_option_list:
    table_option [table_option ...]

table_option:
    [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | TABLEGROUP [=] tablegroup_name
    | BLOCK_SIZE integer
    | LOB_INROW_THRESHOLD [=] num
    | COMPRESSION [=] 'compression_value'
    | AUTO_INCREMENT [=] int_value
    | COMMENT 'string'
    | TTL (ttl_definition) 
    | ROW_FORMAT [=] row_format_value
    | PCTFREE [=] num
    | parallel_option
    | READ {ONLY | WRITE}
    | DUPLICATE_SCOPE [=] 'none | cluster'
    | TABLE_MODE [=] 'table_mode_value'

compression_value:
    none
    | lz4_1.0
    | zstd_1.0
    | snappy_1.0

row_format_value:
    REDUNDANT
    | COMPACT
    | DYNAMIC
    | COMPRESSED
    | DEFAULT

parallel_option:
    NOPARALLEL
    | PARALLEL integer

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

partition_option:
    partition_and_template_subpartition_option
    | partition_and_individual_subpartition_option

partition_and_template_subpartition_option:
    PARTITION BY RANGE {(expr) | COLUMNS (column_name_list)} [template_subpartition_option] (range_partition_definition_list)
    | PARTITION BY LIST {(expr) | COLUMNS (column_name_list)}[template_subpartition_option] (list_partition_definition_list)
    | PARTITION BY HASH(expr) [template_subpartition_option] {(hash_partition_definition_list) | PARTITIONS partition_num}
    | PARTITION BY KEY(column_name_list) [template_subpartition_option] {(key_partition_definition_list) | PARTITIONS partition_num}

template_subpartition_option:
    SUBPARTITION BY RANGE {(expr) | COLUMNS (column_name_list)} SUBPARTITION TEMPLATE (range_subpartition_definition_list)
    | LIST {(expr) | COLUMNS (column_name_list)} SUBPARTITION TEMPLATE (list_subpartition_definition_list)
    | HASH(expr) {SUBPARTITION TEMPLATE (hash_subpartition_definition_list)
                  | SUBPARTITIONS subpartition_num}
    | KEY(column_name_list) {SUBPARTITION TEMPLATE (key_subpartition_definition_list)
                            | SUBPARTITIONS subpartition_num}

range_subpartition_definition_list:
    range_subpartition_definition [, range_subpartition_definition ...]

range_subpartition_definition:
    SUBPARTITION subpartition_name VALUES LESS THAN (expr | MAXVALUE)

range_partition_definition_list:
    range_partition_definition [, range_partition_definition ...]

range_partition_definition:
    PARTITION partition_name VALUES LESS THAN (expr | MAXVALUE)

list_subpartition_definition_list:
    list_subpartition_definition [, list_subpartition_definition ...]

list_subpartition_definition:
    SUBPARTITION subpartition_name VALUES IN (value_list | DEFAULT)

list_partition_definition_list:
    list_partition_definition [, list_partition_definition ...]

list_partition_definition:
    PARTITION partition_name VALUES IN (value_list | DEFAULT)

hash_subpartition_definition_list:
    hash_subpartition_definition [, hash_subpartition_definition ...]

hash_subpartition_definition:
    SUBPARTITION subpartition_name

hash_partition_definition_list:
    hash_partition_definition [, hash_partition_definition ...]

hash_partition_definition:
    PARTITION partition_name

key_subpartition_definition_list:
    key_subpartition_definition [, key_subpartition_definition ...]

key_subpartition_definition:
    SUBPARTITION subpartition_name

key_partition_definition_list:
    key_partition_definition [, key_partition_definition ...]

key_partition_definition:
    PARTITION partition_name

partition_and_individual_subpartition_option:
    PARTITION BY RANGE {(expr) | COLUMNS(column_name_list)} [individual_subpartition_option]
        {(range_partition_definition [(subpartition_definition)]
          [, range_partition_definition [(subpartition_definition)] ... ]
          )}
    | PARTITION BY LIST {(expr) | COLUMNS (column_name_list)} [individual_subpartition_option]
          {(list_partition_definition [(subpartition_definition)]
            [, list_partition_definition [(subpartition_definition)] ...]
            )}
    | PARTITION BY HASH(expr) [individual_subpartition_option]
          {(hash_partition_definition [(subpartition_definition)]
            [, hash_partition_definition [(subpartition_definition)] ...]
            )}
    | PARTITION BY KEY(column_name_list) [individual_subpartition_option]
          {(key_partition_definition [(subpartition_definition)]
            [, key_partition_definition [(subpartition_definition)] ...]
            )}

individual_subpartition_option:
    SUBPARTITION BY RANGE {(expr) | COLUMNS (column_name_list)}
    | SUBPARTITION BY LIST {(expr) | COLUMNS (column_name_list)}
    | SUBPARTITION BY HASH (expr)
    | SUBPARTITION BY KEY(column_name_list)

subpartition_definition:
    range_subpartition_definition_list
    | list_subpartition_definition_list
    | hash_subpartition_definition_list
    | key_subpartition_definition_list

Parameters

table_definition

• column_definition: defines the columns of a table, including the column name, data type, and constraints. For more information, see column_definition.

• [CONSTRAINT [constraint_name]]: an optional parameter to specify a constraint and name it (constraint_name). When you specify a constraint name while creating a primary key, the syntax is supported but the feature is not implemented.

  • constraint_name: the name of the constraint. If you do not specify this parameter, the system generates a name for the constraint. The following restrictions apply to the constraint name:

    • It must be at most 64 characters long.
    • It can contain spaces in the beginning, at the end, or between words, but the spaces must be enclosed with "`".
    • It can contain the special character "$".
    • If the constraint name is a reserved word, it must be enclosed with "`", otherwise, an error occurs.
    • The constraint names of CHECK constraints must be unique within the same database.

  OceanBase Database allows you to specify constraint names for primary keys, unique keys, foreign keys, and CHECK constraints. The syntax is as follows:

  • PRIMARY KEY index_desc: specifies the primary key constraint for a table. You can specify one or more columns as the primary key. If multiple columns are specified, they will form a composite primary key. If you do not specify any columns, a hidden primary key will be generated. You can use the ALTER TABLE statement to modify the primary key of a table or add a primary key to a table in OceanBase Database. For more information, see ALTER TABLE.

    Here is an example:

    Create a table named tbl1 that contains two columns: col1 and col2. Specify col1 as the primary key and name the constraint tbl1_col1_pk.

    CREATE TABLE tbl1 (col1 INT, col2 INT, CONSTRAINT tbl1_col1_pk PRIMARY KEY (col1));
    

  • UNIQUE {INDEX | KEY} [unique_index_name] index_desc: specifies the unique constraint for a table.

    • unique_index_name: the name of the unique constraint. It is an optional parameter.

    Here is an example:

    Create a table named tbl2 that contains two columns: col1 and col2. Specify the col2 column with the unique constraint and name the constraint tbl2_col2_uk.

    CREATE TABLE tbl2 (col1 INT, col2 VARCHAR(50), CONSTRAINT tbl2_col2_uk UNIQUE KEY (col2));
    

    or

    CREATE TABLE tbl2 (col1 INT, col2 VARCHAR(50), UNIQUE KEY tbl2_col2_uk (col2));
    

  • FOREIGN KEY [foreign_index_name] index_desc REFERENCES source_tbl_name (source_key_part) [match_action] [opt_reference_option_list]: specifies the foreign key constraint for a table.

    • foreign_index_name: the name of the foreign key constraint. If you do not specify this parameter, the system will generate a name for the foreign key constraint. The naming rule is as follows: table name + OBFK + creation time. For example, the foreign key constraint created for the t1 table at 00:00:00 on August 1, 2021, will be named t1_OBFK_1627747200000000.

    • source_tbl_name: the name of the source table referenced by the foreign key.

    • source_key_part: the name of the column in the source table that will be associated with the target table.

    • opt_reference_option_list: the list of options that specifies the behavior when an update or deletion occurs in the reference table. The foreign key allows cross-table references. When an UPDATE or DELETE operation affects a matching row in a child table, the result depends on the reference action specified in the ON UPDATE and ON DELETE clauses:

      • CASCADE: the corresponding row in the parent table is deleted or updated, and the corresponding rows in the child table are automatically deleted or updated.
      • SET NULL: the corresponding row in the parent table is deleted or updated, and the foreign key column in the child table is set to NULL.
      • RESTRICT: the deletion or update of the corresponding row in the parent table is restricted.
      • NO ACTION: the check is delayed.
      • SET DEFAULT: the foreign key column in the child table is set to the default value when the corresponding item in the parent table is deleted.

    Here is an example:

    1. Create a table named tbl3 that contains two columns: col1 and col2. Specify col1 as the primary key.

       CREATE TABLE tbl3 (col1 INT PRIMARY KEY, col2 VARCHAR(50));
       

    2. Create a table named tbl4 that contains three columns: col1, col2, and col3. Specify col1 as the primary key and col3 as the foreign key. The foreign key references the col1 column in the tbl3 table. In this foreign key constraint, the ON DELETE CASCADE option specifies that the corresponding rows in the tbl4 table will be deleted when the corresponding row in the tbl3 table is deleted.

       CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 VARCHAR(50), col3 INT, CONSTRAINT tbl4_col3_fk FOREIGN KEY (col3) REFERENCES tbl3(col1) ON DELETE CASCADE);
       

       or

       CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 VARCHAR(50), col3 INT, FOREIGN KEY tbl4_col3_fk (col3) REFERENCES tbl3(col1) ON DELETE CASCADE);
       

  • CHECK(expr) constranit_state: specifies the check constraint for a table, which limits the value range of a column.

    • If you specify a CHECK constraint for a single column, the column-level constraint can be written in the column definition and can have a name.

    • If you specify a CHECK constraint for a table, the constraint will be applied to multiple columns in the table and can be written before or after the column definition. The CHECK constraints that you create for a table will be dropped when you drop the table.

    • You can use the following methods to view the constraints:

      • Execute the SHOW CREATE TABLE statement.
      • Query the information_schema.TABLE_CONSTRAINTS view.
      • Query the information_schema.CHECK_CONSTRAINTS view.

    • expr: the constraint expression. It must meet the following requirements:

      • expr cannot be empty.
      • The result of expr must be of the Boolean type.
      • expr must not contain a non-existent column.

    Here is an example:

    Create a table named tbl5 that contains two columns: col1 and col2. Specify the CHECK constraint for the col2 column and name the constraint tbl5_col2_check.

    CREATE TABLE tbl5 (col1 INT, col2 DECIMAL(10, 2), CONSTRAINT tbl5_col2_check CHECK (col2 >= 1000 AND col2 <= 10000));
    

• [SPATIAL] {INDEX | KEY} [index_name] [index_type] (key_part, ...) [index_option_list]: specifies the index of a table, including the index name, index type, and column name.

  • SPATIAL: specifies to create a spatial index. For more information about spatial indexes, see Spatial indexes.

  • INDEX | KEY: specifies to create a key or index. These two keywords are equivalent.

  • index_name: the name of the index. It is an optional parameter. If you do not specify this parameter, the first column referenced in the index will be used as the index name. If column names are duplicated, the names will be modified by adding underscores (_) and sequence numbers. (For example, if the index is created based on the c1 column and a column with the same name exists in the table, the index name will be modified to c1_2. ) You can execute the SHOW INDEX statement to view the indexes on a table.

  • index_type: the type of the index. It is an optional parameter.

  • key_part: the name of the column or expression to be included in the index.

    • index_col_name [(length)]: the name of a column in the table as the index column. You can use the length parameter to specify the length of the index column. For example, you can use id(10) to specify the id column as the index column and use only the first 10 characters of the id column as the index.

    • expr: a valid function-based index expression. It can be a Boolean expression, for example, c1=c1. In the MySQL mode of OceanBase Database, expressions of some system functions are not supported as function-based index keys. For more information, see Function-based index supported system functions and Function-based index not supported system functions.

      Notice

      OceanBase Database currently does not allow function-based indexes to be created on generated columns.

    • ASC: the ascending order. The DESC (descending order) is not supported at present. You can add this parameter to indicate to sort the index in the ascending order.

  • index_option_list: the list of index options. Multiple index options are separated with spaces. The following index options are supported:

    • GLOBAL: specifies to create a global index.
    • LOCAL: specifies to create a local index. This is the default value.
    • BLOCK_SIZE integer: the size of an index block, which is the number of bytes in each index block.
    • STORING(column_name_list): the columns to store in the index. Multiple columns are separated with commas (,).
    • COMMENT 'string': adds a comment to the index.

  Here is an example:

  • Create a table named tbl6 that contains two columns: col1 and col2. Create an index on the col2 column and name the index tbl6_col2_idx.

    CREATE TABLE tbl6 (col1 INT, col2 VARCHAR(50) NOT NULL, INDEX tbl6_col2_idx (col2));
    

  • Create a table named tbl7 that contains two columns: col1 and col2. Create an index named tbl7_col1_idx on the result of (col1+1).

    CREATE TABLE tbl7 (col1 INT, col2 INT, INDEX tbl7_col1_idx ((col1+1)));
    

column_definition

• column_name data_type [DEFAULT const_value] [AUTO_INCREMENT] [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] [[DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name] [[DEFAULT] COLLATE [=] collation_name] [COMMENT 'string']:

  • column_name: the name of the column. The column name can be up to 128 bytes long.

  • data_type: the data type of the column, such as integer, text, and date. For more information about the data types supported in the MySQL mode of OceanBase Database, see Overview.

  • [DEFAULT const_value]: the default value of the column. This is an optional parameter.

  • [AUTO_INCREMENT]: specifies whether the column is an auto-increment column. Currently, only integer columns (BOOL/BOOLEAN columns excluded) can be set as auto-increment columns. OceanBase Database allows you to use auto-increment columns as partitioning keys. For more information about auto-increment columns, see Define an auto-increment column.

  • [NULL | NOT NULL]: specifies whether the column can contain NULL values or not. If you do not specify this parameter, the column can contain NULL values. NULL indicates no value.

  • [[PRIMARY] KEY]: specifies whether the column is the primary key. If you specify the column as the primary key, its values must be unique and not null.

  • [UNIQUE [KEY]]: specifies whether the column is a unique key. This ensures that the values in the column are unique.

  • [[DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name]: the default character set of the column. For more information about the character sets supported in the MySQL mode of OceanBase Database, see Character set.

  • [DEFAULT] COLLATE [=] collation_name: the default collation of the column. For more information about the collations supported in the MySQL mode of OceanBase Database, see Collation.

  • [COMMENT 'string']: a comment on the column. This is an optional parameter.

  Here is an example:

  Create a table named tbl8 that contains four columns. The col1 column is an auto-increment column and the primary key. The default value of the col2 column is 0. The col3 column has a unique key constraint. The col4 column uses the GBK character set and the gbk_bin collation. A comment 'test_comment' is added to each column.

  CREATE TABLE tbl8 (col1 INT AUTO_INCREMENT PRIMARY KEY COMMENT 'test_comment',
    col2 INT DEFAULT 0 COMMENT 'test_comment',
    col3 VARCHAR(10) UNIQUE KEY COMMENT 'test_comment',
    col4 VARCHAR(64) CHARSET GBK COLLATE gbk_bin COMMENT 'test_comment'
    );
  

• column_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [opt_generated_column_attribute]:

  • column_name: the name of the new column.

  • data_type: the data type of the new column.

  • [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]: creates a generated column.

    • [GENERATED ALWAYS] AS (expr): the expression for calculating the values of the new column.

    • [VIRTUAL | STORED]: specifies whether the generated column is a virtual column or a stored column.

      • VIRTUAL: the values of the column are not stored. Instead, the values are calculated when reading rows, after any BEFORE triggers. A virtual column does not occupy storage space.
      • STORED: the values of the column are calculated and stored when inserting or updating rows. A stored column occupies storage space and can be indexed.

  • [opt_generated_column_attribute]: other attributes of the generated column, such as the comment on the column.

  Here is an example:

  Create a table named tbl9 that contains a virtual generated column named col4. The data type of col4 is VARCHAR(100). The value of col4 is the concatenation of the values of col2 and col3.

  CREATE TABLE tbl9 (
      col1 INT,
      col2 VARCHAR(50),
      col3 VARCHAR(50),
      col4 VARCHAR(100) GENERATED ALWAYS AS (CONCAT(col2, ' ', col3)) VIRTUAL
  );
  

table_option

• [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name: specifies the default character set for the table. For more information about character sets in MySQL mode of OceanBase Database, see Character sets and collations.

• [DEFAULT] COLLATE [=] collation_name: specifies the default collation for the table. For more information about collations in MySQL mode of OceanBase Database, see Collations.

• TABLEGROUP [=] tablegroup_name: specifies the table group to which the table belongs.

• BLOCK_SIZE integer: specifies the microblock size of the table.

• LOB_INROW_THRESHOLD [=] num: specifies the INROW threshold for LOB data. When the size of LOB data exceeds this threshold, the data is stored as an OUTROW in the LOB meta table. The default value is 4 KB.

• COMPRESSION [=] 'compression_value': specifies the compression algorithm for the table. Valid values include:

  • none: no compression algorithm is used.
  • lz4_1.0: the lz4 compression algorithm is used.
  • zstd_1.0: the zstd compression algorithm is used.
  • snappy_1.0: the snappy compression algorithm is used.

• AUTO_INCREMENT [=] int_value: specifies the initial value (start value) of the auto-increment column in the table.

• COMMENT 'string': specifies the comment on the table. The comment is case-insensitive.

• TTL (ttl_definition): Time To Live, specifies to delete expired data. For more information, see Delete expired data.

• ROW_FORMAT [=] row_format_value: specifies whether to enable the Encoding storage format for the table. Valid values include:

  • REDUNDANT: the Encoding storage format is disabled.
  • COMPACT: the Encoding storage format is disabled.
  • DYNAMIC: the Encoding storage format is enabled.
  • COMPRESSED: the Encoding storage format is enabled.
  • DEFAULT: equivalent to dynamic mode.

• PCTFREE [=] num: specifies the percentage of space reserved for macroblock overheads.

• parallel_option: specifies the parallelism at the table level. Valid values include:

  • NOPARALLEL: the parallelism is set to 1. This is the default value.
  • PARALLEL integer: the specified parallelism. The value of integer must be greater than or equal to 1.

• READ {ONLY | WRITE}: specifies the read and write permissions on the table. Valid values include:

  • READ ONLY: indicates that the table can only be read and not modified or deleted.

    Notice

    The READ ONLY parameter at the table level does not take effect for users with the SUPER privilege and takes effect only for common users.

  • READ WRITE: the default value, indicating that the table can be read, modified, and deleted.

• DUPLICATE_SCOPE [=] 'none | cluster': specifies the attribute of a replicated table. Valid values include:

  • none: indicates that the table is a normal table. This is the default value.
  • cluster: indicates that the table is a replicated table. In this case, the leader needs to copy transactions to all full-featured (F) and read-only (R) replicas in the current tenant. OceanBase Database supports replicated tables only at the cluster level.

  For more information about replicated tables, see the Create a replicated table section in Create a table.

• TABLE_MODE [=] 'table_mode_value': specifies the threshold for triggering a major compaction and the major compaction strategy, namely, the behavior of the system after data is dumped. Valid values include:

  Note

  Except for the NORMAL mode, all TABLE_MODE modes listed below represent QUEUING tables. A QUEUING table is the most basic type of table. The subsequent modes (excluding the NORMAL mode) represent tables that use more aggressive major compaction strategies.

  • NORMAL: the default value. In this mode, the probability of triggering a major compaction after data is dumped is very low.
  • QUEUING: In this mode, the probability of triggering a major compaction after data is dumped is low.
  • MODERATE: indicates a moderate probability. In this mode, the probability of triggering a major compaction after data is dumped is moderate.
  • SUPER: indicates a high probability. In this mode, the probability of triggering a major compaction after data is dumped is high.
  • EXTREME: indicates an increased probability. In this mode, the probability of triggering a major compaction after data is dumped is relatively high.

  For more information about major compactions, see Adaptive major compaction.

  Here is an example:

  Create a table named tbl10, enable the Encoding storage format, use the zstd compression algorithm, set the percentage of space reserved for macroblock overheads to 5%, and set the parallelism to 3.

  CREATE TABLE tbl10 (col1 INT AUTO_INCREMENT, col2 INT, col3 VARCHAR(64))
    ROW_FORMAT DYNAMIC
    COMPRESSION 'zstd_1.0'
    PCTFREE 5
    PARALLEL 3;
  

partition_option

• partition_and_template_subpartition_option: specifies the partitioning and template-based subpartitioning options. The MySQL mode of OceanBase Database supports the following partitioning types: RANGE partitioning, RANGE COLUMNS partitioning, LIST partitioning, LIST COLUMNS partitioning, HASH partitioning, KEY partitioning, and composite partitioning (subpartitioning). For more information, see Partition overview.

  • template_subpartition_option: the template-based subpartitioning option. It is a optional parameter.

    Note

    • For a template-based subpartitioned table, each subpartition in a partition is defined based on the template, namely, subpartitions in the same partition have the same definition.
    • For a template-based subpartitioned table, after subpartitions are defined, the naming rule for each subpartition is ($part_name)s($subpart_name). You can execute the SELECT table_name,partition_name,subpartition_name FROM information_schema.partitions; statement to view the names of subpartitions.

  • range_partition_definition_list: a list of partition definitions for RANGE and RANGE COLUMNS partitioning.

  • list_partition_definition_list: a list of partition definitions for LIST and LIST COLUMNS partitioning.

  • hash_partition_definition_list: a list of partition definitions for HASH partitioning. Custom partition names are supported.

  • key_partition_definition_list: a list of partition definitions for KEY partitioning. Custom partition names are supported.

  • PARTITIONS partition_num: the number of partitions. Custom partition names are supported. This option applies to HASH and KEY partitioning.

• partition_and_individual_subpartition_option: specifies the partitioning and non-template-based (custom) subpartitioning options.

partition_definition/subpartition_definition

• PARTITION partition_name VALUES LESS THAN (expr | MAXVALUE)/SUBPARTITION subpartition_name VALUES LESS THAN (expr | MAXVALUE): specifies the definition of a partition or subpartition in a RANGE or RANGE COLUMNS partitioned table. The upper limit of the last partition can be set to MAXVALUE. This value does not have a specific numeric value and is greater than the upper limits of all other partitions. It also includes null values. If the last RANGE partition is specified with MAXVALUE, no new partition can be added.

• PARTITION partition_name VALUES IN (value_list | DEFAULT)/SUBPARTITION subpartition_name VALUES IN (value_list | DEFAULT): specifies the definition of a partition or subpartition in a LIST or LIST COLUMNS partitioned table. The upper limit of the last partition can be set to DEFAULT. This value does not have a specific numeric value and is greater than the upper limits of all other partitions. It also includes null values.

• PARTITION partition_name/SUBPARTITION subpartition_name: specifies the name of a partition or subpartition in a HASH or KEY partitioned table.

Here are some examples:

• Create a non-template-based subpartitioned table named tbl13 with five partitions.

  CREATE TABLE tbl11 (col1 INT PRIMARY KEY, col2 INT)
    PARTITION BY HASH(col1)
    PARTITIONS 5;
  

  or

  CREATE TABLE tbl11 (col1 INT PRIMARY KEY, col2 INT)
    PARTITION BY HASH(col1)
    (PARTITION p0,
     PARTITION p1,
     PARTITION p2,
     PARTITION p3,
     PARTITION p4);
  

• Create a template-based subpartitioned table named tbl12 with RANGE partitions and KEY subpartitions.

  CREATE TABLE tbl12 (col1 INT, col2 INT, col3 INT)
    PARTITION BY RANGE(col1)
      SUBPARTITION BY KEY(col2, col3) SUBPARTITIONS 5
    (PARTITION p0 VALUES LESS THAN(0),
    PARTITION p1 VALUES LESS THAN(100));
  

  or

  CREATE TABLE tbl12 (col1 INT, col2 INT, col3 INT)
    PARTITION BY RANGE(col1)
      SUBPARTITION BY KEY(col2, col3)
      SUBPARTITION TEMPLATE 
      (SUBPARTITION mp0,
      SUBPARTITION mp1,
      SUBPARTITION mp2,
      SUBPARTITION mp3,
      SUBPARTITION mp4)
    (PARTITION p0 VALUES LESS THAN(0),
    PARTITION p1 VALUES LESS THAN(100));
  

• Create a non-template-based subpartitioned table named tbl13 with LIST COLUMNS partitions and RANGE subpartitions.

  CREATE TABLE tbl13 (col1 INT, col2 TIMESTAMP)
    PARTITION BY LIST COLUMNS(col1)
      SUBPARTITION BY RANGE(UNIX_TIMESTAMP(col2))
    (PARTITION p0 VALUES IN(1,3)
      (SUBPARTITION sp0 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/31')),
      SUBPARTITION sp1 VALUES LESS THAN(UNIX_TIMESTAMP('2021/12/31')),
      SUBPARTITION sp2 VALUES LESS THAN(UNIX_TIMESTAMP('2022/12/31')),
      SUBPARTITION sp3 VALUES LESS THAN(UNIX_TIMESTAMP('2023/12/31'))
        ),
    PARTITION p1 VALUES IN(4,6)
      (SUBPARTITION sp4 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/31')),
      SUBPARTITION sp5 VALUES LESS THAN(UNIX_TIMESTAMP('2021/12/31')),
      SUBPARTITION sp6 VALUES LESS THAN(UNIX_TIMESTAMP('2022/12/31')),
      SUBPARTITION sp7 VALUES LESS THAN(UNIX_TIMESTAMP('2023/12/31'))
        ),
    PARTITION p2 VALUES IN(7,9)
      (SUBPARTITION sp8 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/31')),
      SUBPARTITION sp9 VALUES LESS THAN(UNIX_TIMESTAMP('2021/12/31')),
      SUBPARTITION sp10 VALUES LESS THAN(UNIX_TIMESTAMP('2022/12/31')),
      SUBPARTITION sp11 VALUES LESS THAN(UNIX_TIMESTAMP('2023/12/31'))
        )
    );
  

For more information about how to create a partitioned table in the MySQL mode of OceanBase Database, see Create a partitioned table.

create_table_select_stmt

• table_definition_list: the column definition list of the table, including the names, data types, and constraints of the columns. For more information, see table_definition.

  • If table_definition_list is not specified, the new table will have the same column names and data types as the columns in the result set of the select_stmt.
  • If table_definition_list is specified, the new table will contain the columns specified in table_definition_list and the columns in the result set of the select_stmt.

• [AS]: an optional keyword that indicates that a table is to be created based on the result set of a query.

• select_stmt: the query statement to be executed. For more information about the structure and options of the query statement, see SELECT statement.

Example

Create a replicated table

Create a cluster-level replicated table named dup_t1.

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

   CREATE RESOURCE UNIT IF NOT EXISTS 2c5g MAX_CPU 2, MEMORY_SIZE '5G';
   

2. Create a resource pool with a unit number of 2.

   CREATE RESOURCE POOL tenant_pool UNIT = '2c5g', UNIT_NUM = 2, ZONE_LIST = ('z1', 'z2', 'z3');
   

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

   CREATE TENANT obmysql resource_pool_list=('tenant_pool'), LOCALITY = "F@z1, F@z2, R@z3", PRIMARY_ZONE = "z1" SET ob_tcp_invited_nodes='%';
   

4. Switch to the test database.

   USE test;
   

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

   CREATE TABLE dup_t1(c1 INT) DUPLICATE_SCOPE = 'cluster';
   

6. (Optional) View the broadcast log stream information. The replicated table is created on the log stream.

   SELECT * FROM oceanbase.DBA_OB_LS WHERE FLAG LIKE "%DUPLICATE%";
   

   The return result is as follows:

   +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
   | 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
   

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

   SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TABLE_NAME = "dup_t1";
   

   The return result is as follows:

   +-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
   | 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
   

8. Insert data into and read data from the replicated table as you would with a normal table. For a read request, if a Proxy is used, the request can be routed to any OBServer node; if direct connection is used, the request will be processed on the OBServer node where the local replica resides.

   1. Insert data.

      INSERT INTO dup_t1 VALUES(1);
      

   2. Query data.

      SELECT * FROM dup_t1;
      

      The return result is as follows:

      +------+
      | c1   |
      +------+
      |    1 |
      +------+
      1 row in set
      

Create a table by copying data from an existing table

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1 (col1 INT(8) PRIMARY KEY, col2 VARCHAR(50), col3 DATE);
   

2. Insert three records into the test_tbl1 table.

   INSERT INTO test_tbl1 VALUES(1, 'A1', '2024-01-01'),(2, 'A2', '2024-01-02'),(3, 'A3', '2024-01-03');
   

3. Create a table named test_tbl2, and copy the column names, data types, and data from the test_tbl1 table.

   CREATE TABLE IF NOT EXISTS test_tbl2
     AS SELECT * FROM test_tbl1;
   

4. Use the DESC command to query the field information of the test_tbl2 table.

   DESC test_tbl2;
   

   The return result is as follows:

   +-------+-------------+------+------+---------+-------+
   | Field | Type        | Null | Key  | Default | Extra |
   +-------+-------------+------+------+---------+-------+
   | col1  | int(8)      | NO   |      | NULL    |       |
   | col2  | varchar(50) | YES  |      | NULL    |       |
   | col3  | date        | YES  |      | NULL    |       |
   +-------+-------------+------+------+---------+-------+
   3 rows in set
   

5. Query the data in the test_tbl2 table.

   SELECT * FROM test_tbl2;
   

   The return result is as follows:

   +------+------+------------+
   | col1 | col2 | col3       |
   +------+------+------------+
   |    1 | A1   | 2024-01-01 |
   |    2 | A2   | 2024-01-02 |
   |    3 | A3   | 2024-01-03 |
   +------+------+------------+
   3 rows in set
   

6. Create a table named test_tbl3, define two columns: c1 and c2, and copy the column names, data types, and data that meets the test_tbl1.col1 = 2 condition from the test_tbl1 table.

   CREATE TABLE IF NOT EXISTS test_tbl3 (c1 INT, c2 INT)
     AS SELECT * FROM test_tbl1
     WHERE test_tbl1.col1 = 2;
   

7. Use the DESC command to query the field information of the test_tbl3 table.

   DESC test_tbl3;
   

   The return result is as follows:

   +-------+-------------+------+------+---------+-------+
   | Field | Type        | Null | Key  | Default | Extra |
   +-------+-------------+------+------+---------+-------+
   | c1    | int(11)     | YES  |      | NULL    |       |
   | c2    | int(11)     | YES  |      | NULL    |       |
   | col1  | int(8)      | NO   |      | NULL    |       |
   | col2  | varchar(50) | YES  |      | NULL    |       |
   | col3  | date        | YES  |      | NULL    |       |
   +-------+-------------+------+------+---------+-------+
   5 rows in set
   

8. Query the data in the test_tbl3 table.

   SELECT * FROM test_tbl3;
   

   The return result is as follows:

   +------+------+------+------+------------+
   | c1   | c2   | col1 | col2 | col3       |
   +------+------+------+------+------------+
   | NULL | NULL |    2 | A2   | 2024-01-02 |
   +------+------+------+------+------------+
   1 row in set
   

Create a table by copying the schema of an existing table

1. Create a table named test_tbl4.

   CREATE TABLE test_tbl4 (col1 INT(8) PRIMARY KEY, col2 VARCHAR(50),
     INDEX test_col2_idx (col2))
       PARALLEL 3
       PARTITION BY RANGE(col1)
       (PARTITION p0 VALUES LESS THAN(100),
        PARTITION p1 VALUES LESS THAN(200),
        PARTITION p2 VALUES LESS THAN(300));
   

2. Create a table named test_tbl5, and copy the column names, data types, and data from the test_tbl4 table.

   CREATE TABLE test_tbl5 LIKE test_tbl4;
   

3. View the table definition of the test_tbl5 table.

   SHOW CREATE TABLE test_tbl5;
   

   The return result is as follows:

   +-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
   +-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | test_tbl5 | CREATE TABLE `test_tbl5` (
     `col1` int(8) NOT NULL,
     `col2` varchar(50) DEFAULT NULL,
     PRIMARY KEY (`col1`),
     KEY `test_col2_idx` (`col2`) BLOCK_SIZE 16384 LOCAL
   ) 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 PARALLEL 3
   partition by range(col1)
   (partition `p0` values less than (100),
   partition `p1` values less than (200),
   partition `p2` values less than (300)) |
   +-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

References

• ALTER TABLE
• DROP TABLE
• INSERT