
-- 查看是否开启:
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