#检测事件是否开启
show variables like 'event_scheduler';
#开启事件（最好在my.init设置，因为重启后还会变回默认值OFF）
set global event_scheduler = on;
#创建事件（从11月24号开始每天执行一次）
create EVENT eve_createTable
ON SCHEDULE EVERY 1 DAY
STARTS '2016-11-24 00:00:00' ON COMPLETION PRESERVE ENABLE
DO
CALL pro_createTable();
注：
1、ON COMPLETION PRESERVE ENABLE 是创建此事件即开始自动执行
2、SCHEDULE EVERY 1 DAY STARTS '2016-11-24 00:00:00' 从指定时间开始每天执行一次
#创建存储过程（动态表名）
CREATE PROCEDURE pro_createTable()
BEGIN
DECLARE str VARCHAR(20000);
set str= CONCAT('CREATE TABLE member_network_',DATE_FORMAT(now(),'%Y%m%d'),'(
`id` bigint(20) NOT NULL AUTO_INCREMENT ,
`member_id` bigint(20) NULL DEFAULT NULL ,
`host_ip` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`serv_crc` varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
`app_crc` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`sync_time` timestamp NULL DEFAULT NULL ,
`online_time` datetime NULL DEFAULT NULL ,
`type` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`up_stream_flux` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`down_stream_flux` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`total_stream_flux` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`line_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`url` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`action` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`sev_port` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`sor_port` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`protocol` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`regionCode` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`memo` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`policy` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`dns` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`idcard` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`client_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `dept_type` (`regionCode`(255), `serv_crc`(255), `online_time`, `name`, `type`(255)) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT
');
SET @sqlstr=str;
PREPARE stmt from @sqlstr;
EXECUTE stmt;
deallocate prepare stmt;
END;
执行时报错，因为CONCAT拼接超过最大值；
#sql语句查看该参数，修改并重启mysql
show VARIABLES like '%max_allowed_packet%';
set global max_allowed_packet = 25600
mysql的前天、今天、后天
#2016-12-09
DATE_FORMAT(date_sub(curdate(),interval 1 day),'%Y%m%d')
#2016-12-10
DATE_FORMAT(date_sub(curdate(),interval 0 day),'%Y%m%d')
#2016-12-11
DATE_FORMAT(date_sub(curdate(),interval -1 day),'%Y%m%d')