mysql 表分区和自动创建新的分区

1 创建新的表

CREATE TABLE `send_url_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `do_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '标记1yak 2web',
  `source` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '渠道',
  `send_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '类型',
  `push_date` int(11) COLLATE utf8mb4_general_ci NOT NULL  COMMENT '发送日期',
  `send_url` text COLLATE utf8mb4_unicode_ci COMMENT '发送url',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`,`push_date`),
  KEY `send_url_log_do_id_index` (`do_id`),
  KEY `send_url_log_source_index` (`source`),
  UNIQUE KEY `id_UNIQUE` (`id`,`push_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='上报日志表'
PARTITION BY RANGE (`push_date`)
(PARTITION p0 VALUES LESS THAN (20230201) ENGINE = InnoDB,
 PARTITION p20230202 VALUES LESS THAN (20230202) ENGINE = InnoDB,
 PARTITION p20230203 VALUES LESS THAN (20230203) ENGINE = InnoDB,
 PARTITION p20230204 VALUES LESS THAN (20230204) ENGINE = InnoDB
 );　

2添加新的分区

 alter TABLE `send_url_log` add PARTITION(
 PARTITION p20230205 VALUES LESS THAN (20230205) ENGINE = InnoDB
);

3删除分区

alter table `send_url_log`  drop PARTITION p20230205;

4 创建存储过程

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `send_url_log_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    #当前日期存在的分区的个数
    DECLARE ROWS_CNT INT UNSIGNED;
    #目前日期，为当前日期的后一天
    DECLARE TARGET_DATE TIMESTAMP;
    #分区的名称，格式为p20230203
    DECLARE PARTITIONNAME VARCHAR(9);
    #当前分区名称的分区值上限，即为 PARTITIONNAME + 2
    DECLARE PARTITION_ADD_DAY VARCHAR(9);
    SET TARGET_DATE = NOW() + INTERVAL 2 DAY;
    SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' );
    SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY;
    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;;
DELIMITER ;

5 创建定时任务

DELIMITER $$
#该表所在的数据库名称
USE `sy_ad_game`$$
CREATE EVENT IF NOT EXISTS `daily_generate_partition`
ON SCHEDULE EVERY 1 hour   #执行周期，还有天、月等等
STARTS '2023-02-03 00:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
    #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称
    CALL datacollectcenter.send_url_log_by_day('sy_ad_game','send_url_log');
END$$
DELIMITER ;

6 查看mysql是否开启了定时任务

show variables like '%event_sche%';

7 开启定时任务

set global event_scheduler=1;

8 查询定任务

SELECT event_name,event_definition,interval_value,interval_field,status

FROM information_schema.EVENTS;

查询分区信息

SELECT *
FROM information_schema.PARTITIONS WHERE PARTITION_NAME IS NOT NULL