二、适用范围对于每隔一段时间就有固定需求的操作,如创建表,删除数据等操作,可以使用event来处理。
例如:使用event在每月的1日凌晨1点自动创建下个月需要使用的三张表。
三、使用权限
开启event :(1)执行 SET GLOBAL event_scheduler = 1;(2)在mysql配置文件中设置:event_scheduler=ON
查看event :(1)SHOW VARIABLES LIKE 'event_scheduler'; (2)要求东八区 show variables like '%time_zone%';
eg:
早上5点往t_bb_register_loan中插入数据
create EVENT bb_register_loan_to_everyday
on SCHEDULE every 1 day starts timestamp '2015-05-30 05:00:00'
do
insert into t_bb_register_loan(createTime,userId,nickName,realName,idCardNo,mobile,registerTime,inviterNickName,loanId,investAmount,investTime,qx,amount,redAmount,coupon,returnAmount)
select
NOW(),b.userid,b.nickName,b.realName,b.idCardNo,b.mobile,b.registerTime,(select u.nickname from user_main u where u.mobile=s.mobile),
a.loanid,a.investAmount,a.investTime,CONCAT(a.termCount,if(a.termUnit=1,'天',if(a.termUnit=2,'周','月'))),a.amount,a.red_money_amount,a.coupon,a.returnAmount
from user_main b
left join (
select a.loanid,c.title,c.termCount,c.termUnit,c.amount,c.openTime,c.fullTime,a.investAmount,a.investTime,a.investorUserId,a.red_money_amount,t.coupon,t.returnAmount
from loan_investor a,loan c,(select t.loanid,t.LOAN_INVESTOR_ID,r.coupon,r.returnAmount from trade t left join t_requestcouponrecord r on t.couponid=r.id where SERIAL_NUMBER like '05%' and t.TRADE_STATUS='SUCCESS'
)t
where a.loanid = c.loanid and
a.loanid=t.loanid and a.id=t.LOAN_INVESTOR_ID and
a.investTime >DATE_FORMAT(SUBDATE(now(),interval 1 day),'%Y-%m-%d') and a.investTime
)a on a.investorUserId = b.userid
left join user_invited_code d on b.userid=d.userid
left join(
select r.mobile,r.userid,IFNULL(recommendcode,r.mobile) code from t_recommendinfo r
) s on s.userid=b.userid
where b.registerTime >=DATE_FORMAT(SUBDATE(now(),interval 1 day),'%Y-%m-%d') and b.registerTime
删除event : drop event if exists bb_register_loan_to_everyday;
查询:select * from mysql.event