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

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！