CREATE TABLE (AUTO mode)

Partition types

Hash type

In PolarDB-X, there are two types of hash-based partitioning: hash partitioning and key partitioning. Both are part of the standard partitioning syntax in native MySQL. To enable flexible partition management features—such as partition split, merge, and migration—and to support hot-spot splitting for vector partition keys, PolarDB-X redefines the routing for hash and key partitioning; while it remains syntactically compatible with the MySQL CREATE TABLE syntax, its underlying partition routing implementation is different from that of MySQL. The following table describes the differences between key partitioning and hash partitioning:

Example 1-1: Key partition

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;

## This query triggers partition pruning and scans only a single partition.
SELECT id from key_tbl where name='Jack';

If the first partition column, name, causes data imbalance or a data hotspot, you can perform a partition split using the next partition column, such as id. For details, see Modify a table group level partition (AUTO mode).

If a vector partition key with N partition columns uses its first K columns for routing (1 ≤ K ≤ N), including conditions on the prefix partition columns (the first K columns) in the WHERE clause enables partition pruning.

Example 1-2: Hash partitioning

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;

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;

Currently, PolarDB-X supports the following partitioning functions:

• YEAR

• MONTH

• DAYOFMONTH

• DAYOFWEEK

• DAYOFYEAR

• TO_DAYS

• TO_MONTHS

• TO_WEEKS

• TO_SECONDS

• UNIX_TIMESTAMP

• SUBSTR/SUBSTRING

For SUBSTR/SUBSTRING functions, the partition key must be a string type; for all other partition functions, it must be a time type (such as DATE, DATETIME, or TIMESTAMP).

Example 1-3: Hash partition expansion

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;

Unlike key partitioning, hash partitioning uses a vector partition key, where all partition columns contribute to both the hash and routing calculations during partition routing. Therefore, to enable partition pruning, the WHERE clause of an SQL query must specify equality conditions for all partition columns. For example, SQL1 can trigger partition pruning on the hash_tbl2 table, but SQL2 cannot:

##SQL1 (This query triggers partition pruning, scanning only a single partition)
SELECT id from hash_tbl2 where name='Jack' and birthday='1990-11-11';

##SQL2 (This query does not trigger partition pruning, resulting in a full partition scan)
SELECT id from hash_tbl2 where name='Jack';

Because hash partitioning uses the entire partition key to calculate the hash value up front, it theoretically distributes data more evenly than key partitioning using a vector partitioning key. However, it cannot support hotspot splitting, as there are no subsequent columns available to split a hot partition.

Limitations

• Data type limitations

  • Integer types: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, and TINYINT UNSIGNED.

  • Time types: DATETIME, DATE, and TIMESTAMP.

  • String types: CHAR and VARCHAR.

• Syntax limitations

  • When using a partition function with a single-column partition key for a hash partition, the key must be a time type.

  • A vector partition key for a hash partition does not support partition functions or hotspot splitting.

  • By default, the maximum number of partitions is 8,192.

  • By default, the maximum number of partition columns is 5.

Data Uniformity

• The built-in consistent hash algorithm for key partitioning and hash partitioning is MurmurHash3, a high-performance algorithm with a low collision probability.

• With MurmurHash3, data distribution for key partitioning and hash partitioning typically becomes balanced only when the number of distinct partition key values (N) exceeds 3000. The larger the value of N, the more balanced the data distribution.

Range type

In PolarDB-X, there are two types of range partitioning: range partition and range columns partition. Both are part of the standard partitioning syntax of native MySQL. The following table compares these two partition types.

Example 2-1: Range columns partitioning

Range columns partitioning supports a vector partition key but not a partition function. For example, to partition a table by order ID and order date, you can use the following CREATE TABLE syntax:

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 2-2: range partitioning

Range partitioning supports only a single-column partition key. However, for a datetime partition column, you can use partitioning functions such as YEAR, TO_DAYS, TO_SECONDS, or MONTH to convert its values to integers.

For example, to create quarterly partitions on the order_time column using range partitioning, use the following syntax:

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

Limits

• Data type restrictions

  • Integer types: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, and TINYINT UNSIGNED.

  • Datetime types: DATETIME and DATE.

  • String types: CHAR and VARCHAR.

• Syntax restrictions

  • RANGE COLUMNS partitioning and RANGE partitioning do not support using a NULL value as a boundary value.

  • RANGE COLUMNS partitioning does not support the TIMESTAMP data type.

  • RANGE partitioning supports only integer types. If the partitioning key uses the TIMESTAMP type, you must use the UNIX_TIMESTAMP function to ensure time zone consistency.

  • RANGE partitioning does not support hot spot splitting.

  • During queries, NULL values are treated as the minimum value for partition routing.

  • By default, the maximum number of partitions is 8,192.

  • By default, the maximum number of partitioning columns is 5.

List type

Similar to the range type, PolarDB-X further subdivides the list partitioning policy into two types: list partitioning and list columns partitioning. Both types use the standard partitioning syntax of native MySQL. Additionally, PolarDB-X supports a default partition for both list partitioning and list columns partitioning. This table compares these two types:

Example 3-1: List columns partitioning

list columns partitioning supports vector partition keys. For example, list columns partitioning lets you partition orders by country and city. The table creation syntax is as follows:

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 3-2: list partitioning

Although list partitioning supports only a single-column partition key, for a time-type partition column, you can use partition function expressions such as YEAR, MONTH, DAYOFMONTH, TO_DAYS, and TO_SECONDS to convert its values to an integer type.

