基于mysql实现的定时任务

SELECT `id`,`crontab_task_id`,`today_exec_times`,`state`,`is_deleted` FROM %s WHERE `years` IN (0,%s) AND `months` IN (0,%s) AND `days` IN (0,%s) AND `weekdays` IN (-1,%s) AND  (%d >= (`hours`*3600+`minutes`*60+`seconds`)) AND ((`hours`*3600+`minutes`*60+`seconds`+`second_range`) > %d) AND (`must_exec_times` > `today_exec_times`) AND `run_status` IN (0,3,4) UNION SELECT `id`,`crontab_task_id`,`today_exec_times`,`state`,`is_deleted` FROM %s WHERE `years` IN (0,%s) AND `months` IN (0,%s) AND `days` IN (0,%s) AND `weekdays` IN (-1,%s) AND `hours` IN (-1,%s) AND `minutes` IN (-1,%s) AND `seconds` IN (-1,%s)   AND (`must_exec_times` > `today_exec_times`) AND `run_status` IN (0,3,4)

func checkScheme(nowTime time.Time) {
    if nowTime.Hour() == 0 && nowTime.Minute() == 0 && nowTime.Second() == 0 {
        log.Println("清空昨天的数据")
        initYesterdayData()
        return
    }
    defer func() {
        if e := recover(); e != nil {
            log.Println("Panicing %s\r\n", e)
        }
    }()
    year := nowTime.Format("2006")
    month := nowTime.Format("1")
    day := nowTime.Format("2")
    hours := nowTime.Format("15")
    minute := nowTime.Format("4")
    second := nowTime.Format("5")
    weekday := nowTime.Weekday().String()
    weekDayMap := map[string]string{
        "Sunday":    "0",
        "Monday":    "1",
        "Tuesday":   "2",
        "Wednesday": "3",
        "Thursday":  "4",
        "Friday":    "5",
        "Saturday":  "6",
    }

    currentTimeIndex := nowTime.Hour()*3600 + nowTime.Minute()*60 + nowTime.Second()
    rangeSql := "SELECT `id`,`crontab_task_id`,`today_exec_times`,`state`,`is_deleted` FROM %s WHERE `years` IN (0,%s) AND `months` IN (0,%s) AND `days` IN (0,%s) AND `weekdays` IN (-1,%s) AND  (%d >= (`hours`*3600+`minutes`*60+`seconds`)) AND ((`hours`*3600+`minutes`*60+`seconds`+`second_range`) > %d) AND (`must_exec_times` > `today_exec_times`) AND `run_status` IN (0,3,4)"
    rangeSql = fmt.Sprintf(rangeSql, CrontabScheme, year, month, day, weekDayMap[weekday], currentTimeIndex, currentTimeIndex)

    exactSql := "SELECT `id`,`crontab_task_id`,`today_exec_times`,`state`,`is_deleted` FROM %s WHERE `years` IN (0,%s) AND `months` IN (0,%s) AND `days` IN (0,%s) AND `weekdays` IN (-1,%s) AND `hours` IN (-1,%s) AND `minutes` IN (-1,%s) AND `seconds` IN (-1,%s)   AND (`must_exec_times` > `today_exec_times`) AND `run_status` IN (0,3,4)"
    exactSql = fmt.Sprintf(exactSql, CrontabScheme, year, month, day, weekDayMap[weekday], hours, minute, second)

    results, err := ReadOrm.QueryString(rangeSql + " UNION " + exactSql)
    if err != nil {
        panic(err)
    }
    for _, v := range results {
        //非启用状态不处理
        if v["state"] != "1" {
            continue
        }
        //删除状态不处理
        if v["is_deleted"] == "1" {
            continue
        }
                //修改状态
        affectRow, err := WriteOrm.Table(CrontabScheme).Where("id = ?", v["id"]).Update(struct{ RunStatus int }{RunStatus: systemDao.CRONTAB_SCHEME_PUSHKAFKASTATUS})
        if err != nil {
            continue
        }
        if affectRow == 0 {
            continue
        }
        schemeTask := map[string]string{
            "schemeId":       v["id"],
            "taskId":         v["crontab_task_id"],
            "todayExecTimes": v["today_exec_times"],
        }
        //入列todo

    }

}