This topic describes how to use the DD function.

Limits

  • The partitioning key must be of the DATE, DATETIME, or TIMESTAMP type.
  • This function can be used only for partitioning data into table shards, not into database shards.
  • When you use the DD function to partition data into table shards, ensure that each database shard has no more than 31 table shards. This is because a month cannot have more than 31 days.
  • The version of the PolarDB-X 1.0 instance must be 5.1.28-1320920 or later..

Routing method

To obtain the table shard subscript, divide by the day in the time value of the database shard key.

Scenarios

The DD function is applicable to scenarios where data needs to be partitioned into table shards by day. The name of a table shard indicates a specific day.

Examples

Assume that you want to partition data into database shards by user ID and then create a physical table shard for each day based on the create_time column. You can use the following Data Definition Language (DDL) statement to create tables:

create table test_dd_tb (    
    id int, 
    name varchar(30) DEFAULT NULL,  
    create_time datetime DEFAULT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by HASH(id) 
tbpartition by DD(create_time) tbpartitions 31;