CREATE TABLE

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]

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

CREATE TABLE single_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) SINGLE;

CREATE TABLE broadcast_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) BROADCAST;

Create a partitioned table

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.

Parameters