基于MYSQL数据库实现自动创建分区

13 篇文章 1 订阅

概述
Mysql不能自动创建分区,需要使用mysql event事件的方式自动创建分区,今天主要分享一下在MYSQL数据库应如何实现自动创建分区。
1、准备测试表及数据

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `insert_user` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'Insert User',
  `insert_date` datetime NOT NULL COMMENT 'Insert Date',
  PRIMARY KEY (`id`,insert_date) USING BTREE,
  KEY `fsl_idx_order_release_insert_date` (`insert_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='test'
/*!50500 PARTITION BY RANGE  COLUMNS(insert_date)
(
 PARTITION p202007 VALUES LESS THAN ('2020-08-01') ENGINE = InnoDB,
 PARTITION p202008 VALUES LESS THAN ('2020-09-01') ENGINE = InnoDB) */
insert into t1 values (1,'hwb',now()),(1,'hwb2',now()+1),(1,'hwb3',now()+2);

2、相关存储过程
每次执行先校验当前分区是否存在,如果存在则不处理;不存在则创建

DELIMITER //
#该表所在数据库名称
USE `ycdb` // DROP PROCEDURE
IF
	EXISTS `create_partition_by_month` // CREATE PROCEDURE `create_partition_by_month` ( IN_SCHEMANAME VARCHAR ( 64 ), IN_TABLENAME VARCHAR ( 64 ) ) BEGIN
	DECLARE
		ROWS_CNT INT UNSIGNED;
	DECLARE
		BEGINTIME TIMESTAMP;
	DECLARE
		ENDTIME INT UNSIGNED;
	DECLARE
		PARTITIONNAME VARCHAR ( 16 );
	DECLARE
		ENDTIME_DATETIME VARCHAR ( 30 );
	
	SET BEGINTIME = DATE( NOW( ) - INTERVAL DAY ( NOW( ) ) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH );
    select BEGINTIME;
	SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y%m' );
    select PARTITIONNAME;
	SET ENDTIME = UNIX_TIMESTAMP( BEGINTIME + INTERVAL 1 MONTH );
	select endtime;
	SET ENDTIME_DATETIME = FROM_UNIXTIME( ENDTIME );
    select ENDTIME_DATETIME;
	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 ('", ENDTIME_DATETIME, "') 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;
												 
                           
--手动测试
call create_partition_by_month("ycdb", "t1");

3、数据库定时任务(每小时执行一次)
每小时执行一次,检查下个月的表分区是否已经创建,如果没有创建,则调用上面的存储过程创建。

DELIMITER $$
#该表所在的数据库名称
USE `ycdb`$$
CREATE EVENT IF NOT EXISTS `Month_partition_t1`
ON SCHEDULE EVERY 1 hour   #执行周期,还有天、月等等
STARTS '2020-08-10 17:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions By month'
DO BEGIN
    #调用刚才创建的存储过程,第一个参数是数据库名称,第二个参数是表名称
    CALL ycdb.create_partition_by_month("ycdb", "t1");
END$$
DELIMITER ;

如果没有执行,请检查Mysql是否开启了event(默认是关闭的)

[mysqld] event_scheduler=ON
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值