KEY - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to create a key partitioned table.

Syntax

The following statement is used to create one or more key partitioned tables.

CREATE TABLE [ schema. ]table_name
 table_definition
   PARTITION BY [LINEAR] KEY(column_list) [PARTITIONS number]
   (partition_definition [, partition_definition] ...);

partition_definition is:

 PARTITION partition_name

Parameters

Parameter	Description
column_list	The list of partition key columns. You can specify 0 or more partition key columns. The following data types are supported: INT, string types, DATE, TIME, and DATETIME.
partition_name	The name of the partition. The name must be unique within the table.
number	The number of key partitions.

Description

Key partitions are similar to hash partitioning and also use the same hash algorithm as in MySQL.

The differences between key partitions and hash partitions:

No partition key column may be specified in key partitions. In this case, the primary key column is used as the partition key column by default. If no partition key column is specified, the partition is performed based on the unique key.
Key partitions support multiple partition key columns.
KEY partitions support the following data types: INT, string types, DATE, TIME, and DATETIME.
Key partitions support the extended data type of LINEAR KEY.

Examples

Create a key partitioned table:

CREATE TABLE sales_key
(
  s_id        varchar(20),
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  PRIMARY KEY(s_id)
)PARTITION by key (s_id)
PARTITIONS 11;

Create a linear key partitioned table:

CREATE TABLE sales_linear_key
(
  s_id        varchar(20),
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  PRIMARY KEY(s_id)
)PARTITION by linear key (s_id)
PARTITIONS 11;