TDSQL for MySQL allows you to subpartition tables by RANGE or LIST, with subpartitioning syntax similar to that in MySQL.
Subpartitioning by HASH at level 1 and by LIST at level 2 is as follows:
MySQL [test]> CREATE TABLE customers_1 (
first_name VARCHAR(25) key,
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
) shardkey=first_name
PARTITION BY LIST (city) (
PARTITION pRegion_1 VALUES IN('Beijing', 'Tianjin', 'Shanghai'),
PARTITION pRegion_2 VALUES IN('Chongqing', 'Wulumuqi', 'Dalian'),
PARTITION pRegion_3 VALUES IN('Suzhou', 'Hangzhou', 'Xiamen'),
PARTITION pRegion_4 VALUES IN('Shenzhen', 'Guangzhou', 'Chengdu')
);
Subpartitioning by RANGE at level 1 and by LIST at level 2 is as follows:
MySQL [test]> CREATE TABLE tb_sub_r_l (
id int(11) NOT NULL,
order_id bigint NOT NULL,
PRIMARY KEY (id,order_id))
PARTITION BY list(order_id)
(PARTITION p0 VALUES in (2121122),
PARTITION p1 VALUES in (38937383))
TDSQL_DISTRIBUTED BY RANGE(id) (s1 values less than (100),s2 values less than (1000));
Query OK, 0 rows affected, 1 warning (0.35 sec)
year
, month
, and day
functions are supported. If the function is empty, it will be defaulted to the day
function.year
, month
, and day
functions are supported. The value entered is converted to year, month, and day and then compared against the sharded table information.year
, month
, and day
functions are supported.AlarmDo not use the TIMESTAMP type as the subpartitioning key, because it is subject to the time zone and can only specify a time value before the year of 2038. If the subpartitioning key is `char` or `varchar` type, its length is better to be below 255.
Use level-1 subpartitioned tables for your business.
Was this page helpful?