TDSQL for MySQL currently supports two-level sharding in range and list formats, where the specific table creation syntax is similar to the sharding syntax in MySQL.
The syntax for creating a sharded table with a hash at level 1 and a 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') );
The syntax for creating a sharded table with a range at level 1 and a 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.
Refrain from using the TIMESTAMP type as the shardkey, because it is subject to the time zone and can only specify a time value before the year of 2038. If the shardkey is `char` or `varchar` type, its length is better to be below 255.
Use one-level sharded tables for businesses as much as possible.