MySQL创建定时任务动态创建月表

#设置时区
set time_zone = '+8:00';


#开启事件调度器
set GLOBAL event_scheduler = 1;


#设置数据库
use game_center_dashboard;


#如果存在同名任务先删除
drop event if exists table_shard_by_month;


#设置分隔符为 '$$' ，mysql默认的语句分隔符为 ';' ，
#这样在后续的 create 到 end 这段代码都会看成是一条语句来执行
DELIMITER $$


#创建计划任务，每月执行一次
create event table_shard_by_month 
on schedule  every 1 month 


#当月手动替换
#starts current_timestamp
#下个月第一天00:00执行
starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day), interval 1 month),interval 0 hour)


#持续执行
on completion preserve
enable
do


#开始该计划任务
begin


-- do something 编写你的计划任务要做的事


-- 用concat函数拼接动态创建月表  如 game_center_active_user_201606


 set @target_tname = 'game_center_active_user';
 set @month = date_format(now(),'%Y%m');
 set @sql_create_table = concat('create table if not exists ',@target_tname,'_',@month,' like ',@target_tname);
 prepare sql_create_table from @sql_create_table;
 execute sql_create_table;


 set @target_tname = 'game_center_add_download_log';
 set @month = date_format(now(),'%Y%m');
 set @sql_create_table = concat('create table if not exists ',@target_tname,'_',@month,' like ',@target_tname);
 prepare sql_create_table from @sql_create_table;
 execute sql_create_table;


# set @target_tname = 'game_center_arrive_user';
# set @month = date_format(now(),'%Y%m');
# set @sql_create_table = concat('create table if not exists ',@target_tname,'_',@month,' like ',@target_tname);
# prepare sql_create_table from @sql_create_table;
# execute sql_create_table;


 set @target_tname = 'game_center_brow_page_log';
 set @month = date_format(now(),'%Y%m');
 set @sql_create_table = concat('create table if not exists ',@target_tname,'_',@month,' like ',@target_tname);
 prepare sql_create_table from @sql_create_table;
 execute sql_create_table;


 set @target_tname = 'game_center_download_data';
 set @month = date_format(now(),'%Y%m');
 set @sql_create_table = concat('create table if not exists ',@target_tname,'_',@month,' like ',@target_tname);
 prepare sql_create_table from @sql_create_table;
 execute sql_create_table;




#结束计划任务
end $$
#将语句分割符设置回 ';'
DELIMITER ;