CREATE TABLE

Description

This statement is used to create a new table in the database.

Syntax

CREATE [hint_options] [TEMPORARY] TABLE [IF NOT EXISTS] table_name
      (table_definition_list) [table_option_list] [partition_option] [[MERGE_ENGINE = {delete_insert | partial_update}] table_column_group_option] [IGNORE | REPLACE] [AS] select;

CREATE TABLE [TEMPORARY] [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]
    | [FULLTEXT] {INDEX | KEY} [index_name] [index_type] (key_part,...) [WITH PARSER tokenizer_option] [PARSER_PROPERTIES[=](parser_properties_list)]
      [index_option_list] [index_column_group_option]
    | index_json_clause
    | [CONSTRAINT [constraint_name]] CHECK(expression) constranit_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}

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

parser_properties_list:
    parser_properties, [parser_properties]

parser_properties:
    min_token_size = int_value
    | max_token_size = int_value
    | ngram_token_size = int_value
    | ik_mode = 'char_value'
    | min_ngram_size = int_value
    | max_ngram_size = int_value

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
    | 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
    | TABLE_MODE [=] 'table_mode_value'
    | auto_increment_cache_size [=] INT_VALUE
    | READ {ONLY | WRITE}
    | ORGANIZATION [=] {INDEX | HEAP}
    | enable_macro_block_bloom_filter [=] {True | False}
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    | SEMISTRUCT_ENCODING_TYPE [=] 'encoding'

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | TIME_ZONE = {'default' | 'time_zone'}
    | BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}

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
    | PARTITION BY RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')] (range_partition_list)

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)

index_json_clause:
    [UNIQUE] INDEX idx_json_name((CAST(json_column_name->'$.json_field_name' AS UNSIGNED ARRAY)))
    | INDEX idx_json_name(column_name, [column_name, ...] (CAST(json_column_name->'$.json_field_name' AS CHAR(n) ARRAY)))

Parameters

