JAVA定时创建mysql表

最新推荐文章于 2024-07-10 21:37:49 发布

柠檬味的Cat

最新推荐文章于 2024-07-10 21:37:49 发布

阅读量184

点赞数 1

分类专栏： Java 文章标签： java mysql

本文链接：https://blog.csdn.net/qq_42739591/article/details/133273855

版权

Java 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Java代码实现

	@Value("${timeTask.scheduled}")
	private boolean scheduled;

	// 提前创建3天的表
	@Scheduled(cron = "0 0 1 * * ?")
	public void createTable() {
		if (scheduled) {
			log.info("开始检验日志表");
			LocalDate localDate = LocalDate.now();
			LocalDate tomoDate = localDate.plusDays(1);
			// 当前日期时间
			String nowtableName = tomoDate.format(DateTimeFormatter.BASIC_ISO_DATE);
			// 1.判断表是否存在
			Integer exist = operationLogService.existTable(nowtableName);
			if (exist == 0) {
				// 2.不存在创建日表
				operationLogService.createTable(nowtableName, 3);
				log.info("定时任务已创建3天的日志表, 开始日期{}", tomoDate);
			}
		}
	}

mybatis.xml代码实现

<select id="createTable" resultType="java.lang.Integer"
		statementType="CALLABLE">
		{
		call proc_create_table_log(
		#{startTime,mode=IN,jdbcType=VARCHAR},
		#{interval,mode=IN,jdbcType=INTEGER}
		)
		}
</select>

mysql创建可运行函数，在mybatis.xml中被调用。

CREATE DEFINER=`root`@`%` PROCEDURE `proc_create_table_log`(in val_s varchar(10), in val_e int)
begin
declare i int;
declare idays varchar(8);
set i=0;
while i<val_e do
set idays=DATE_FORMAT(date_add(val_s, interval i day),'%Y%m%d');
set @sql_create_table = concat(
"CREATE TABLE if not exists `operation_log_",idays,"`  (
   `id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '操作日志id',
  `creator` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作人',
	`user_id` int(10) NULL DEFAULT NULL COMMENT '操作人ID',
  `title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标题',
  `content` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '内容',
  `create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `ip` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作人ip',
  `city_code` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '城市代码',
  `time` int(11) NULL DEFAULT NULL COMMENT '耗时 单位秒',
  `result` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '结果，1-成功 2-失败',
  `method` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求方法',
  `module` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '模块名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '操作日志表' ROW_FORMAT = Dynamic;
");
PREPARE sql_create_table FROM @sql_create_table;   
EXECUTE sql_create_table;
set i=i+1;
end while;
end