前言:
目前小伙伴们对“mysql分区表加分区”大体比较关注,看官们都想要剖析一些“mysql分区表加分区”的相关文章。那么小编在网摘上搜集了一些关于“mysql分区表加分区””的相关内容,希望咱们能喜欢,小伙伴们快快来学习一下吧!概述
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 PROCEDUREIF 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 PRESERVEENABLECOMMENT 'Creating partitions By month'DO BEGIN #调用刚才创建的存储过程,第一个参数是数据库名称,第二个参数是表名称 CALL ycdb.create_partition_by_month("ycdb", "t1");END$$DELIMITER ;
如果没有执行,请检查Mysql是否开启了event(默认是关闭的)
[mysqld] event_scheduler=ON
后面会分享更多devops和DBA方面内容,感兴趣的朋友可以关注下~
版权声明:
本站文章均来自互联网搜集,如有侵犯您的权益,请联系我们删除,谢谢。
标签: #mysql分区表加分区 #mysql自动分区表