Parameter	Description
hint_options	Optional. Specifies the Hint options. You can manually specify the bypass import Hint, including APPEND, DIRECT, and NO_DIRECT. The corresponding Hint format is /*+ [APPEND | DIRECT(need_sort,max_error,load_type)] parallel(N) |NO_DIRECT */. For more information about how to bypass import data by using the CREATE TABLE AS SELECT statement, see the Use CREATE TABLE AS SELECT to bypass import data section in Full bypass import.
TEMPORARY	Optional. Indicates that the table is a temporary table.
IF NOT EXISTS	If you specify IF NOT EXISTS, an error will not be returned even if the table to be created already exists. If you do not specify this option and the table to be created already exists, an error will be returned.
IGNORE | REPLACE	Optional. Specifies how to handle rows with duplicate unique key values when you use the CREATE TABLE ... SELECT statement. If you do not specify IGNORE or REPLACE, an error will be returned when there are rows with duplicate unique key values. IGNORE: If a unique key value in the new table conflicts with that in a row of the copied data, the conflicting row will be ignored and will not be copied to the new table. In other words, the IGNORE option skips duplicate data and copies only rows that do not cause unique key conflicts. REPLACE: When there are rows with duplicate unique key values, the rows in the new table will be replaced with those in the copied data.
PRIMARY KEY	Specifies the primary key of the created table. If you do not specify this option, a hidden primary key will be used. seekdb allows you to modify the primary key of a table or add a primary key to a table by using the ALTER TABLE statement. For more information, see ALTER TABLE.
FOREIGN KEY	Specifies the foreign key of the created table. If you do not specify the foreign key name, the foreign key name will be automatically generated as the table name + OBFK + the creation time. For example, the foreign key name created for the t1 table on August 1, 2021, 00:00:00 is t1_OBFK_1627747200000000. A foreign key allows cross-table referencing of related data. When the UPDATE or DELETE operation affects the key values in the parent table that match the rows in the child table, the result depends on the ON UPDATE and ON DELETE clauses: CASCADE: Indicates that when a row is deleted or updated in the parent table, the corresponding row in the child table is automatically deleted or updated. SET NULL: Indicates that when a row is deleted or updated in the parent table, the foreign key column in the child table is set to NULL. RESTRICT: Indicates that the deletion or update of a row in the parent table is rejected. NO ACTION: Specifies delayed checking. Additionally, the SET DEFAULT operation is supported.
FULLTEXT	Optional. Specifies whether to create a full-text index. For more information about how to create a full-text index, see the Create a full-text index section in Create an index. Notice: Only local full-text indexes are supported.
WITH PARSER tokenizer_option	Optional. Specifies the tokenizer for the full-text index. For more information, see tokenizer_option below.
PARSER_PROPERTIES[=](parser_properties_list)	Optional. Specifies the properties of the tokenizer. For more information, see parser_properties below.
KEY | INDEX	Specifies the key or index of the created table. If you do not specify the index name, the index name will be automatically generated as the first column referenced by the index. If the index name is duplicated, the index name will be generated as the column name + underscore (_) + sequence number. For example, if the index name is duplicated for the index created by using the c1 column, the index name will be c1_2. You can execute the SHOW INDEX statement to view the indexes on a table.
key_part	Creates a function index.
index_col_name	Specifies the column name of the index. The ASC option is supported after each column name, but the DESC option is not supported. The default is ASC. The sorting order for creating an index is as follows: first, the values in the first column of index_col_name are sorted; then, the values in the next column are sorted for the records with the same value in the first column; and so on.
expr	Specifies a valid function index expression. Boolean expressions are also supported. For example, c1=c1. Notice: In the current version of seekdb, you cannot create a function index on a generated column.
ROW_FORMAT	Specifies whether to enable the Encoding storage format for the table. redundant: The Encoding storage format is not enabled. compact: The Encoding storage format is not enabled. dynamic: The Encoding storage format is enabled. compressed: The Encoding storage format is enabled. default: Equivalent to the dynamic mode.
[GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]	Creates a generated column. expr specifies the expression for computing the column value. VIRTUAL: The column value is not stored. Instead, it is computed immediately after any BEFORE trigger when a row is read. Virtual columns do not occupy storage space. STORED: The column value is evaluated and stored when a row is inserted or updated. Stored columns occupy storage space and can be indexed.
BLOCK_SIZE	Specifies the microblock size of the table.
lob_inrow_threshold	Specifies the INROW threshold for the LOB field. When the size of a LOB field exceeds this threshold, the excess data is stored in the LOB Meta table as OUTROW. The default value is controlled by the ob_default_lob_inrow_threshold variable.
COMPRESSION	Specifies the compression algorithm for the table. Valid values: 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.
CHARSET | CHARACTER SET	Specifies the default character set for columns in the table. For more information about supported character sets, see Character sets.
COLLATE	Specifies the default collation for columns in the table. For more information about supported collations, see Collations.
AUTO_INCREMENT	Specifies the initial value for an auto-increment column in the table. SeekDB supports using an auto-increment column as a partitioning key.
COMMENT	A comment. The comment is not case-sensitive.
PCTFREE	Specifies the percentage of space reserved for macroblocks.
parallel_clause	Specifies the parallelism at the table level: NOPARALLEL: the parallelism is 1, which is the default configuration PARALLEL integer: specifies the parallelism, where integer is greater than or equal to 1.
CHECK	Restricts the range of values in a column. If you define a CHECK constraint for a single column, you can specify the constraint in the column definition and provide a name for it. If you define a CHECK constraint for a table, the constraint applies to multiple columns in the table and can appear before the column definitions. When the table is dropped, the CHECK constraints created in the table are also dropped. You can view constraint information by using the following methods: Use the SHOW CREATE TABLE command Query the information_schema.TABLE_CONSTRAINTS view Query the information_schema.CHECK_CONSTRAINTS view
constraint_name	The constraint name, which can contain a maximum of 64 characters. The constraint name can contain spaces at the beginning, middle, and end, but you must use the backtick (`) to enclose the name. The constraint name can contain the special character "$". If the constraint name is a reserved word, you must enclose it in backticks (`) to avoid errors. The names of CHECK constraints in the same database must be unique.
expression	The constraint expression. expression cannot be empty. The result of expression must be a boolean value. expression cannot contain columns that do not exist.
MERGE_ENGINE = {delete_insert | partial_update}	Optional. Specifies the update model for the table. Valid values: partial_update: the default value, indicating that the existing update model remains unchanged. delete_insert: indicates that the merge on write model is used, where update operations are converted to delete and insert operations, and all columns are written. Note After you specify the MERGE_ENGINE parameter when you create a table, you cannot modify its value. SeekDB supports the MERGE_ENGINE parameter.
table_column_group_option/index_column_group_option	Specifies the columnar storage options for a table or index. The following table describes the options. WITH COLUMN GROUP(all columns, each column): specifies to create a rowstore or columnstore table or index with redundant data. WITH COLUMN GROUP(all columns): specifies to add a rowstore table or index. WITH COLUMN GROUP(each column): specifies to create a columnstore table or index.
SKIP_INDEX	Specifies the Skip Index attribute for a column. Valid values: MIN_MAX: the most general aggregate data type in Skip Index. It stores the maximum value, minimum value, and null count of the indexed column at the index node level. This type of data can accelerate the pushdown of filters and MIN/MAX aggregates. SUM: accelerates the pushdown of SUM aggregates for numeric data types. Notice You cannot create a Skip Index for columns of the JSON or spatial data type. You cannot create a Skip Index for generated columns.
index_json_clause	Specifies the clause for creating a multi-value index. For more information about multi-value indexes, see the Create a multi-value index section in Create an index. Notice The multi-value index feature is currently in the experimental stage and is not recommended for use in production environments to avoid affecting system stability.
json_column_name	The name of the column of the JSON data type in the table.
idx_json_name	The name of the multi-value index to be created.
json_field_name	The name of the field in the JSON column to be indexed.
TABLE_MODE	Optional. Specifies the merge trigger threshold and merge strategy, which control the merge behavior after data is dumped. For more information about the values, see table_mode_value.
auto_increment_cache_size	Optional. Specifies the number of cached auto-increment values. The default value is 0, indicating that this parameter is not configured. In this case, the system uses the value of the auto_increment_cache_size parameter as the cache size for the auto-increment column.
READ {ONLY | WRITE}	Specifies the read/write permissions for the table. Valid values: READ ONLY: indicates that the table can be read but not modified or deleted. Notice The READ ONLY parameter at the table level does not take effect for users with the SUPER privilege. It only takes effect for ordinary users. READ WRITE: the default value, indicating that the table can be read, modified, and deleted.
ORGANIZATION [=] {INDEX | HEAP}	Specifies the storage order of data rows in the table, that is, the table organization mode. Valid values: INDEX: specifies an index-organized table. HEAP: specifies a heap-organized table. If you do not specify the ORGANIZATION option, the value of this option is the same as that of the default_table_organization parameter. Notice The CREATE TABLE statement in seekdb supports the ORGANIZATION option.
enable_macro_block_bloom_filter [=] {True | False}	Specifies whether to persist the bloom filter at the macroblock level. Valid values: True: enables the persistence of the bloom filter at the macroblock level. False: disables the persistence of the bloom filter at the macroblock level. Notice The CREATE TABLE statement in seekdb supports the enable_macro_block_bloom_filter option.
DYNAMIC_PARTITION_POLICY [=] $dynamic_partition_policy_list$	Specifies the dynamic partition management attributes of the table to enable automatic creation and deletion of partitions. dynamic_partition_policy_list specifies the configurable parameters of the dynamic partition strategy. Separate the parameters with commas. For more information, see dynamic_partition_policy_option below. Notice seekdb supports specifying the dynamic partition management attributes of the table.
PARTITION BY RANGE [COLUMNS]($column_name_list$) [SIZE$'size_value'$]	Specifies the creation of an automatic partition table. For more information, see the automatic partition syntax in the Create a table section.
SEMISTRUCT_ENCODING_TYPE	Optional. Specifies the encoding type of semi-structured data. Valid values: '': disables semi-structured encoding. 'encoding': enables semi-structured encoding. For more information about how to use semi-structured encoding, see Use semi-structured encoding.

