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)
dayfunctions are supported. If the function is empty, it will be defaulted to the
dayfunctions are supported. The value entered is converted to year, month, and day and then compared against the sharded table information.
dayfunctions are supported.
Do 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.