简单的mysql定时器 执行存储过程
来源:互联网 发布:-3的源码 编辑:程序博客网 时间:2024/06/13 03:54
--创建定时器
CREATE EVENT event_statistics
--设置为每天触发
ON SCHEDULE EVERY 1 DAY
--起始时间
STARTS '2015-01-29 02:00:00'ON COMPLETION PRESERVE DO
--调用存储过程
CALL statistics();
create procedure statistics()
begin
declare done boolean default 0;
declare o varchar(50);
declare oldloginuser int;
declare allloginuser int;
declare newloginuser int;
declare alluser int;
declare installuser int;
declare authinstalluser int;
declare noauthuser int;
declare monthuser int;
declare weekuser int;
declare thrice int;
declare thricestr varchar(50);
declare fifth int;
declare tentimes int;
declare done_1 boolean default 0;
declare app_name varchar(100);
declare cursorforstation cursor
for
select no from t_wifi_station;
declare continue handler for sqlstate '02000' set done=1;
open cursorforstation;
repeat
if o!="" then
select count(distinct userid) into oldloginuser from t_user_login_log where
stationno = o and date(logintime) < date_sub(curdate(),interval 1 day) and userid
in
(select userid from t_user_login_log where
stationno = o and date(logintime) = date_sub(curdate(),interval 1 day));
select count(distinct userid) into allloginuser from t_user_login_log where
stationno = o and date(logintime) = date_sub(curdate(),interval 1 day);
set newloginuser = allloginuser - oldloginuser;
select count(1) into allloginuser from t_user_login_log where
stationno = o and date(logintime) = date_sub(curdate(),interval 1 day);
select count(1) into alluser from t_wifi_user where
stationno = o and date(installtime) <= date_sub(curdate(),interval 1 day);
select count(1) into installuser from t_wifi_user where stationno = o
and date(installtime) = date_sub(curdate(),interval 1 day);
select count(1) into authinstalluser from t_wifi_user where stationno = o
and status =1 and date(installtime) = date_sub(curdate(),interval 1 day);
set noauthuser = installuser - authinstalluser;
select count(1) into monthuser from t_user_login_log where
date(logintime) < date_sub(curdate(),interval 1 month) and userid
in
(select userid from t_user_login_log where stationno = o
and date(logintime) = date_sub(curdate(),interval 1 day));
select count(1) into weekuser from t_user_login_log where date(logintime) < date_sub(curdate(),interval 1 week) and userid
in
(select userid from t_user_login_log where stationno = o
and date(logintime) = date_sub(curdate(),interval 1 day));
insert into t_statistics(staionno,loginnum,olduser,newuser,usertotal,install,authnum,noauthnum,monthuser,weekuser,gettime)
values (o,allloginuser,oldloginuser,newloginuser,alluser,installuser,authinstalluser,noauthuser,monthuser,weekuser,date_sub(curdate(),interval 1 day));
select count(*) into thrice from (select count(1) from t_user_login_log where stationno = o and date(logintime) = date_sub(curdate(),interval 1 day)
group by userid having count(1) >=3) as mytable;
insert into t_rate(staionno,numkey,numvalue,gettime) values(o,3,thrice,date_sub(curdate(),interval 1 day));
select count(*) into fifth from (select count(1) from t_user_login_log where stationno = o and date(logintime) = date_sub(curdate(),interval 1 day)--日期等于昨天的数据
group by userid having count(1) >=5) as mytable;
insert into t_rate(staionno,numkey,numvalue,gettime) values(o,3,fifth,date_sub(curdate(),interval 1 day));
select count(*) into tentimes from (select count(1) from t_user_login_log where stationno = o and date(logintime) = date_sub(curdate(),interval 1 day)
group by userid having count(1) >=10) as mytable;
insert into t_rate(staionno,numkey,numvalue,gettime) values(o,3,tentimes,date_sub(curdate(),interval 1 day));--昨天
end if;
fetch cursorforstation into o;
until done
end repeat;
close cursorforstation;
call appstapro();
end
create procedure appstapro()
begin
declare done_1 boolean default 0;
declare app_name varchar(100);
declare app_count int;
declare app_statistics cursor
for
select appname,count(1) coun from t_wifi_userapp
group by appname order by count(1) desc limit 0,30;
declare continue handler for sqlstate '02000' set done_1=1;
delete from t_applist;
open app_statistics;
repeat
if app_name!="" then
insert into t_applist(name,downnum,gettime)
values(app_name,app_count,date_sub(curdate(),interval 1 day)); --昨天的日期
end if;
fetch app_statistics into app_name,app_count;
until done_1
end repeat;
close app_statistics;
end
show events;
查看定时器状态
SHOW VARIABLES LIKE 'event_scheduler'
//开启定时器
SET GLOBAL event_scheduler = 1;
- 简单的mysql定时器 执行存储过程
- mysql定时器执行存储过程
- MySQL --- MySQL的定时器和存储过程
- MySQL存储过程,定时器
- MySQL 存储过程和定时器的应用
- mysql触发器、定时器、存储过程的使用。
- 简单的mysql存储过程
- 简单mysql的存储过程
- mysql简单的存储过程
- mysql 定时器调用存储过程
- mysql定时器调用存储过程
- mysql 定时器 启动 存储过程
- mysql执行存储过程
- MySQL 用户执行存储过程的权限
- mysql如何跳出存储过程的执行
- Hibernate4.x执行mysql的存储过程
- MySQL 用户执行存储过程的权限
- mysql执行存储过程权限的问题
- 写给自己的第一篇
- C# 事件
- LINUX 游戏服务器之旅4_mongodb环境
- iOS编译错误
- Java_IO流_File类总结(扫描某个盘的全部内容)
- 简单的mysql定时器 执行存储过程
- poi读取excel文件
- RESTful架构的理解
- 使用swiftype实现站内搜索
- 用css创建的二级菜单
- MySQL数据库事务隔离级别(Transaction Isolation Level)
- Nginx+Tomcat集群部署
- [BZOJ1016]JSOI2008最小生成树计数 |kruskal|乘法原理|dfs
- 用wireshark抓iOS真机的包