今天有个业务需求,每天要重置流水号.想起oracle有job 于是联想到mysql应该有类似的.发现mysql
通过event 来实现
create event [if not exists] event_name on schedule schedule [on completion [not] preserve] [enable | disable] [comment 'comment'] do sql_statement; schedule: at timestamp [+ interval interval] | every interval [starts timestamp] [ends timestamp] interval: quantity {year | quarter | month | day | hour | minute | week | second | year_month | day_hour | day_minute | day_second | hour_minute | hour_second | minute_second}
简单使用如下
delimiter $$ /** * 重置流水号 * */ -- set global event_scheduler = on$$ -- required for event to execute but not create create /*[definer = { user | current_user }]*/ event `xxx`.`reset_serialnumber` on schedule every 1 day starts '2014-05-06 23:59:59' /* uncomment the example below you want to use */ -- scheduleexample 1: run once -- at 'yyyy-mm-dd hh:mm.ss'/current_timestamp { + interval 1 [hour|month|week|day|minute|...] } -- scheduleexample 2: run at intervals forever after creation -- every 1 [hour|month|week|day|minute|...] -- scheduleexample 3: specified start time, end time and interval for execution /*every 1 [hour|month|week|day|minute|...] starts current_timestamp/'yyyy-mm-dd hh:mm.ss' { + interval 1[hour|month|week|day|minute|...] } ends current_timestamp/'yyyy-mm-dd hh:mm.ss' { + interval 1 [hour|month|week|day|minute|...] } */ /*[on completion [not] preserve] [enable | disable] [comment 'comment']*/ do begin update xxx_sequence set current_value = 0 where id = 1; end$$ delimiter ;