varchar类型的字段如何用作分区键

🪐 TiDB 技术问题
, , ,
1

我有一张表
create table t1 (oid varchar(32),dt varchar(20),otime varchar(32) primary key oid)

otime里面存储的是年月日时分秒格式 类似于：20220830151347

如果我要按照月份进行分区 比如 20220830151347 放到p08 分区

请教一下如何使用 otime 字段作为分区键

2

image
image1249×953 41.7 KB

直接这样建就可以，但是要满足插入的数据格式严格满足要求
image
image1389×877 27.3 KB

否则就会发现在对应分区找不到数据

3

字符串分总能插进去吧

4

CREATE TABLE t1 (
oid VARCHAR(32),
dt VARCHAR(20),
otime VARCHAR(32),
PRIMARY KEY (oid)
)
PARTITION BY RANGE (TO_DAYS(STR_TO_DATE(otime, ‘%Y%m%d%H%i%s’)))
(
PARTITION p01 VALUES LESS THAN (TO_DAYS(‘2022-02-01’)),
PARTITION p02 VALUES LESS THAN (TO_DAYS(‘2022-03-01’)),
PARTITION p03 VALUES LESS THAN (TO_DAYS(‘2022-04-01’)),
PARTITION p04 VALUES LESS THAN (TO_DAYS(‘2022-05-01’)),
PARTITION p05 VALUES LESS THAN (TO_DAYS(‘2022-06-01’)),
PARTITION p06 VALUES LESS THAN (TO_DAYS(‘2022-07-01’)),
PARTITION p07 VALUES LESS THAN (TO_DAYS(‘2022-08-01’)),
PARTITION p08 VALUES LESS THAN (TO_DAYS(‘2022-09-01’)),
PARTITION p09 VALUES LESS THAN (TO_DAYS(‘2022-10-01’)),
PARTITION p10 VALUES LESS THAN (TO_DAYS(‘2022-11-01’)),
PARTITION p11 VALUES LESS THAN (TO_DAYS(‘2022-12-01’)),
PARTITION p12 VALUES LESS THAN (TO_DAYS(‘2023-01-01’))
);

5

你可以尝试一下，我这边测试6.5是没有问题的