MySQL创建分区表 按天自动分区实现方法

流程图

pie
    title 分区表创建流程
    "创建分区表" : 50
    "添加分区" : 30
    "定时自动分区" : 20

步骤

步骤 操作
1 创建分区表
2 添加分区
3 定时自动分区

具体操作步骤

步骤1：创建分区表

CREATE TABLE `my_table` (
    `id` INT NOT NULL,
    `name` VARCHAR(50),
    `created_at` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2022-01-01')),
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2022-02-01')),
    PARTITION p2 VALUES LESS THAN (TO_DAYS('2022-03-01')),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
  • 代码解释：
    • 创建一个名为my_table的表，包括id、name和created_at字段
    • 使用RANGE分区方式，按照created_at字段的天数进行分区
    • 创建4个分区，分别是p0~p3

步骤2：添加分区

ALTER TABLE my_table ADD PARTITION (
    PARTITION p4 VALUES LESS THAN (TO_DAYS('2022-04-01'))
);
  • 代码解释：
    • 添加一个新的分区p4，值小于'2022-04-01'的created_at字段

步骤3：定时自动分区

CREATE EVENT auto_partition
ON SCHEDULE EVERY 1 DAY
DO
    ALTER TABLE my_table REORGANIZE PARTITION MAX_PARTITIONS 1;
  • 代码解释：
    • 创建一个定时事件auto_partition，每天执行一次
    • 重新组织表分区，删除最旧的分区，保持分区数不变

总结

通过以上步骤，你可以实现MySQL创建分区表按天自动分区的功能。记得定时执行定时事件，保持表的分区数量适当，避免数据量过大影响查询性能。祝你顺利完成任务！