mysql定时任务

#查看当前是否已开启事件调度器

 

show variables like 'event_scheduler';

 

#要想保证能够执行event事件，就必须保证定时器是开启状态，默认为关闭状态

 

set global event_scheduler =1;

 

#或者set GLOBAL event_scheduler = ON;

 

# 选择库/‘’‘’‘’‘’‘’

 

use TRAVEL_CARD_UAT; # 如果原来存在该名字的任务计划则先删除

 

drop event if exists SetEVToInvalidStatus_AtNight_0100; delimiter $$

 

# 每天晚上01:00

 

create event SetEVToInvalidStatus_AtNight_0100

#on schedule every 1 DAY starts timestamp '2017-04-24 10:00:00'

 

# 测试使用 1 hour

 

on schedule every 1 hour starts timestamp '2017-04-24 10:00:10'

 

do

 

begin

 

start transaction;

 

set @timenow=now(); #开始事务

 

# 表1

 

update tb_ev_stocks set FSTATUS=3 where FSTATUS=0 and FVALIDENDDATE < @timenow ;

 

# 表2

 

update tb_ev_stock_details set FSTATUS=3 where FSTATUS=0 and FVALIDENDDATE < @timenow ;

 

commit; #提交事务

 

 

 

end $$

 

imiter ;

 

# 停止

 

ALTER EVENT SetEVToInvalidStatus_AtNight_0100 DISABLE;

 

# 开启

 

alter event SetEVToInvalidStatus_AtNight_0100 enable;

 

# 查看状态

 

select * from mysql.event

 

 

 

删除 数据库中所有表

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')

FROM information_schema.tables

WHERE table_schema = 'zkhk-new';