业务要求,需要对数据库某张记录表按月分表,这里做简单记录
1.创建存储过程:
drop procedure if exists create_xxx_every_month;
create procedure create_xxx_every_month()
BEGIN
SET @dateStr = DATE_FORMAT(NOW(), '%Y_%m');
#这里直接重命名原来的表
SET @renameTableName = CONCAT("rename table xxx to xxx_",@dateStr);
PREPARE stmt FROM @renameTableName;
EXECUTE stmt;
#创建新表
SET @createStr = CONCAT("create table xxx(
`id` int not null auto_increment comment 'id',
`x1` varchar(30) comment '字段1',
primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
PREPARE stmt FROM @createStr;
EXECUTE stmt;
END ;
这里只直接采用重命名旧表的方式来进行,具体的逻辑可能还需要判断表是否存在等关系,这里不多详述
2.开启定时器:
#查看定时器是否开始 ON/OFF
show variables like 'event_scheduler';
#若结果为OFF 则执行下面语句开启
set global event_scheduler=1;
3.创建一个定时执行存储过程的事件:
drop event if exists event_xxx;
create event if not exists event_xxx
on schedule every 1 month starts timestamp '2019-01-01 00:00:00'
on completion preserve
do call create_xxx_every_month();
4.启动事件 像上面那种刚创建好的事件的状态默认就是启动的,所以不需要再次开启:
#启动关闭事件
alter event event_xxx on completion preserve disable;
alter event event_xxx on completion preserve enable;
5.其他命令:
#查看事件event
show events;
#查看存储过程
show procedure status;
或者
select `name` from mysql.proc where db = 'dbname' and `type` = 'PROCEDURE';