You can execute the CREATE TABLE statement to create a table. This topic describes the syntax of the CREATE TABLE statement and the clauses, parameters, and basic methods that can be used in the CREATE TABLE statement.

Syntax

CREATE [PARTITION] TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
    [table_options]
    [table_partition_definition]

create_definition:
    col_name column_definition
  | mysql_create_definition
  | [UNIQUE] GLOBAL INDEX index_name [index_type] (index_sharding_col_name,...)
      [global_secondary_index_option]
      [index_option] ...

index_sharding_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

# Create a global secondary index.
global_secondary_index_option:
    [COVERING (col_name,...)]
    [partition_options]

# Specify a type of tables.
table_partition_definition:
        single
  |    broadcast
  | partition_options


# Specify a type of partitioning.
partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE{({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)}
        | LIST{({column_name | partition_func(column_name)}) 
        | LIST COLUMNS(column_list)} }
    partition_list_spec

# Create partitioning functions.
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

# Partition a table by using the type of partitioning that you specified.
partition_list_spec:
        hash_partition_list
  | range_partition_list
  | list_partition_list

# Partition a table by using hash or key partitioning.
hash_partition_list:
    PARTITIONS partition_count

# Partition a table by using range or range columns partitioning.
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]
    
# Partition a table by using list or list columns partitioning.
list_partition_list:
    list_partition [, list_partition ...]

list_partition:
    PARTITION partition_name VALUES IN (value_list) [partition_spec_options]

partition_spec_options:
          [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [{CHARSET | CHARACTER SET} [=] charset]
        [COLLATE [=] collation]
        [TABLEGROUP [=] table_group_id]
        [LOCALITY [=] locality_option]

locality_option:
    'dn=storage_inst_id_list'
    
storage_inst_id_list:
    storage_inst_id[,storage_inst_id_list]
Note The DDL syntax for PolarDB-X is developed based on the DDL syntax for MySQL. The syntax provided in this section is different from the syntax provided by MySQL to create a table. For more information, see CREATE TABLE statement.

Default automatic partitioning

  • If you create a partitioned table and do not specify the partition key, PolarDB-X automatically applies key partitioning to the table and partitions the table into 64 partitions based on the primary key. If no primary key is specified for the table, PolarDB-X uses the implicit primary key to partition the table.
  • In PolarDB-X, you can also execute the CREATE PARTITION TABLE statement to create a partitioned table that is partitioned based on the primary key. The following sample code shows how to automatically create a partitioned table that is partitioned based on the specified primary key.
    CREATE PARTITION TABLE auto_part_tbl(
     id bigint not null auto_increment, 
     bid int, 
     name varchar(30), 
     primary key(id)
    );

Create a non-partitioned table and a broadcast table

PolarDB-X allows you to create a non-partitioned table by specifying the SINGLE keyword in the CREATE TABLE statement. The following sample code shows how to create a non-partitioned table:
CREATE TABLE single_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) SINGLE;
PolarDB-X allows you to create a broadcast table by specifying the BROADCAST keyword in the CREATE TABLE statement. When you create a broadcast table on a PolarDB-X instance, the broadcast table is replicated on each data node of the PolarDB-X instance. The following sample code shows how to create a broadcast table:
CREATE TABLE broadcast_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) BROADCAST;

Create a partitioned table

PolarDB-X allows you to create a partitioned table by specifying a PARTITION BY clause in the CREATE TABLE statement. This way, you can create a partitioned table based on your business requirements. The following types of partitioning are supported by PolarDB-X:
  • Hash partitioning: This type of partitioning routes data rows to partitions based on the values that are hashed by using the built-in consistent hashing method. The hash values are calculated based on the values of a specified partitioning column or the values that are returned by a specified expression that contains a partitioning function. A variant of hash partitioning is key partitioning. Hash partitioning and key partitioning are different based on the following usage: For hash partitioning, you can use an expression that contains a partitioning function. For key partitioning, you can use multiple partitioning columns as the partition key.
  • Range partitioning: This type of partitioning routes data rows to partitions by evaluating whether the values of a specified partitioning column or the values that are returned by a specified expression that contains a partitioning function are within the given ranges. A variant of range partitioning is range columns partitioning. Range partitioning and range columns partitioning are different based on the following usage: For range partitioning, you can use an expression that contains a partitioning function. For range columns partitioning, you can use multiple partitioning columns as the partition key.
  • List partitioning: This type of partitioning is similar to range partitioning. List partitioning routes data rows to partitions by evaluating whether the values of a specified partitioning column or the values that are returned by a specified expression that contains a partitioning function are within the given lists of discrete values. A variant of list partitioning is list columns partitioning. List partitioning and list columns partitioning are different based on the following usage: For list partitioning, you can use an expression that contains a partitioning function. For list columns partitioning, you can use multiple partitioning columns as the partition key.

