當(dāng)前版本：mysql8.0

一共分為三步

 1.建庫、建表
 2.新建自動(dòng)創(chuàng)建分區(qū)的存儲(chǔ)過程和新建定時(shí)調(diào)用分區(qū)存儲(chǔ)過程的事件
 3.測試：新建自動(dòng)插入數(shù)據(jù)的存儲(chǔ)過程和事件

需要關(guān)注的.

1.一定要在建表的之后創(chuàng)建分區(qū)，如果不這個(gè)時(shí)候創(chuàng)建，后期存儲(chǔ)過程中創(chuàng)建會(huì)失敗，mysql會(huì)有提示！
2.DATE_FORMAT(TARGET_DATE, 'p%Y%m%d%h%m')得到的數(shù)字不是年月日小時(shí)分，而是年月日小時(shí)月，應(yīng)該使用 DATE_FORMAT(TARGET_DATE, 'p%Y%m%d%h%m%i')
3.執(zhí)行順序要按三步順序執(zhí)行

一、建庫、建表

create database if not exists test;
use test;

drop table if exists t_auto_insert_day;
create table if not exists test.t_auto_insert_day
(
    id        int auto_increment not null,
    column_2  varchar(20)        null,
    dc_time   datetime           null,
    data_time bigint             not null comment '數(shù)據(jù)時(shí)間,數(shù)據(jù)分區(qū)字段yyyymmdd',
    primary key (id, data_time)
) partition by range ( data_time ) (
    partition p20190912 values less than (20190912)
    );

drop table if exists t_auto_insert_hour;
create table if not exists test.t_auto_insert_hour
(
    id        int auto_increment not null,
    column_2  varchar(20)        null,
    column_3  varchar(20)        null,
    dc_time   datetime           null,
    data_time bigint             not null comment '數(shù)據(jù)時(shí)間,數(shù)據(jù)分區(qū)字段yyyymmddHH',
    primary key (id, data_time)
) partition by range ( data_time ) (
    partition p2019091215 values less than (2019091215)
    );

drop table if exists t_auto_insert_minute;
create table if not exists test.t_auto_insert_minute
(
    id        int auto_increment not null,
    column_2  varchar(20)        null,
    dc_time   datetime           null,
    data_time bigint             not null comment '數(shù)據(jù)時(shí)間,數(shù)據(jù)分區(qū)字段yyyymmddHHmm',
    primary key (id, data_time)
) partition by range ( data_time ) (
    partition p201909121525 values less than (201909121525)
    );

新建分區(qū)存儲(chǔ)過程和對應(yīng)的事件

 drop procedure if exists auto_create_partition_by_day;
#每天創(chuàng)建區(qū)分存儲(chǔ)過程
create procedure auto_create_partition_by_day(IN IN_SCHEMANAME varchar(64), IN IN_TABLENAME varchar(64))
BEGIN
    #當(dāng)前日期存在的分區(qū)的個(gè)數(shù)
    DECLARE ROWS_CNT INT UNSIGNED;
    #目前日期，為當(dāng)前日期的后一天
    DECLARE TARGET_DATE TIMESTAMP;
    #分區(qū)的名稱，格式為p20180620
    DECLARE PARTITIONNAME VARCHAR(9);
    #當(dāng)前分區(qū)名稱的分區(qū)值上限，即為 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_DAY VARCHAR(9);
    SET TARGET_DATE = NOW() + INTERVAL 1 DAY;
    #格式化時(shí)間得到新增分區(qū)名稱
    SET PARTITIONNAME = DATE_FORMAT(TARGET_DATE, 'p%Y%m%d');
    SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY;
    #格式化時(shí)間得到新增分區(qū)范圍
    SET PARTITION_ADD_DAY = DATE_FORMAT(TARGET_DATE, '%Y%m%d');
    SELECT COUNT(*)
    INTO ROWS_CNT
    FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME
      AND table_name = IN_TABLENAME
      AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                          ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (',
                          PARTITION_ADD_DAY, ') ENGINE = InnoDB);');
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        deallocate PREPARE STMT;
    ELSE
        SELECT CONCAT('partition `', PARTITIONNAME, '` for table `', IN_SCHEMANAME, '.', IN_TABLENAME, '` already exists') AS result;
    END IF;
