mysql数据库逻辑设计与建表总结（基于MYSQL数据库实现自动创建分区）
mysql数据库逻辑设计与建表总结（基于MYSQL数据库实现自动创建分区）[mysqld] event_scheduler=ON后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下~
概述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
后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下~