Create a partitioned table by using hash partitioning

PolarDB-X allows you to use hash partitioning and key partitioning. Key partitioning is a variant of hash partitioning. Hash partitioning and key partitioning are different based on the following usage: For hash partitioning, you can use an expression that contains a partitioning function. For key partitioning, you can use multiple partitioning columns as the partition key.

  • Hash partitioning

    If you want to use hash partitioning to create a partitioned table, use a column that contains only integer values as the partition key. If you want to use a column that contains date and time values as the partition key, you must use an expression that contains a partitioning function such as YEAR, TO_DAYS, TO_SECONDS, or MONTH to generate integer values based on the date and time values in the column. If a column contains string values, you cannot use the column as a partitioning column.

    The following sample code shows how to create a partitioned table that is partitioned into eight hash partitions based on user IDs:
    CREATE TABLE hash_tbl(
     id bigint not null auto_increment, 
     bid int, 
     name varchar(30),
     birthday datetime not null,
     primary key(id)
    ) 
    partition by  hash(bid) 
    partitions 8;
    The following sample code shows how to create a partitioned table that is partitioned into eight hash partitions based on the birthday column:
    CREATE TABLE hash_tbl_todays(
     id bigint not null auto_increment, 
     bid int, 
     name varchar(30),
     birthday datetime not null,
     primary key(id)
    ) 
    PARTITION BY HASH(TO_DAYS(birthday)) 
    PARTITIONS 8;
    The following partitioning functions are supported by PolarDB-X:
    • YEAR
    • TO_DAYS
    • TO_SECOND
    • MONTH
    • UNIX_TIMESTAMP
  • Key partitioning
    If you use key partitioning to create a partitioned table, you can use multiple columns as the partition key. You cannot use an expression that contains a partitioning function for a partitioning column. This way, key partitioning is different from hash partitioning. Compared with hash partitioning, key partitioning supports more data types for partitioning columns. The columns of the following data types can be used as partitioning columns:
    • Integer: BIGINT, BIGINT UNSINGEDINT, INT UNSINGED, MEDIUMINT, MEDIUMINT UNSINGED, SMALLINT, SMALLINT UNSINGED, TINYINT, and TINYINT UNSINGED
    • Date and time: DATETIME, DATE, and TIMESTAMP
    • String: CHAR and VARCHR

    Key partitioning is used as the default type of partitioning in PolarDB-X.

    The following sample code shows how to create a partitioned table that is partitioned into eight key partitions based on the partition key. The partition key consists of the id and birthday columns.
    CREATE TABLE key_tbl(
     id bigint not null auto_increment, 
     bid int, 
     name varchar(30),
     birthday datetime not null,
     primary key(id)
    ) 
    PARTITION BY KEY(id, birthday) 
    PARTITIONS 8;

Create a partitioned table by using range partitioning