END;

drop event if exists event_every_day_call_create_partition;
#每天晚上十二點(diǎn)，執(zhí)行事件，調(diào)用每天分區(qū)存儲(chǔ)過程
create event event_every_day_call_create_partition on schedule
    every '1' day
        #從現(xiàn)在開始，也可以從制定時(shí)間開始2019-09-11 23:59:59
        starts now()
    on completion preserve
    enable
    do
    #調(diào)用分區(qū)存儲(chǔ)過程
    call auto_create_partition_by_day('test', 't_auto_insert_day');



drop procedure if exists auto_create_partition_by_hour;
#每小時(shí)創(chuàng)建區(qū)分存儲(chǔ)過程
create procedure auto_create_partition_by_hour(IN IN_SCHEMANAME varchar(64), IN IN_TABLENAME varchar(64))
BEGIN
    #當(dāng)前日期存在的分區(qū)的個(gè)數(shù)
    DECLARE ROWS_CNT INT UNSIGNED;
    #目前日期，為當(dāng)前日期的后一天
    DECLARE TARGET_DATE TIMESTAMP;
    #分區(qū)的名稱，格式為p2019091214
    DECLARE PARTITIONNAME VARCHAR(11);
    #當(dāng)前分區(qū)名稱的分區(qū)值上限，即為 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_HOUR VARCHAR(11);
    SET TARGET_DATE = NOW() + INTERVAL 1 hour;
    SET PARTITIONNAME = DATE_FORMAT(TARGET_DATE, 'p%Y%m%d%H');
    SET TARGET_DATE = TARGET_DATE + INTERVAL 1 hour;
    SET PARTITION_ADD_HOUR = DATE_FORMAT(TARGET_DATE, '%Y%m%d%H');
    SELECT COUNT(*)
    INTO ROWS_CNT
    FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME
      AND table_name = IN_TABLENAME
      AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                          ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (',
                          PARTITION_ADD_HOUR, ') ENGINE = InnoDB);');
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        deallocate PREPARE STMT;
    ELSE
        SELECT CONCAT('partition `', PARTITIONNAME, '` for table `', IN_SCHEMANAME, '.', IN_TABLENAME, '` already exists') AS result;
    END IF;
END;

drop event if exists event_every_hour_call_create_partition;
#每小時(shí)，執(zhí)行事件，調(diào)用每天分區(qū)存儲(chǔ)過程
create event event_every_hour_call_create_partition on schedule
    every '1' hour
        #從現(xiàn)在開始，也可以從制定時(shí)間開始2019-09-11 10:00:00
        starts now()
    on completion preserve
    enable
    do
    #調(diào)用分區(qū)存儲(chǔ)過程
    call auto_create_partition_by_hour('test', 't_auto_insert_hour');



drop procedure if exists auto_create_partition_by_five_minute;
#每五分鐘分區(qū)一次的存儲(chǔ)過程
create procedure auto_create_partition_by_five_minute(IN IN_SCHEMANAME varchar(64), IN IN_TABLENAME varchar(64))
BEGIN
    #當(dāng)前日期存在的分區(qū)的個(gè)數(shù)
    DECLARE ROWS_CNT INT UNSIGNED;
    #當(dāng)前日期，為當(dāng)前日期的后一天
    DECLARE TARGET_DATE TIMESTAMP;
    #分區(qū)的名稱，格式為p201909121009
    DECLARE PARTITIONNAME VARCHAR(15);
    #當(dāng)前分區(qū)名稱的分區(qū)值上限，即為 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_MINUTE VARCHAR(15);
    SET TARGET_DATE = NOW() + INTERVAL 5 minute;
    SET PARTITIONNAME = DATE_FORMAT(TARGET_DATE, 'p%Y%m%d%H%i');
    SET TARGET_DATE = TARGET_DATE + INTERVAL 5 minute;
    SET PARTITION_ADD_MINUTE = DATE_FORMAT(TARGET_DATE, '%Y%m%d%H%i');
    SELECT COUNT(*)
    INTO ROWS_CNT
    FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME
      AND table_name = IN_TABLENAME
      AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                          ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (',
                          PARTITION_ADD_MINUTE, ') ENGINE = InnoDB);');
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        deallocate PREPARE STMT;
    ELSE
        SELECT CONCAT('partition `', PARTITIONNAME, '` for table `', IN_SCHEMANAME, '.', IN_TABLENAME, '` already exists') AS result;
    END IF;
