mysql怎么导入事件和存储过程_mysql 事件 存储过程练习

-- mysql事件实战  -- 查看事件是否开启  show variables like 'event_scheduler';  select @@event_scheduler;  show processlist;  -- 如果看到event_scheduler为on活着processlist中显示有event_scheduler的信息说明已经开启了事件  -- 否则说明事件没有开启，我们需要开启它  -- 开启方式：1：通过动态参数修改  set global event_scheduler= ON;  -- 注意：还是要在my。cnf中添加event_scheduler=ON,如果没有添加  -- mysql重启事件又会回到原来的状态了   use ejabberd; alter table mytest  auto_increment=106; show create table mytest; show events; -- 创建一个测试的test表 CREATE TABLE `ejabberd`.`mytest` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `t1` datetime DEFAULT NULL,   `id2` int(11) NOT NULL DEFAULT '0',   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; -- 创建一个每隔3秒往mytest表中插入一条数据的事件 drop event if exists mytest; create event if not exists mytest  on schedule every 3 second on completion preserve do insert into mytest(id,t1)values('',NOW()); select * from mytest; drop event if exists event_insert_table_mytest; create event event_insert_table_mytest on schedule every 3 second starts '2015-07-01 00:00:00' enable do insert into mytest(id,t1)values('',NOW()); -- 创建一个1分钟后清空mytest表数据的事件 drop event if exists clear_table_mytest; create event clear_table_mytest on schedule at current_timestamp + interval 1 minute do truncate table mytest; -- 创建一个从指定时间开始到10分钟后结束，每隔5秒往mytest表中插入一条数据的事件accessible drop event if exists event_begin_end_insert_table_mytest; create event event_begin_end_insert_table_mytest on schedule every 5 second starts '2015-11-16 11:24:00' ends '2015-11-16 11:24:00' + interval 10 minute on completion preserve do insert into mytest (id,t1) values('',now()); -- 每天凌晨执行的事件：将sys_user的verify值设为0 drop event if exists event_update_table_sys_user_VERIFY; create event event_update_table_sys_user_VERIFY on schedule every 1 DAY starts '2015-11-15 00:00:00' on completion preserve do update sys_user set VERIFY =0; -- 从2015-12-12日14：00：00起每天执行一次pro_del_onlineInfo_90D_before这个存储过程，并带上c参数 -- 1:创建事件 drop event event_del_tal_onlineInfo_90D_before_count;-- 删除事件 create event event_del_tal_onlineInfo_90D_before_count on schedule every 1 day starts '2016-01-26 03:00:00' on completion not preserve enable do call pro_del_onlineInfo_90D_before(90); show procedure status where Db='ejabberd'; -- 查看存储过程 -- 1：删除存储过程 drop procedure p_del_count -- 2:创建存储过程 delimiter // create procedure pro_del_onlineInfo_90D_before(IN date_inter int) begin delete from online_info where online_datedate_sub(now(),interval date_inter day)); end // -- 存储过程练习 -- call pro_getRunning(160);调用存储过程 -- drop procedure pro_getRunning;删除存储过程 delimiter // create procedure pro_getRunning(date_inte int) begin start transaction; SELECT USERNAME,INSERT_DATE ,DATE_SUB(NOW(),INTERVAL date_inte DAY) AS ODERDATE FROM sys_user WHERE INSERT_DATE < DATE_SUB(NOW(),INTERVAL date_inte DAY);  end // -- begin -- 测试，删除180天之前的上线记录，如果180天之内没有登录过，则保存它的上线记录 -- 1:查出90天内的所有用户上线记录 DATE_SUB() 函数从日期减去指定的时间间隔。 -- select online.username from (select * from online_info) online where online.online_date>date_sub(now(),interval date_inter day) -- 2:删除90天以外的同用户名的记录 -- delete from online_info where online_datedate_sub(now(),interval date_inter day)); -- 3:创建存储过程 delimiter // create procedure pro_del_onlineInfo_90D_before(IN date_inter int) begin delete from online_info where online_datedate_sub(now(),interval date_inter day)); end // -- 4：调用存储过程 -- call pro_del_onlineInfo_90D_before(90); -- 5：创建事件 create event event_del_tal_onlineInfo_90D_before_count on schedule every 1 day starts '2016-01-26 03:00:00' on completion not preserve enable do call pro_del_onlineInfo_90D_before(90); -- end