今天研究了一波mysql的event功能,记录两个创建event的语句,以备后面使用。
sql1:每月1号创建下个月的表
DROP EVENT IF EXISTS `create_table_every_month`;
CREATE EVENT `create_table_every_month` ON SCHEDULE EVERY '1' MONTH STARTS '2021-08-01 00:00:00' ENDS '2031-08-01 00:00:00' ON COMPLETION PRESERVE ENABLE COMMENT '每月1号创建下个月的t_recv_data表' DO BEGIN DECLARE `@date` VARCHAR(15);
DECLARE `@sqlStr` VARCHAR(2560);
SET
`@date` = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y%m');
SET
@sqlstr = CONCAT(
"CREATE TABLE t_",
`@date`,
"(`id` bigint NOT NULL AUTO_INCREMENT COMMENT '唯一标识的记录ID,自增',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
) ENGINE=InnoDB AUTO_INCREMENT=151812 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=' 测试表'"
);
PREPARE stmt
FROM
@sqlstr;
EXECUTE stmt;
END;
sql2:每月1号删除6个月前的表
DROP EVENT IF EXISTS `drop_table_every_month`;
CREATE EVENT `drop_table_every_month` ON SCHEDULE EVERY '1' MONTH STARTS '2021-08-01 00:00:00' ENDS '2031-08-01 00:00:00' ON COMPLETION PRESERVE ENABLE COMMENT '每月1号删除6个月前的表' DO BEGIN DECLARE `@date` VARCHAR(15);
DECLARE `@sqlStr` VARCHAR(2560);
SET
`@date` = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 6 MONTH), '%Y%m');
SET
@sqlstr = CONCAT("DROP TABLE t_", `@date`);
PREPARE stmt
FROM
@sqlstr;
EXECUTE stmt;
END;