数据库自动创建和删除表分区及数据库定时事件
发布时间：2022-10-25 12:56:10 所属栏目：MySql教程 来源：
导读： 1：创建存储过程
CREATE DEFINER=`d`@`%` PROCEDURE `XXX`(
IN_SCHEMANAME VARCHAR ( 64 ),
IN_TABLENAME VARCHAR ( 64 ))
BEGIN
#当前日期存在的分区的个数
D
CREATE DEFINER=`d`@`%` PROCEDURE `XXX`(
IN_SCHEMANAME VARCHAR ( 64 ),
IN_TABLENAME VARCHAR ( 64 ))
BEGIN
#当前日期存在的分区的个数
D
|
1：创建存储过程 CREATE DEFINER=`d`@`%` PROCEDURE `XXX`( IN_SCHEMANAME VARCHAR ( 64 ), IN_TABLENAME VARCHAR ( 64 )) BEGIN #当前日期存在的分区的个数 DECLARE ROWS_CNT INT UNSIGNED; DECLARE D_ROWS_CNT INT UNSIGNED; #目前日期创建数据库表，为当前日期的后一天 DECLARE TARGET_DATE TIMESTAMP; DECLARE D_TARGET_DATE TIMESTAMP; #分区的名称，格式为p20180620 DECLARE PARTITIONNAME VARCHAR ( 9 ); DECLARE D_PARTITIONNAME VARCHAR ( 9 ); #当前分区名称的分区值上限 DECLARE PARTITION_ADD_DAY VARCHAR ( 9 ); SET TARGET_DATE = NOW() + INTERVAL 1 DAY; #保留30天内分区 SET D_TARGET_DATE = NOW() - INTERVAL 30 DAY; SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' ); SET D_PARTITIONNAME = DATE_FORMAT( D_TARGET_DATE, 'p%Y%m%d' ); SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY; SET PARTITION_ADD_DAY = TO_DAYS(TARGET_DATE); SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.PARTITIONS WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME; SELECT COUNT(*) INTO D_ROWS_CNT FROM information_schema.PARTITIONS WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = D_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; IF D_ROWS_CNT = 1 THEN SET @delete = CONCAT( ' ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`', ' DROP PARTITION ', D_PARTITIONNAME, ';' ); PREPARE STMT2 FROM @delete; EXECUTE STMT2; DEALLOCATE PREPARE STMT2; END IF; END 2：创建数据库定时事件 （编辑：云计算网_梅州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
浙公网安备 33038102330479号