DD

This topic describes how to use the DD function.

Description

You can use the DD function to divide the day in each time value of a database shard key by 31 to obtain the subscripts of table shards.

Limits

• A shard key must be of the DATE, DATETIME, or TIMESTAMP type.

• The DD function can be used only for table sharding. This function cannot be used for database sharding.

• When you use the DD function to partition data into table shards, make sure that each database shard contains 31 table shards or less. This is because a month cannot have more than 31 days.

Scenarios

The DD function is suitable for scenarios in which data needs to be partitioned into table shards by day. The name of a table shard indicates a specific day.

Examples

Data needs to be partitioned into database shards by user ID. Then, data in each database shard needs to be partitioned into physical table shards based on the days in the values of the create_time column. This way, each day corresponds to a physical table shard. In this case, you can execute the following DDL statement:

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;