基于MYSQL事件的按月备份表，滚动，保留6次备份

MYSQL事件实现

DELIMITER ;;
CREATE EVENT BACKUP
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN
    select now();
END
;;
DELIMITER ;

返回日期
CURDATE()

返回前一天的数字
DAY(CURDATE()-1)

日期减法函数：计算当天减去月初到昨天的天数,等于这个月的1号
DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY)

日期加法函数：得到下月1号的日期
DATE_ADD
DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY)

日期加法函数：得到下月1号的凌晨1点的时间
DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)

CREATE TABLE `version_replace` (
  `table_name` varchar(32) NOT NULL,
  `table_version` int(10) unsigned NOT NULL DEFAULT ‘0‘,
  UNIQUE KEY `table_name_idx` (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

动态SQL语句实现改表名称为日期变量

SET @command=concat(‘RENAME TABLE `version` TO `version_backup_‘,date_format(now(),‘%y%m‘),‘` , `version_replace` TO `version`‘);
PREPARE modify FROM @command;
EXECUTE modify;

动态SQL语句实现删除6次之前的备份

SET @command=concat(‘DROP TABLE version_backup_‘,date_format(date_sub(now(), interval 6 month),‘%y%m‘),‘‘);
PREPARE modify FROM @command;
EXECUTE modify;

获取格式化后的年月份
select date_format(now(),‘%y%m‘);

获取当前日期的前6个月
select date_sub(now(), interval 6 month);

取得当前日期的前6个月,并格式化显示,为拼接表名称做准备
select date_format(date_sub(now(), interval 6 month),‘%y%m‘);

最终实现

DELIMITER ;;
CREATE EVENT BACKUP
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 0 HOUR)
ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN
    /*创建一个待替换的空表*/
    CREATE TABLE `version_replace` (
    `table_name` varchar(32) NOT NULL,
    `table_version` int(10) unsigned NOT NULL DEFAULT ‘0‘,
    UNIQUE KEY `table_name_idx` (`table_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    /*RENAME语句具有原子性，把原表重命名为日期备份表,并且将创建的新表重命名为原表*/
    SET @command=concat(‘RENAME TABLE `version` TO `version_backup_‘,date_format(now(),‘%y%m‘),‘` , `version_replace` TO `version`‘);
    PREPARE modify FROM @command;
    EXECUTE modify;

    /*删除6个月前的那次备份*/
    SET @command=concat(‘DROP TABLE version_backup_‘,date_format(date_sub(now(), interval 6 month),‘%y%m‘),‘‘);
    PREPARE modify FROM @command;
    EXECUTE modify;
END
;;
DELIMITER ;

文章写的不错？请扫下面作者的讨饭专用码，赞助一下。

基于MYSQL事件的按月备份表，滚动，保留6次备份

标签：col   int   表名   文章   功能   html   date   数据   table