mysql定时任务，每天定时执行

最近在公司需要做一个mysql每天定时执行存储过程，完成后记录一下，以便日后查阅。
这里我们实现一个定时任务，每天凌晨1点执行一个存储过程，向用户表插入一条记录。

首先查看mysql数据库是否开启了事件计划 ，如果没有需要开启事件

1. 查看事件计划状态

show variables like '%event_sche%';

可以看到，事件计划未开启

2. 开启事件计划，有以下两种方法

1）在命令行执行语句修改，这种方式虽然能生效，但是会在数据库重启后恢复原来的状态

 set global event_scheduler=1;

2）修改my.ini配置文件，使配置在数据库重启后依然生效，修改后记得要重启数据库

 [mysqld]
event_scheduler=ON

创建用户表user

CREATE TABLE `user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建存储过程user_procedure

-- 先删除存储过程
DROP PROCEDURE IF EXISTS user_procedure; 
-- 创建存储过程
delimiter $$
create procedure user_procedure()
begin
insert into user(user_name , age , create_time) values ('张萌' , 26 , now());
end$$

创建完成后可以手动调用一次存储过程，看是否能正常执行

call user_procedure();

此时刷新user表，可以看到表中新增了一条记录，说明存储过程符合预期。

如果想要再次查看存储过程的创建语句，可以通过下面命令

show create procedure user_procedure;

创建定时任务event，每天的凌晨一点执行

-- 先删除事件
DROP EVENT IF EXISTS user_event ;	
CREATE EVENT `user_event`	-- 创建名字为user_event的事件
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR) -- 每隔一天执行一次，开始执行时间为明天凌晨1点整
ON COMPLETION NOT PRESERVE
ENABLE
DO call user_procedure()	-- 指定要执行的存储过程
;

上述脚本中的执行计划说明
1） SCHEDULE EVERY 1比较好理解，就是每天执行一次
2）开始执行时间我们可以先看一下DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR) 的值是多少。
首先查询一下当前时间

select now();

开始执行时间为

select DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR);

可以看到，事件的开始执行时间为当前时间的第二天凌晨1点。这样定时任务就配置好了

查看所有事件可以使用命令

select * from information_schema.`EVENTS`;

开启关闭事件使用

alter event user_event on completion preserve enable; -- 开启定时任务
alter event user_event on completion preserve disable; -- 关闭定时任务

最后

为了方便大家测试，提供了 每隔一分钟执行一次，开始执行时间为当前时间的下一分钟的脚本

-- 先删除事件
DROP EVENT IF EXISTS user_event ;	
CREATE EVENT `user_event`	-- 创建名字为user_event的事件
ON SCHEDULE EVERY 1 MINUTE STARTS DATE_ADD(now(), INTERVAL 1 MINUTE) -- 每隔一分钟执行一次，开始执行时间为当前时间的下一分钟
ON COMPLETION NOT PRESERVE
ENABLE
DO call user_procedure()	-- 指定要执行的存储过程
;

这样我们就可以看到用户表中每分钟都会新增一条记录。

借助可视化客户端

除了纯手写脚本外，我们也可以借助可视化客户端来辅助我们开发，比如navicat
如下图所示，选中一个数据库后，点击事件->新建事件，在定义选项卡中填写我们要执行的sql语句

然后再计划选项卡中设置执行时间

上面的配置表示：在当前时间的一分钟后执行，且该事件只会执行一次，执行完成后事件会自动删除。我们还可以在SQL预览选项卡中预览navicat帮我们生成好的sql语句，而不需要再纯手写

配置完成后点击保存按钮，输入事件名称即可。

如果想要多次执行，可以在EVERY区域进行配置

上面的配置表示：每隔一分钟执行一次，开始执行时间为当前时间的后一分钟。