mysql event使用示例

今天研究了一波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;