table_mode_value

info

In the following TABLE_MODE modes, all modes except NORMAL represent QUEUING tables. The QUEUING table is the most basic table type. The following modes (except NORMAL) represent more aggressive compaction strategies.

• NORMAL: The default value, indicating normal mode. 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 moderate mode. In this mode, the probability of triggering a major compaction after data is dumped is moderate.
• SUPER: Indicates super mode. In this mode, the probability of triggering a major compaction after data is dumped is high.
• EXTREME: Indicates extreme mode. In this mode, the probability of triggering a major compaction after data is dumped is high.

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

tokenizer_option

• SPACE: The default value, indicating that the tokenizer splits data by spaces. You can specify the following parameters:

  Parameter	Value Range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• NGRAM: Indicates that the tokenizer splits data by N-grams (Chinese). You can specify the following parameters:

  Parameter	Value Range
  ngram_token_size	[1, 10]

• NGRAM2: Indicates that the tokenizer splits data into continuous characters in the range of min_ngram_size to max_ngram_size. You can specify the following parameters:

  Parameter	Value Range
  min_ngram_size	[1, 16]
  max_ngram_size	[1, 16]

• BENG: Indicates that the tokenizer splits data by Beng (basic English). You can specify the following parameters:

  Parameter	Value Range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• IK: Indicates that the tokenizer splits data by IK (Chinese). Currently, only the utf-8 character set is supported. You can specify the following parameter:

  Parameter	Value Range
  ik_mode	smart max_word

