1.分区相关
创建分区表
CREATE TABLE `app_message_all_new` (
`user_id` bigint(20) NOT NULL,
`message_type` int(11),
`massive_type` int(11),
`status` int(11),
`deleted` int(11),
`create_time` datetime,
`message_id` bigint(20) NOT NULL,
`month` varchar(10) not null,
`sender_seq_no` varchar(45) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`message` varchar(500) DEFAULT NULL,
`extra` varchar(2048) DEFAULT NULL,
`send_date` datetime
)
UNIQUE KEY(user_id, message_type,massive_type,status,deleted,create_time,message_id,month)
PARTITION BY LIST(`month`)
(
PARTITION `p_202301` VALUES IN ("202301"),
PARTITION `p_202302` VALUES IN ("202302"),
PARTITION `p_202303` VALUES IN ("202303")
)
DISTRIBUTED BY HASH(user_id) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
添加分区
alter table app_message_all_new add partition p_202304 values in ("202304");
删除分区
alter table app_message_all_new drop partition p_202304