PolarDB:CREATE TABLE (AUTO mode)

Example: KEY partitioning (recommended for most use cases)

Create a table partitioned by name and id. By default, routing uses only the name column, leaving id available for hot partition splitting later:

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(name, id)
PARTITIONS 8;

To trigger partition pruning, include the first routing column in the WHERE clause:

-- Scans only one partition
SELECT id FROM key_tbl WHERE name = 'Jack';

If the name column develops a hot spot, use ALTER TABLEGROUP to split on the id column. For details, see ALTER TABLEGROUP.

Example: HASH partitioning (single-column)

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(id)
PARTITIONS 8;

To partition by a datetime column, use a partition function to convert it to an integer:

CREATE TABLE hash_tbl_days(
  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;

Example: HASH partitioning (vector key)

PolarDB-X extends HASH partitioning to support vector partition keys (not available in MySQL). Unlike KEY partitioning, routing uses all partition key columns simultaneously, so partition pruning requires conditions on all of them:

CREATE TABLE hash_tbl2(
  id       bigint not null auto_increment,
  bid      int,
  name     varchar(30),
  birthday datetime not null,
  primary key(id)
)
PARTITION BY HASH(name, birthday)
PARTITIONS 8;

-- Partition pruning: scans one partition (all key columns specified)
SELECT id FROM hash_tbl2 WHERE name = 'Jack' AND birthday = '1990-11-11';

-- No pruning: scans all partitions (incomplete key)
SELECT id FROM hash_tbl2 WHERE name = 'Jack';

When to use

• KEY partitioning — default choice for even data distribution with the option to split hot partitions later.

• HASH with a partition function — when you want to distribute rows by a time component (e.g., year, month) rather than the raw timestamp.

• HASH with a vector key — when rows must route by the combined value of multiple columns and you do not need to split hot partitions.

Limitations

Data types

Integer: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, TINYINT UNSIGNED

Date/time: DATE, DATETIME, TIMESTAMP

String: CHAR, VARCHAR

Syntax constraints

• Single-column keys with a DATE, DATETIME, or TIMESTAMP column may use partition functions.

• Vector partition keys do not support partition functions or hot partition splitting.

• A table can have at most 8,192 partitions by default.

• A partition key can have at most 5 columns.

Example: RANGE COLUMNS partitioning

Partition an orders table by order_id and order_time ranges:

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'),
  PARTITION p6 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

Example: RANGE partitioning

Partition by quarter using TO_DAYS() to convert order_time to an integer:

CREATE TABLE orders_quarterly(
  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')),
  PARTITION p6 VALUES LESS THAN (MAXVALUE)
);

When to use

• RANGE COLUMNS — date or ID range partitioning with multi-column keys or hot partition splitting requirements.

• RANGE — single-column date-based partitioning where you want to use a time extraction function.

Limitations

Data types

Integer: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, TINYINT UNSIGNED

Date/time: DATE, DATETIME (TIMESTAMP not supported in RANGE COLUMNS; use UNIX_TIMESTAMP() in RANGE)

String: CHAR, VARCHAR (RANGE COLUMNS only; not supported in RANGE)

Syntax constraints

• NULL cannot be used as a range boundary.

• If a query specifies NULL for a RANGE partition key, PolarDB-X treats the NULL as the minimum value during routing.

• A table can have at most 8,192 partitions by default.

• A partition key can have at most 5 columns.

Example: LIST COLUMNS partitioning

Partition a regional orders table by country and city:

CREATE TABLE orders_region(
  id         int,
  country    varchar(64),
  city       varchar(64),
  order_time datetime not null
)
PARTITION BY LIST COLUMNS(country, city)
(
  PARTITION p1 VALUES IN (('China', 'Hangzhou'), ('China', 'Beijing')),
  PARTITION p2 VALUES IN (('United States', 'New York'), ('United States', 'Chicago')),
  PARTITION p3 VALUES IN (('Russia', 'Moscow'))
);

Example: LIST partitioning

Partition by year using YEAR() to extract an integer from order_time:

CREATE TABLE orders_by_year(
  id         int,
  country    varchar(64),
  city       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)
);

Example: DEFAULT partition (LIST COLUMNS)

Add a DEFAULT partition to catch rows that do not match any other partition. Only one DEFAULT partition is allowed, and it must be defined last:

CREATE TABLE orders_region_default(
  id         int,
  country    varchar(64),
  city       varchar(64),
  order_time datetime not null
)
PARTITION BY LIST COLUMNS(country, city)
(
  PARTITION p1 VALUES IN (('China', 'Hangzhou'), ('China', 'Beijing')),
  PARTITION p2 VALUES IN (('United States', 'New York'), ('United States', 'Chicago')),
  PARTITION p3 VALUES IN (('Russia', 'Moscow')),
  PARTITION pd VALUES IN (DEFAULT)
);

Example: DEFAULT partition (LIST)

LIST partitioning also supports a DEFAULT partition to route unmatched integer values:

CREATE TABLE orders_by_year_default(
  id         int,
  country    varchar(64),
  city       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),
  PARTITION pd VALUES IN (DEFAULT)
);

When to use

• LIST COLUMNS — categorical partitioning on string columns (e.g., country, region) or on multiple columns.

• LIST — categorical partitioning on a single integer or time-derived integer column.