PolarDB-X allows you to use range partitioning and range columns partitioning. Range columns partitioning is a variant of range partitioning. Range partitioning and range columns partitioning are different based on the following usage: For range partitioning, you can use an expression that contains a partitioning function. For range columns partitioning, you can use multiple partitioning columns as the partition key.

  • Range partitioning
    If you want to use range partitioning to create a partitioned table, use a column that contains only integer values as the partition key. If you want to use a column that contains date and time values as the partition key, you must use an expression that contains a partitioning function such as YEAR, TO_DAYS, TO_SECONDS, or MONTH to generate integer values based on the date and time values in the column. If a column contains string values, you cannot use the column as a partitioning column.
    Note You cannot use null values in the VALUES LESS THAN clause.
    The following sample code shows how to create a partitioned table that is partitioned based on the order_id and order_time columns for each quarter:
    CREATE TABLE orders(
     id int, 
     order_time datetime not null) 
    PARTITION BY RANGE(to_days(order_time)) 
    (
      PARTITION p1 VALUES LESS THAN (to_days('2021-01-01')),
      PARTITION p2 VALUES LESS THAN (to_days('2021-04-01')),
      PARTITION p3 VALUES LESS THAN (to_days('2021-07-01')),
      PARTITION p4 VALUES LESS THAN (to_days('2021-10-01')),
      PARTITION p5 VALUES LESS THAN (to_days('2022-01-01'))
    );
  • Range columns partitioning
    If you use range columns partitioning to create a partitioned table, you can use multiple columns as the partition key. You cannot use an expression that contains a partitioning function for a partitioning column. Compared with range partitioning, range columns partitioning supports more data types for partitioning columns. The columns of the following data types can be used as partitioning columns:
    • Integer: BIGINT, BIGINT UNSINGEDINT, INT UNSINGED, MEDIUMINT, MEDIUMINT UNSINGED, SMALLINT, SMALLINT UNSINGED, TINYINT, and TINYINT UNSINGED
    • Date and time: DATETIME and DATE
    • String: CHAR and VARCHR
    Note
    • If you use range columns partitioning to create a partitioned table, you cannot use a column that contains timestamp values as a partitioning column. The TIMESTAMP data type will be supported for partitioning columns in future versions.
    • You cannot use null values in the VALUES LESS THAN clause.
    The following sample code shows how to create a partitioned table that is partitioned based on the order_id and order_time columns by using range partitioning:
    CREATE TABLE orders(
     order_id int, 
     order_time datetime not null) 
    PARTITION BY range columns(order_id,order_time) 
    (
      PARTITION p1 VALUES LESS THAN (10000,'2021-01-01'),
      PARTITION p2 VALUES LESS THAN (20000,'2021-01-01'),
      PARTITION p3 VALUES LESS THAN (30000,'2021-01-01'),
      PARTITION p4 VALUES LESS THAN (40000,'2021-01-01'),
      PARTITION p5 VALUES LESS THAN (50000,'2021-01-01')
    );

Create a partitioned table by using list partitioning

List partitioning is similar to range partitioning. PolarDB-X allows you to use list partitioning and list columns partitioning. List columns partitioning is a variant of list partitioning. List partitioning and list columns partitioning are different based on the following usage: For list partitioning, you can use an expression that contains a partitioning function. For list columns partitioning, you can use multiple partitioning columns as the partition key.

  • List partitioning

    If you want to use list partitioning to create a partitioned table, use a column that contains only integer values as the partition key. If you want to use a column that contains date and time values as the partition key, you must use an expression that contains a partitioning function such as YEAR, TO_DAYS, TO_SECONDS, or MONTH to generate integer values based on the date and time values in the column. If a column contains string values, you cannot use the column as a partitioning column.

    The following sample code shows how to create a partitioned table that is partitioned based on the order_time column for each quarter:
    CREATE TABLE orders(
     id int, 
     order_region varchar(64),
     order_time datetime not null) 
    PARTITION BY LIST(YEAR(order_time)) 
    (
      PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
      PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
      PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019)
    );
  • List columns partitioning
    If you use list columns partitioning to create a partitioned table, you can use multiple columns as the partition key. You cannot use an expression that contains a partitioning function for a partitioning column. Compared with list partitioning, list columns partitioning supports more data types for partitioning columns. The columns of the following data types can be used as partitioning columns:
    • Integer: BIGINT, BIGINT UNSINGEDINT, INT UNSINGED, MEDIUMINT, MEDIUMINT UNSINGED, SMALLINT, SMALLINT UNSINGED, TINYINT, and TINYINT UNSINGED
    • Date and time: DATETIME and DATE
    • String: CHAR and VARCHR
    Note
    • If you use list columns partitioning to create a partitioned table, you cannot use a column that contains timestamp values as a partitioning column. The TIMESTAMP data type will be supported for partitioning columns in future versions.
    • You cannot use null values in the VALUES LESS THAN clause.
    The following sample code shows how to create a partitioned table that is partitioned based on the order_region column by using list columns partitioning:
    CREATE TABLE orders(
     id int, 
     order_region varchar(64),
     order_time datetime not null) 
    PARTITION BY LIST COLUMNS(order_region) 
    (
      PARTITION p1 VALUES IN ('Hangzhou', 'Shenzhen'),
      PARTITION p2 VALUES IN ('Beijing', 'Shanghai'),
      PARTITION p3 VALUES IN ('Qingdao')
    );

