#检测事件是否开启
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')
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。