首页 > 代码库 > mysql 通过事件定时为数据库创建动态表名
mysql 通过事件定时为数据库创建动态表名
#检测事件是否开启
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 通过事件定时为数据库创建动态表名
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。