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)
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.AlarmRefrain 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.
Was this page helpful?