MySQL使用存储过程分表

涉及技术有Sql语句、存储过程、MySQL事件

需求：按月对每个表进行分表备份操作

实现：编写一个存储过程1、重命名现有的表名后缀添加上一个月，然后创建相同结构的新表。2、定时执行该任务

备份的表名：mmall_order

存储过程名称：p_month_backups

定时任务名称：month_call_order_backups

一、创建存储过程

create procedure p_month_backups()
BEGIN
    # 创建表名后缀，上个月
    SET @dateStr = DATE_FORMAT(date_add(now(),interval-1 month), '%Y_%m');
    #这里直接重命名原来的表
    SET @renameTableName = CONCAT("rename table mmall_order to mmall_order_",@dateStr);
    PREPARE stmt FROM @renameTableName;
    EXECUTE stmt;
     
    #创建新表
    SET @createStr = CONCAT("create table mmall_order as select * from mmall_order_",@dateStr,"  where 1=2");
    PREPARE stmt FROM @createStr;
    EXECUTE stmt; 
END ;

步骤依次是：重命名原来的表，创建新表

二、调用存储过程测试是否能达到理想的结果

如上图所示，存储过程达到理想的效果，然后编写定时任务(事件)执行该存储过程。

三、编写事件

首先确认打开定时器

#查看定时器是否开始  ON/OFF
show variables like 'event_scheduler';
#若结果为OFF 则执行下面语句开启
SET GLOBAL event_scheduler = 1;

首先创建一分钟执行一次的定时任务，测试使用

CREATE EVENT `month_call_order_backups`   
ON SCHEDULE
EVERY '1' DAY_MINUTE  STARTS '2018-12-19 00:00:00'   
 DO call p_month_backups();

然后还原之前调用的数据等待一分钟看效果

drop table mmall_order

rename table mmall_order_2019_08 to  mmall_order

我这里为了测试，时间调整为10秒，效果如下图：

然后把存储过程调用的时间改为每月一号凌晨调用即可

扩展资料：

#查看定时器是否开始  ON/OFF
show variables like 'event_scheduler';
#若结果为OFF 则执行下面语句开启
SET GLOBAL event_scheduler = 1;

show processlist; 

show events;

也可以添加一个日志表，每次定时任务执行都插入开始和结束日志：

CREATE TABLE `sys_log` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'log的id唯一标识',
  `log_type` int(11) DEFAULT NULL COMMENT 'log的类型，{info:1,error:0 } error,info或者其它',
  `log_trigger` varchar(225) DEFAULT NULL COMMENT 'log的触发器，例如存储过程等',
  `log_content` varchar(225) DEFAULT NULL COMMENT 'log记录的内容',
  `log_creatime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'log的创建时间',
  `log_remarks` varchar(225) DEFAULT NULL COMMENT 'log的备注',
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后把存储过程写成活的，例如表名作为参数传进去，更改后如下：

create procedure p_month_backups(in  tabName VARCHAR (255))
BEGIN
insert into sys_log (log_type,    log_trigger,    log_content,    log_creatime,    log_remarks)
    VALUES
    (1,"procedure-p_month_backups","begin",now(),CONCAT("操作表名称：",tabName));
# SET @dateStr = DATE_FORMAT(NOW(), '%Y_%m');
SET @dateStr = DATE_FORMAT(date_add(now(),interval-1 month), '%Y_%m');
#这里直接重命名原来的表
SET @renameTableName = CONCAT("rename table ",tabName," to ",tabName,"_",@dateStr);
PREPARE stmt FROM @renameTableName;
EXECUTE stmt;
 
#创建新表
SET @createStr = CONCAT("create table ",tabName," as select * from ",tabName,"_",@dateStr,"  where 1=2");
PREPARE stmt FROM @createStr;
EXECUTE stmt; 
insert into sys_log (log_type,    log_trigger,    log_content,    log_creatime,    log_remarks)
    VALUES
    (1,"procedure-p_month_backups","end",now(),CONCAT("操作表名称：",tabName));
END ;