定时任务表sql语句
CREATE TABLE `QRTZ_LOCKS`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`LOCK_NAME` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (`SCHED_NAME`, `LOCK_NAME`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_PAUSED_TRIGGER_GRPS`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (`SCHED_NAME`, `TRIGGER_GROUP`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_SCHEDULER_STATE`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`INSTANCE_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`LAST_CHECKIN_TIME` bigint(13) NOT NULL,
`CHECKIN_INTERVAL` bigint(13) NOT NULL,
PRIMARY KEY (`SCHED_NAME`, `INSTANCE_NAME`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_CALENDARS`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`CALENDAR_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`CALENDAR` blob NOT NULL,
PRIMARY KEY (`SCHED_NAME`, `CALENDAR_NAME`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_JOB_DETAILS`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`JOB_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`JOB_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`DESCRIPTION` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`JOB_CLASS_NAME` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`IS_DURABLE` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`IS_NONCONCURRENT` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`IS_UPDATE_DATA` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`REQUESTS_RECOVERY` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`JOB_DATA` blob NULL,
PRIMARY KEY (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) USING BTREE,
INDEX `IDX_QRTZ_J_REQ_RECOVERY` (`SCHED_NAME`, `REQUESTS_RECOVERY`) USING BTREE,
INDEX `IDX_QRTZ_J_GRP` (`SCHED_NAME`, `JOB_GROUP`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_TRIGGERS`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`JOB_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`JOB_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`DESCRIPTION` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`NEXT_FIRE_TIME` bigint(13) NULL DEFAULT NULL,
`PREV_FIRE_TIME` bigint(13) NULL DEFAULT NULL,
`PRIORITY` int(11) NULL DEFAULT NULL,
`TRIGGER_STATE` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`TRIGGER_TYPE` varchar(8) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`START_TIME` bigint(13) NOT NULL,
`END_TIME` bigint(13) NULL DEFAULT NULL,
`CALENDAR_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`MISFIRE_INSTR` smallint(2) NULL DEFAULT NULL,
`JOB_DATA` blob NULL,
PRIMARY KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) USING BTREE,
INDEX `IDX_QRTZ_T_J` (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) USING BTREE,
INDEX `IDX_QRTZ_T_JG` (`SCHED_NAME`, `JOB_GROUP`) USING BTREE,
INDEX `IDX_QRTZ_T_C` (`SCHED_NAME`, `CALENDAR_NAME`) USING BTREE,
INDEX `IDX_QRTZ_T_G` (`SCHED_NAME`, `TRIGGER_GROUP`) USING BTREE,
INDEX `IDX_QRTZ_T_STATE` (`SCHED_NAME`, `TRIGGER_STATE`) USING BTREE,
INDEX `IDX_QRTZ_T_N_STATE` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`, `TRIGGER_STATE`) USING BTREE,
INDEX `IDX_QRTZ_T_N_G_STATE` (`SCHED_NAME`, `TRIGGER_GROUP`, `TRIGGER_STATE`) USING BTREE,
INDEX `IDX_QRTZ_T_NEXT_FIRE_TIME` (`SCHED_NAME`, `NEXT_FIRE_TIME`) USING BTREE,
INDEX `IDX_QRTZ_T_NFT_ST` (`SCHED_NAME`, `TRIGGER_STATE`, `NEXT_FIRE_TIME`) USING BTREE,
INDEX `IDX_QRTZ_T_NFT_MISFIRE` (`SCHED_NAME`, `MISFIRE_INSTR`, `NEXT_FIRE_TIME`) USING BTREE,
INDEX `IDX_QRTZ_T_NFT_ST_MISFIRE` (`SCHED_NAME`, `MISFIRE_INSTR`, `NEXT_FIRE_TIME`, `TRIGGER_STATE`) USING BTREE,
INDEX `IDX_QRTZ_T_NFT_ST_MISFIRE_GRP` (`SCHED_NAME`, `MISFIRE_INSTR`, `NEXT_FIRE_TIME`, `TRIGGER_GROUP`,
`TRIGGER_STATE`) USING BTREE,
CONSTRAINT `QRTZ_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) REFERENCES `QRTZ_JOB_DETAILS` (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_BLOB_TRIGGERS`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`BLOB_DATA` blob NULL,
PRIMARY KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) USING BTREE,
INDEX `SCHED_NAME` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) USING BTREE,
CONSTRAINT `QRTZ_BLOB_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `QRTZ_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_FIRED_TRIGGERS`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ENTRY_ID` varchar(95) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`TRIGGER_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`INSTANCE_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`FIRED_TIME` bigint(13) NOT NULL,
`SCHED_TIME` bigint(13) NOT NULL,
`PRIORITY` int(11) NOT NULL,
`STATE` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`JOB_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`JOB_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`IS_NONCONCURRENT` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`REQUESTS_RECOVERY` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`SCHED_NAME`, `ENTRY_ID`) USING BTREE,
INDEX `IDX_QRTZ_FT_TRIG_INST_NAME` (`SCHED_NAME`, `INSTANCE_NAME`) USING BTREE,
INDEX `IDX_QRTZ_FT_INST_JOB_REQ_RCVRY` (`SCHED_NAME`, `INSTANCE_NAME`, `REQUESTS_RECOVERY`) USING BTREE,
INDEX `IDX_QRTZ_FT_J_G` (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) USING BTREE,
INDEX `IDX_QRTZ_FT_JG` (`SCHED_NAME`, `JOB_GROUP`) USING BTREE,
INDEX `IDX_QRTZ_FT_T_G` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) USING BTREE,
INDEX `IDX_QRTZ_FT_TG` (`SCHED_NAME`, `TRIGGER_GROUP`) USING BTREE
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_SIMPROP_TRIGGERS`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`STR_PROP_1` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`STR_PROP_2` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`STR_PROP_3` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`INT_PROP_1` int(11) NULL DEFAULT NULL,
`INT_PROP_2` int(11) NULL DEFAULT NULL,
`LONG_PROP_1` bigint(20) NULL DEFAULT NULL,
`LONG_PROP_2` bigint(20) NULL DEFAULT NULL,
`DEC_PROP_1` decimal(13, 4) NULL DEFAULT NULL,
`DEC_PROP_2` decimal(13, 4) NULL DEFAULT NULL,
`BOOL_PROP_1` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`BOOL_PROP_2` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) USING BTREE,
CONSTRAINT `QRTZ_SIMPROP_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `QRTZ_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_CRON_TRIGGERS`
(
`SCHED_NAME` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_NAME` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`TRIGGER_GROUP` varchar(190) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`CRON_EXPRESSION` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`TIME_ZONE_ID` varchar(80) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) USING BTREE,
CONSTRAINT `QRTZ_CRON_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `QRTZ_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = Compact;
CREATE TABLE `QRTZ_SIMPLE_TRIGGERS`
(
`SCHED_NAME` varchar(120) COLLATE utf8_bin NOT NULL,
`TRIGGER_NAME` varchar(190) COLLATE utf8_bin NOT NULL,
`TRIGGER_GROUP` varchar(190) COLLATE utf8_bin NOT NULL,
`REPEAT_COUNT` bigint(7) NOT NULL,
`REPEAT_INTERVAL` bigint(12) NOT NULL,
`TIMES_TRIGGERED` bigint(10) NOT NULL,
PRIMARY KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) USING BTREE,
CONSTRAINT `QRTZ_SIMPLE_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `QRTZ_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8
COLLATE = utf8_bin;
pom依赖
org.springframework.boot
>spring-boot-starter-quartz
com.alibaba
>druid-spring-boot-starter
mysql
>mysql-connector-java
application.yml
spring:
#分布式定时任务相关属性配置
quartz:
properties:
org:
quartz:
scheduler:
instanceName: clusteredScheduler
instanceId: AUTO
jobStore:
class: org.quartz.impl.jdbcjobstore.JobStoreTX
driverDelegateClass: org.quartz.impl.jdbcjobstore.StdJDBCDelegate
tablePrefix: QRTZ_
isClustered: true
clusterCheckinInterval: 10000
useProperties: false
threadPool:
class: org.quartz.simpl.SimpleThreadPool
threadCount: 10
threadPriority: 5
threadsInheritContextClassLoaderOfInitializingThread: true
#数据库方式
job-store-type: jdbc
#初始化表结构
jdbc:
initialize-schema: always
#数据库配置 根据实际修改ip 库 用户名 密码等
datasource:
url: jdbc:mysql://127.0.0.1:3306/quick?useSSL=false&useUnicode=true&&characterEncoding=utf-8
username: root
password: xxxxx
druid:
filters: stat
maxActive: 100
initialSize: 2
maxWait: 5000
minIdle: 10
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
maxOpenPreparedStatements: 20
removeAbandoned: true
removeAbandonedTimeout: 1800
logAbandoned: true
web-stat-filter:
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid3 * * ?
//每天0点0分执行一次
CronScheduleBuilder scheduleBuilder = CronScheduleBuilder.cronSchedule("0 0 0 * * ?");
return TriggerBuilder.newTrigger().forJob(runTemporaryRoleJobDetail())
.withIdentity("temporaryRoleTask")
.withSchedule(scheduleBuilder)
.build();
}
}