Data types in partitioned tables

The following table describes the data types that can be used for different partitioning types.

Data type Hash partitioning Key partitioning Range partitioning Range columns partitioning List partitioning List columns partitioning
Numeric TINYINT Supported Supported Supported Supported Supported Supported
TINYINT UNSIGNED Supported Supported Supported Supported Supported Supported
SMALLINT Supported Supported Supported Supported Supported Supported
SMALLINT UNSIGNED Supported Supported Supported Supported Supported Supported
MEDIUMINT Supported Supported Supported Supported Supported Supported
MEDIUMINT UNSIGNED Supported Supported Supported Supported Supported Supported
INT Supported Supported Supported Supported Supported Supported
INT UNSIGNED Supported Supported Supported Supported Supported Supported
BIGINT Supported Supported Supported Supported Supported Supported
BIGINT UNSIGNED Supported Supported Supported Supported Supported Supported
Date and time DATE If you want to use a column of this data type as a partitioning column, you must use the YEAR, MONTH, TO_DAYS, or TO_SECONDS function as a partitioning function to calculate the values of the column. Supported If you want to use a column of this data type as a partitioning column, you must use the YEAR, MONTH, TO_DAYS, or TO_SECONDS function as a partitioning function to calculate the values of the column. Supported If you want to use a column of this data type as a partitioning column, you must use the YEAR, MONTH, TO_DAYS, or TO_SECONDS function as a partitioning function to calculate the values of the column. Supported
DATETIME If you want to use a column of this data type as a partitioning column, you must use the YEAR, MONTH, TO_DAYS, or TO_SECONDS function as a partitioning function to calculate the values of the column. Supported If you want to use a column of this data type as a partitioning column, you must use the YEAR, MONTH, TO_DAYS, or TO_SECONDS function as a partitioning function to calculate the values of the column. Supported If you want to use a column of this data type as a partitioning column, you must use the YEAR, MONTH, TO_DAYS, or TO_SECONDS function as a partitioning function to calculate the values of the column. Supported
TIMESTAMP If you want to use a column of this data type as a partitioning column, you must use the UNIX_TIMESTAMP function as a partitioning function to calculate the values of the column. Supported Not supported Not supported Not supported Not supported
String CHAR Not supported Supported Not supported Supported Not supported Supported
VARCHAR Not supported Supported Not supported Supported Not supported Supported

Parameters

Parameter Description
CHARSET | CHARACTER SET Specifies the default character set that is used for columns in a table. You can use one of the following character sets:
  • utf8
  • utf8mb4
  • gbk
COLLATE Specifies the default collation for columns in a table. You can use one of the following collations:
  • utf8_bin
  • utf8_general_ci
  • utf8_unicode_ci
  • gbk_bin
  • gbk_chinese_ci
  • utf8mb4_general_ci
  • utf8mb4__general_cs
  • utf8mb4_bin
  • utf8mb4_unicode_ci
TABLEGROUP Specifies the table group to which the partitioned table belongs. If this parameter is not specified, PolarDB-X automatically assigns the partitioned table to a table group or creates a table group. In the table group, all tables are partitioned based on the same partitioning method.
LOCALITY Specifies the data node on which the partitioned table is deployed.