• Add a DEFAULT partition when new category values may appear after table creation.

Limitations

Data types

Integer: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, TINYINT UNSIGNED

Date/time: DATE, DATETIME

String: CHAR, VARCHAR (LIST COLUMNS only)

Syntax constraints

• LIST COLUMNS does not support TIMESTAMP columns.

• LIST partitioning requires integer partition key columns.

• Neither type supports hot partition splitting.

• A table can have at most 8,192 partitions by default.

• A partition key can have at most 5 columns.

How it works

COHASH applies a partition function (such as RIGHT, LEFT, or SUBSTR) to each partition key column to extract the shared portion, then uses consistent hashing on the result. Because the shared portion is identical across columns, rows land in the same partition regardless of which column you query.

Example: COHASH partitioning

Partition an orders table by the last six characters of both order_id and buyer_id:

CREATE TABLE t_orders(
  id         bigint not null auto_increment,
  order_id   bigint,
  buyer_id   bigint,
  order_time datetime not null,
  primary key(id)
)
PARTITION BY CO_HASH(
  RIGHT(order_id, 6),  -- last six characters of order_id
  RIGHT(buyer_id, 6)   -- last six characters of buyer_id
)
PARTITIONS 8;

When order_id and buyer_id share the same last six characters, both of the following queries route to the same partition:

SELECT * FROM t_orders WHERE order_id = 100234;
SELECT * FROM t_orders WHERE buyer_id = 100234;

Migrating from PolarDB-X 1.0 RANGE_HASH

If the source instance uses RANGE_HASH(order_id, buyer_id, 6), use the same syntax in the destination database. PolarDB-X automatically converts it to the equivalent CO_HASH definition:

CREATE TABLE orders(
  id       bigint not null auto_increment,
  buyer_id bigint,
  order_id bigint,
  primary key(id)
)
PARTITION BY RANGE_HASH(order_id, buyer_Id, 6)
PARTITIONS 8;

COHASH vs KEY vs HASH

Usage notes

• You are responsible for maintaining column similarity. PolarDB-X checks routing results but does not enforce that the designated similar portion is identical across columns. If an inserted row has differing values in the similar portion, it may be silently routed to an unexpected partition.

• DML constraints. To prevent inconsistent routing:

  • An INSERT or REPLACE where the partition key columns in a row route to different partitions is rejected.

  • An UPDATE or UPSERT that modifies partition key columns must update all of them. If the new values route to different partitions, the statement is rejected.

  • The same restrictions apply to DML on the primary table when the table has a COHASH global secondary index (GSI).

• Leading zeros in integer columns. When a partition function like RIGHT(c1, 4) is applied to an integer column, the result is automatically converted to its numeric value (e.g., '0034' becomes 34). This ensures that c1 = 1000034 and c2 = 34 route to the same partition as expected.

When to use

Use COHASH when a table has two or more columns whose values are always correlated by a shared suffix or prefix, and you need equivalent point queries on any of those columns to hit a single partition.

Limitations

Partition functions: RIGHT, LEFT, SUBSTR only. Nested functions are not supported.

Data types: BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT (and UNSIGNED variants); DECIMAL (decimal part must be zero); CHAR, VARCHAR. Date/time types are not supported.

Syntax constraints

• All partition key columns must share the same character set, collation, and length/precision definition.

• When using RANGE_HASH syntax, the length value must be positive.

• A table can have at most 8,192 partitions by default.

• A partition key can have at most 5 columns.

Example: Templated subpartitions

Three LIST COLUMNS level-1 partitions, each with four KEY subpartitions — 12 physical partitions total:

CREATE TABLE sp_tbl_list_key_tp(
  id         int,
  country    varchar(64),
  city       varchar(64),
  order_time datetime not null,
  PRIMARY KEY(id)
)
PARTITION BY LIST COLUMNS(country, city)
SUBPARTITION BY KEY(id) SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (('China', 'Hangzhou')),
  PARTITION p2 VALUES IN (('Russia', 'Moscow')),
  PARTITION pd VALUES IN (DEFAULT)
);

Example: Non-templated subpartitions

Three LIST COLUMNS level-1 partitions with 2, 3, and 4 subpartitions respectively — 9 physical partitions total:

CREATE TABLE sp_tbl_list_key_ntp(
  id         int,
  country    varchar(64),
  city       varchar(64),
  order_time datetime not null,
  PRIMARY KEY(id)
)
PARTITION BY LIST COLUMNS(country, city)
SUBPARTITION BY KEY(id)
(
  PARTITION p1 VALUES IN (('China', 'Hangzhou')) SUBPARTITIONS 2,
  PARTITION p2 VALUES IN (('Russia', 'Moscow'))  SUBPARTITIONS 3,
  PARTITION pd VALUES IN (DEFAULT)               SUBPARTITIONS 4
);

When to use

Use level-2 partitions when a single-dimensional partition strategy leads to data skew or insufficient granularity — for example, partitioning orders by region (LIST) and then by ID within each region (KEY).

Limitations

• The total number of physical partitions (the sum of all level-2 partitions across all level-1 partitions) cannot exceed 8,192 by default.

• Subpartition names must be unique and cannot duplicate any level-1 partition name.

• Keep the product of level-1 and level-2 counts small to avoid excessive partition overhead.