END;

drop event if exists event_every_five_minute_call_create_partition;
#每五分鐘，執(zhí)行事件，調(diào)用分區(qū)存儲(chǔ)過程
create event event_every_five_minute_call_create_partition on schedule
    every '5' minute
        #從現(xiàn)在開始，也可以從制定時(shí)間開始2019-09-11 10:00:00
        starts now() #'2019-09-11 10:00:00'
    on completion preserve
    enable
    do
    #調(diào)用分區(qū)存儲(chǔ)過程
    call auto_create_partition_by_five_minute('test', 't_auto_insert_minute');

新建插入數(shù)據(jù)的存儲(chǔ)過程和對應(yīng)的事件

#清空表，且重置主鍵
truncate table t_auto_insert_day;
truncate table t_auto_insert_hour;
truncate table t_auto_insert_minute;


drop procedure if exists procedure_auto_insert_day;
#創(chuàng)建存儲(chǔ)過程，每天插入5數(shù)據(jù)
create procedure procedure_auto_insert_day()
BEGIN
    declare i int default 0;
    while (i < 5)
        do
            insert into t_auto_insert_day
            values (0, concat('auto_insert_day', i), now(), date_format(now(), '%Y%m%d'));
            set i = i + 1;
        end while;
END;


#創(chuàng)建事件，每天調(diào)用存儲(chǔ)過程，插入5條數(shù)據(jù)
drop event if exists event_every_day_call_insert;
create event event_every_day_call_insert on schedule
    every '1' day
        #從2019-09-11 10:00:00 開始執(zhí)行
        starts now()
    on completion preserve
    enable
    do
    call procedure_auto_insert_day();



drop procedure if exists procedure_auto_insert_hour;
#創(chuàng)建存儲(chǔ)過程，每小時(shí)插入5條數(shù)據(jù)
create
    procedure procedure_auto_insert_hour()
BEGIN
    declare i int default 0;
    while (i < 5)
        do
            insert into t_auto_insert_hour
            values (0, concat('auto_insert_hour', i), concat('auto_insert_hour', i), now(), date_format(now(), '%Y%m%d%H'));
            set i = i + 1;
        end while;
END;

drop event if exists event_every_hour_call_insert;
#創(chuàng)建事件，每小時(shí)調(diào)用存儲(chǔ)過程插入5條數(shù)據(jù)
create event event_every_hour_call_insert on schedule
    every '1' hour
        #從現(xiàn)在開始執(zhí)行
        starts now()
    on completion preserve
    enable
    do
    call procedure_auto_insert_hour();


drop procedure if exists procedure_auto_insert_minute;
#存儲(chǔ)過程-每分鐘插入數(shù)據(jù)
create
    procedure procedure_auto_insert_minute()
BEGIN
    declare i int default 0;
    while (i < 5)
        do
            insert into t_auto_insert_minute
            values (0, concat('procene', i), now(), concat(date_format(now(), '%Y%m%d%H'), minute(now())));
            set i = i + 1;
        end while;

END;


drop event if exists event_every_minute_call_insert;
#每分鐘一次調(diào)用存儲(chǔ)過程
create event event_every_minute_call_insert on schedule
    every 1 minute
        starts now()
    on completion preserve
    enable
    do
    call procedure_auto_insert_minute();

查詢分區(qū)結(jié)果：

select partition_name        part,
       partition_expression  expr,
       partition_description descr,
       table_rows
from information_schema.partitions
where table_schema = 'test'
  and table_name = 't_auto_insert_minute';

返回結(jié)果

p201909121525這個(gè)分區(qū)是初始化表的時(shí)候創(chuàng)建的。

手動(dòng)新增分區(qū)

ALTER TABLE t_auto_insert_minute add PARTITION (PARTITION p201909121509 values less than (201909121509));

刪除分區(qū)，刪除時(shí)不能全部刪除，最少需要留一個(gè)

ALTER TABLE t_auto_insert_minute    DROP PARTITION 201909121509;