In order to facilitate the deletion of log data, we plan to use time partitioning to create a log table. However, we found that we can only use the timestamp type field instead of the datetime type field when creating the table. Is there any way to use datetime for partitioning, or how to solve the problem that timestamp can only be used until 2038?
Range COLUMNS partitioning is a variant of Range partitioning. You can use one or more columns as partition keys, and the data types of the partition columns can be integer, string (CHAR / VARCHAR), DATE, and DATETIME. Using any expressions is not supported.
Suppose you want to partition by name and easily delete old invalid data, you can create a table as follows:
CREATE TABLE t (
valid_until datetime,
name varchar(255) CHARACTER SET ascii,
notes text
)
PARTITION BY RANGE COLUMNS(name, valid_until)
(PARTITION `p2022-g` VALUES LESS THAN ('G', '2023-01-01 00:00:00'),
PARTITION `p2023-g` VALUES LESS THAN ('G', '2024-01-01 00:00:00'),
PARTITION `p2024-g` VALUES LESS THAN ('G', '2025-01-01 00:00:00'),
PARTITION `p2022-m` VALUES LESS THAN ('M', '2023-01-01 00:00:00'),
PARTITION `p2023-m` VALUES LESS THAN ('M', '2024-01-01 00:00:00'),
PARTITION `p2024-m` VALUES LESS THAN ('M', '2025-01-01 00:00:00'),
PARTITION `p2022-s` VALUES LESS THAN ('S', '2023-01-01 00:00:00'),
PARTITION `p2023-s` VALUES LESS THAN ('S', '2024-01-01 00:00:00'),
PARTITION `p2024-s` VALUES LESS THAN ('S', '2025-01-01 00:00:00'),
PARTITION `p2022-` VALUES LESS THAN (0x7f, '2023-01-01 00:00:00'),
PARTITION `p2023-` VALUES LESS THAN (0x7f, '2024-01-01 00:00:00'),
PARTITION `p2024-` VALUES LESS THAN (0x7f, '2025-01-01 00:00:00'))
This statement partitions by the range of years and names [‘’, ‘G’), [‘G’, ‘M’), [‘M’, ‘S’), [‘S’,), allowing you to delete invalid data while still being able to perform partition pruning on the name and valid_until columns. The [,) notation represents a half-open interval, for example, [‘G’, ‘M’) includes G, values greater than G and less than M, but does not include M.
I don’t quite understand this part. How can names have different sizes? [’’, ‘G’)、[‘G’, ‘M’)、[‘M’, ‘S’)、[‘S’,) What do they actually represent? However, I just want to know how to partition purely by time. I tried and found that it is possible to directly use time for partitioning.
|
username: TiDBer_OX5mflJs
|
username: 
|
username: