MySQL中利用事件来执行定时任务 亲测收藏版

-- 查看是否开启:

show variables like 'event_scheduler';

-- 如果显示OFF,则输入以下语句开启:

set global event_scheduler = on;

-- 修改 MySQL 配置文件添加,让其自动启动事件

event_scheduler=ON

-- 测试数据表一

DROP TABLE IF EXISTS `web_user`;

CREATE TABLE `web_user` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`address` varchar(200) NOT NULL,

`addtime` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 测试数据表二

DROP TABLE IF EXISTS `web_user2`;

CREATE TABLE `web_user` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`address` varchar(200) NOT NULL,

`addtime` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 类型一:每隔一分钟插入一条数据:

DROP EVENT IF EXISTS `event_minute`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` EVENT `event_minute`

-- 每一分钟执行一次

-- ON SCHEDULE EVERY 1 MINUTE STARTS '2018-03-17 15:41:00' ON COMPLETION NOT PRESERVE ENABLE DO

-- 每天在固定时刻执行

ON SCHEDULE EVERY 1 DAY STARTS '2018-03-17 22:37:00' DO

BEGIN

INSERT INTO web_user(name, address,addtime) VALUES ('test1','test1',now());

-- INSERT INTO web_user(name, address,addtime) VALUES('test2','test2',now());

call proc_user();

END

;;

DELIMITER ;

-- 类型二:特定时间插入一条数据,如:2018-03-17 15:41:00

DROP EVENT IF EXISTS `event_at`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` EVENT `event_at`

ON SCHEDULE AT '2018-03-17 15:51:00' ON COMPLETION NOT PRESERVE ENABLE DO

BEGIN

INSERT INTO web_user(name, address,addtime) VALUES('AT','AT',now());

END

;;

DELIMITER ;

DROP PROCEDURE if exists proc_user;

DELIMITER $$

CREATE PROCEDURE proc_user()

BEGIN

-- 光标Begin-----------------------------------------------------------

DECLARE done INT DEFAULT 0;

DECLARE gbname varchar(20);

DECLARE gbaddress varchar(20);

DECLARE gbobjinfo CURSOR FOR SELECT name,address FROM web_user; -- 声明光标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

open gbobjinfo; -- 打开光标

REPEAT -- 重复执行

FETCH gbobjinfo into gbname,gbaddress; -- 使用光标,读取游标数据

/*判断防止多执行最后一次*/

IF done=0 THEN

insert into web_user2 (name,address)VALUES(gbname,gbaddress);-- 循环插入数据

END IF;

UNTIL done

END REPEAT;

close gbobjinfo;-- 关闭光标

-- 光标End-----------------------------------------------------------

SET @SQLStr="INSERT INTO web_user (name,address,addtime)";

#拼接SQL语句

SET @SQLStr=CONCAT(@SQLStr," ","VALUES ('test2','test2',now())");

PREPARE Stmt FROM @SQLStr;

EXECUTE Stmt;

DEALLOCATE PREPARE Stmt ;

SELECT id INTO @id FROM web_user ORDER BY id Desc Limit 1;

INSERT INTO web_user(name,address,addtime) VALUES(@id,@SQLStr,now());

END $$

DELIMITER $$

-- 调用存储过程

call proc_user();

-----------------------------------------------------------

郑州宇晨PHP培训招生了!

1、原生开发 2、商城开发 3、API接口对接

4、微信开发 5、小程序开发 6、PHPCMS开发

7、服务器搭建等

五个月培训时间全方位为您打造!

在线咨询Q_Q:780050017

V信号:yuchenpeixun360

网址:www@hnphp@net