For example, to create a list partition based on the year of the order_time column, use the following create table syntax:

CREATE TABLE orders_years(
 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 3-3: List columns and list partitions with a default partition

PolarDB-X supports creating list columns partitions and list partitions that include a default partition, where data not defined in the regular partitions is routed.

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')),
  PARTITION pd VALUES IN (DEFAULT)
);

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

Limits

• Data type restrictions

  • Integer types: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, and TINYINT UNSIGNED.

  • Time types: DATETIME and DATE.

  • String types: CHAR and VARCHAR.

• Syntax restrictions

  • List Columns partitioning does not support the TIMESTAMP data type.

  • List partitioning supports only integer types.

  • Neither List Columns partitioning nor List partitioning supports hotspot splitting.

  • By default, the maximum number of partitions is 8,192.

  • By default, the maximum number of partition columns is 5.

CoHash type

The CoHash partitioning policy of PolarDB-X is a partitioning policy unique to PolarDB-X.

Version requirements

Use version 5.4.18-17047709 or later.

Use cases

This partitioning strategy is commonly used for the following business scenarios:

If a table has correlated columns, such as c1 and c2 whose last four characters are always identical, you can partition it horizontally on both columns to route SQL queries with an equality condition on either column to a single partition.

Therefore, this partitioning strategy requires that values remain consistent across multiple partition columns in the same partitioned table.

Example 4-1: Define co-location with independent partition functions

Suppose your application has an orders table where the order_id and buyer_id columns share the same last six digits in each row. To partition this table by these shared digits and ensure that an equality query on either column is routed to the same partition, use the following syntax:

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) /* The last 6 characters of order_id */,
 	RIGHT(`buyer_id`,6) /* The last 6 characters of buyer_id */
) 
PARTITIONS 8;

Example 4-2: Using Range_Hash syntactic sugar for user migration from 1.0 to 2.0

For a migration from 1.0 to 2.0, consider an orders table sharded by database and table using range_hash, defined as follows: DBPARTIITION BY RANGE_HASH(`order_id`,`buyer_id`, 6) . Its corresponding partitioned table in the 2.0 AUTO database is defined as follows:

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;

PolarDB-X automatically converts RANGE_HASH syntax to a CO_HASH partition definition. For example, RANGE_HASH(order_id, buyer_Id, 6) becomes the following CO_HASH definition:

CREATE TABLE orders(
 id bigint not null auto_increment, 
 buyer_id bigint,
 order_id bigint,
 ...
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT(`order_id`,6) /* Uses the last 6 characters of order_id */,
 	RIGHT(`buyer_id`,6) /* Uses the last 6 characters of buyer_id */
) 
PARTITIONS 8;

Key differences from hash/key partitioning

Since the CoHash and hash/key partitioning strategies are similar, this section outlines their key similarities and differences in usage.

Considerations

• Your service must maintain the coordinated relationship between partition column values; PolarDB-X only validates routing results.When you use CO_HASH partitioning, PolarDB-X verifies that all specified partition column values for a row route to the same shard. However, it does not validate whether the values themselves adhere to the coordinated relationship. This enforcement is the responsibility of your service.

  For example, assume your service requires the last four characters of columns c1 and c2 to be identical. If a value for c1, such as 100234, and a value for c2, such as 1320, both route to shard 0, PolarDB-X permits the insert (c1,c2) values (100234,1320) operation because the routing is consistent. However, this operation violates the service rule, as the last four characters of c1 (0234) and c2 (1320) are not the same.

• DML restrictions on modifying partition columns Because CO_HASH partitioning correlates the values of multiple partition columns, PolarDB-X enforces the following restrictions on DML operations that modify these columns to prevent incorrect data distribution:

  • For insert and replace statements, if the values for different partition columns in a single row of the VALUES clause resolve to different partitions, PolarDB-X rejects the operation.

  • For update and upsert statements, you must modify all partition columns simultaneously in the SET clause. For example, if c1 and c2 are partition columns, you must use a statement like UPDATE t1 SET c1='xx',c2='yy' WHERE id=1. If the new values for different partition columns in a single row resolve to different partitions, PolarDB-X rejects the statement.

  • When CO_HASH is the partitioning strategy for a global secondary index (GSI), PolarDB-X also rejects any DML operation on the primary table that causes the values for different partition columns in a single GSI row to resolve to different partitions.

• Handling leading zeros for integer types. Because partition columns in a CO_HASH strategy are correlated, you often define them using a partition function such as SUBSTR, LEFT, or RIGHT. This process can result in leading zeros when integers are truncated. For example, suppose your business logic requires the last four characters of c1 and c2 to be identical. If c1 is 1000034, the last four characters are the string '0034'. For a partition column of an integer type, CO_HASH automatically casts any truncated value to that column's integer type before routing. As a result, CO_HASH casts the string '0034' to the integer 34, computes the hash value, and routes the row. This process automatically handles leading zeros.

Partition function limitations

• RIGHT

• LEFT

• SUBSTR

Data type restrictions

• integer types: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, and TINYINT UNSIGNED

• fixed-point type: DECIMAL (with a scale of 0)

• time types: Not supported

• string types: CHAR and VARCHAR

Syntax restrictions

• You cannot use nested partition functions, such as SUBSTR(SUBSTR(c1,-6),-4), on a partition column.

• When using the RANGE_HASH syntax sugar, the length parameter must not be negative.

• All partition columns must have the exact same data type, including the following properties:

  • Character set and collation

  • Length and precision

• By default, the maximum number of partitions is 8,192.

• By default, the maximum number of partition columns is 5.