数据库自动创建和删除表分区及数据库定时事件

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：创建数据库定时事件

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!