parser_properties

• min_token_size: The minimum token length. Default value: 3. Value range: 1 to 16.

• max_token_size: The maximum token length. Default value: 84. Value range: 10 to 84.

• ngram_token_size: The token length for the NGRAM tokenizer. This parameter is valid only for the NGRAM tokenizer. Default value: 2. Value range: 1 to 10.

• ik_mode: The tokenization mode for the IK tokenizer. Valid values:

  • smart: The default value. The dictionary is used to improve the accuracy of tokenization. The boundaries of the dictionary words are prioritized, which may reduce unnecessary expansions.
  • max_word: The dictionary words are recognized, but the maximum expansion of tokenization is not affected. The max_word mode still tries to split the text into more words, even if the dictionary contains the words.

• min_ngram_size: The minimum token length. Value range: [1,16].

• max_ngram_size: The maximum token length. Value range: [1,16].

Here is an example:

CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT,
    FULLTEXT INDEX fidx(col2) WITH PARSER ik PARSER_PROPERTIES=(ik_mode='max_word'));

dynamic_partition_policy_option

• ENABLE = {true | false}: Optional. Specifies whether to enable dynamic partitioning. Valid values:

  • true: The default value. Indicates that dynamic partitioning is enabled.
  • false: Indicates that dynamic partitioning is disabled.

• TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}: Required. Specifies the time unit for partitioning, that is, the interval for automatically creating partition boundaries. Valid values:

  • hour: Partitions are created by hour.
  • day: Partitions are created by day.
  • week: Partitions are created by week.
  • month: Partitions are created by month.
  • year: Partitions are created by year.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the precreation time. When dynamic partitioning is scheduled, partitions are precreated so that max_partition_upper_bound > now() + precreate_time. Valid values:

  • -1: The default value. Indicates that no partitions are precreated.
  • 0: Indicates that only the current partition is precreated.
  • n {hour | day | week | month | year}: Indicates that partitions are precreated for the specified time span. For example, 3 hour indicates that partitions are precreated for the last 3 hours.
  info

  • When multiple partitions are to be precreated, the interval between the partition boundaries is TIME_UNIT.

  • The first precreated partition boundary is the ceiling of the current maximum partition boundary based on TIME_UNIT.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the expiration time of a partition. When dynamic partitioning is scheduled, all partitions with upper bounds less than now() - expire_time are deleted. Valid values:

  • -1: The default value. Indicates that the partition never expires.
  • 0: Indicates that all partitions except the current one are expired.
  • n {hour | day | week | month | year}: Indicates the expiration time of a partition. For example, 1 day indicates that the partition expires after 1 day.

