1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

create table if not exists table_name

(

    id          bigint auto_increment comment '主键id',

    create_by   varchar(64)                        not null comment '创建者',

    create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',

    update_by   varchar(64)                        null comment '更新者',

    update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间',

    primary key (id, create_time)

)

    comment '测试表名' partition by range (TO_DAYS(create_time)) (

    partition P202401 values less than (TO_DAYS('2024-02-01')) ,

    partition P202402 values less than (TO_DAYS('2024-03-01')) ,

    partition P202403 values less than (TO_DAYS('2024-04-01')) ,

    partition P202404 values less than (TO_DAYS('2024-05-01'))

);

1

2

3

4

-- 开启事件

SET GLOBAL event_scheduler = ON;

-- 查看事件是否开启

SHOW VARIABLES LIKE '%event_sche%';

1

sudo vi /etc/my.cnf

1

event_scheduler = ON

1

sudo systemctl restart mysqld

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

create event event_create_partition on schedule

    every '1' MONTH starts '2024-04-01 00:00:00'

    on completion preserve

    enable

    do

    BEGIN

        -- 当前时间 2024-04-01 00:00:00

        -- _DATE = 2024-05-01

        DECLARE _DATE VARCHAR(10) DEFAULT ADDDATE(SUBDATE(CURDATE(), DAY(CURDATE()) - 1), INTERVAL 1 MONTH);

        -- P202405

        SET @PARTITION_NAME = CONCAT('P', DATE_FORMAT(_DATE, '%Y%m'));

        -- 2024-06-01

        SET @SHEAR_DATE = CONCAT('', ADDDATE(LAST_DAY(_DATE), 1));

        SET @SQL = CONCAT('ALTER TABLE table_name ADD PARTITION (PARTITION ', @PARTITION_NAME, ' VALUES LESS THAN (TO_DAYS("', @SHEAR_DATE, '")))');

        PREPARE STMT FROM @SQL;

        EXECUTE STMT;

        DEALLOCATE PREPARE STMT;

    END;

1

SHOW EVENTS;

1

2

3

4

5

6

7

8

9

10

11

12

13

SELECT

   TABLE_SCHEMA,

   TABLE_NAME,

   PARTITION_NAME,

   PARTITION_METHOD,

   PARTITION_EXPRESSION,

   SUBPARTITION_NAME,

   SUBPARTITION_METHOD,

   SUBPARTITION_EXPRESSION

FROM information_schema.partitions

WHERE TABLE_SCHEMA = '数据库名'

AND TABLE_NAME IN ('table_name')

ORDER BY PARTITION_NAME DESC;