#检测事件是否开启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();
注:
#创建存储过程(动态表名)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语句查看该参数,修改并重启mysqlshow VARIABLES like ‘%max_allowed_packet%‘;
set global max_allowed_packet = 25600;
原文:http://5474584.blog.51cto.com/5464584/1875852