• TIME_ZONE = {'default' | 'time_zone'}: Optional. Specifies the time zone used to determine the current time and the size of the partitioning key of the date, datetime, and year types. Valid values:

  • default: The default value. Indicates that no time zone is specified. The instance time zone is used. For other types, the time_zone field must be default.
  • time_zone: Indicates a custom time zone offset. For example, +8:00.

• BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}: Optional. Specifies the timestamp precision of the bigint type partitioning key. Valid values:

  • none: The default value. Indicates no precision (the partitioning key is not of the bigint type).
  • us: Microsecond precision.
  • ms: Millisecond precision.
  • s: Second precision.

For more information about creating a dynamic partitioned table, see Create a dynamic partitioned table.

Here is an example:

CREATE TABLE tbl2 (col1 INT, col2 DATETIME)
    DYNAMIC_PARTITION_POLICY(
        ENABLE = true,
        TIME_UNIT = 'hour',
        PRECREATE_TIME = '3 hour',
        EXPIRE_TIME = '1 day',
        TIME_ZONE = '+8:00',
        BIGINT_PRECISION = 'none')
    PARTITION BY RANGE COLUMNS (col2)(
        PARTITION P0 VALUES LESS THAN ('2024-11-11 13:30:00')
        );

Examples

• Create a database table.

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

• Create a table with an index.

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

• Create a table tbl3 with a function index.

  CREATE TABLE tbl3 (c1 INT, c2 INT, INDEX i1 ((c1+1)), UNIQUE KEY ((c1+c2)));
  Query OK, 0 rows affected (0.440 sec)
  
  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 table with 8 hash partitions.

  CREATE TABLE tbl4 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY HASH(c1) PARTITIONS 8;
  Query OK, 0 rows affected (0.024 sec)

• Create a table with a range partition as the primary partition and a key partition as the subpartition.

  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 (0.054 sec)

• Create a table with two columns: one of type gbk and the other of type utf8.

  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 (0.029 sec)

• Enable encoding and use zstd compression. Set the space reserved for macroblocks to 5%.

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

• Create a table tbl8 and set the parallelism to 3.

  CREATE TABLE tbl8(c1 INT PRIMARY KEY, c2 INT) PARALLEL 3;
  Query OK, 0 rows affected (0.051 sec)

• Use an auto-increment column as the partitioning key.

  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 (0.035 sec)

• Specify a foreign key for the table ref_t2. When an UPDATE operation affects the key value in the parent table that matches a row in the child table, execute the SET NULL operation.

  CREATE TABLE ref_t1(c1 INT PRIMARY KEY, C2 INT);
  Query OK, 0 rows affected (0.094 sec)
  
  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 (0.074 sec)

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

  CREATE TABLE tbl10 (col1 INT, col2 INT, col3 INT, CONSTRAINT equal_check1 CHECK(col1 = col3 * 2));
  Query OK, 0 rows affected
  
  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 (0.001 sec)

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

  CREATE TABLE tbl11 LIKE tbl10;
  Query OK, 0 rows affected
  
  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 (0.001 sec)

• Create a columnstore table 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);

• Specify the Skip Index attribute for a column in the table.

  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)
  );

• Create a table tbl1 with an integer column col1. Specify that the operation uses 5 parallel threads and that the data in the new table tbl1 will be sourced from the query results of table tbl2.

  CREATE /*+ parallel(5) */ TABLE tbl1 (col1 INT) AS SELECT col1 FROM tbl2;

• Create a table tb with a persistent bloom filter at the macroblock level.

  CREATE TABLE tb(c1 INT PRIMARY KEY, c2 INT) enable_macro_block_bloom_filter = True;

References

• Create a table
• Modify a table