msyql 创建分区表 每天更新分区表 mysql自动创建分区

创建分区表语句
分区的字段一定要是主键

CREATE TABLE my_partitioned_table (
    id INT NOT NULL AUTO_INCREMENT,
    created_at datetime NOT NULL,
    value VARCHAR(255),
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (to_days(created_at)) (
    PARTITION p20230312 VALUES LESS THAN (to_days('2023-03-12')),
    PARTITION p20230313 VALUES LESS THAN (to_days('2023-03-13')),
    PARTITION p20230314 VALUES LESS THAN (to_days('2023-03-14'))
);

-- 根据时间每天都添加一个分区

begin;
    set @pname = concat('p',date_format(date_add(curdate(), interval 1 day),'%Y%m%d'));
--  select @pname;
    select partition_description into @par  from `information_schema`.partitions where table_schema = schema() and table_name = 'my_partitioned_table' order by partition_description desc limit 1;
    set @exeSql = concat('alter table my_partitioned_table add partition (partition ',@pname,' values less than (',@par+1,'))');
    select @exeSql;
    prepare ex from @exeSql;
    execute ex;
    deallocate prepare ex;

    select @exeSql;
end;

-- 查看分区

select * from `information_schema`.partitions where table_schema = schema() and table_name = 'my_partitioned_table' ;

创建事件

create event eventName  on
 schedule every 1 day starts '2023-02-01 00:02:00'
 on completion preserve enable do call func();