sql任务重试方案

定时执行相应的sql任务，是工作中常遇到的场景。通常用在统计相关的任务，这些sql语句中，通常都含有相对的时间函数，如now()等, 如:

select count(*) from tb_cinfo where create_time>=UNIX_TIMESTAMP(DATE_FORMAT(date_sub(now(), interval 1 HOUR), '%Y-%m-%d %H')) and create_time<UNIX_TIMESTAMP(DATE_FORMAT(now(), '%Y-%m-%d %H')) 

上面这个sql语句的的结果为 上一个小时内新增的数据量，但如该sql语句18点执行失败，19点进行手工重试时，就会有问题了，因为18点执行时统计的是17点的数据，而若19点进行重试，重新执行该语句，则统计的是18点的数据。

方案

自定义业务场景常用的时间函数，然后对伪sql解析为具体的可执行sql, 并将该可执行sql记录下来，之后重试则执行该记录下来的sql。

举例说明:

{@date}: 表示当前日期，e.g. 20200831, ，支持加减，如前一天：{@date-1} => 20200830，后一天：{@date+1} => 20200901

{@date_timestamp}: 表示当前时间戳, e.g. 1598864224, 支持加减，如前10分钟：{@date_timestamp-600}，后1个小时：{@date_timestamp+3600}

{@date_minute_timestamp}: 表示当前分钟时间戳, 如2020-08-31 16:50:55按照2020-08-31 16:50:00，转换为1598863800, 同样支持加减，如前一分钟: {@date_minute_timestamp-1}，后一分钟: {@date_minute_timestamp+1}

{@date_hour_timestamp}: 表示当前小时时间戳,同样支持加减，如前一小时: {@date_hour_timestamp-1}，后一小时: {@date_hour_timestamp+1}

ps: 此处仅部分示例，可根据实际业务场景，自定义需要的时间函数

则之前的sql, 就可以改写为:

select count(*) from tb_cinfo where create_time>={@date_hour_timestamp-1} and create_time<{@date_hour_timestamp}

对该sql进行解析, 得到解析后的sql:

select count(*) from audit_cinfo where create_time>=1598860800 and create_time<1598864400

此时，若该sql执行失败，无论什么时间，都可以直接重试即可，因为该sql语句并不包含时间函数，而是具体的时间数值。

附sql解析语句示例(php版本):

/**
     * sql解析
     *
     * @param $sql
     * @return mixed
     */
    public function parseQuery($sql) {
        $sql = str_replace("\n", ' ', trim($sql, ';'));
        $sql =  preg_replace("/\s(?=\s)/", "\\1", trim($sql));
        preg_match_all('/\{.*?\}/', $sql, $matches);
        $matches = $matches[0];
        $replace = [];
        foreach ($matches as $val) {
            $val = str_replace(' ', '', strtolower($val));
            preg_match('/@\w+[+|\-|\}]/', $val, $match1);
            preg_match('/[+|\-]\d+\}/', $val, $match2);
            $match1 = empty($match1[0]) ? '' : substr($match1[0], 1, -1);
            $func = empty($match2[0]) ? '' : substr($match2[0], 0, 1);
            $match2 = empty($match2[0]) ? '' : substr($match2[0], 1, -1);

            $currTime = time();
            $res = $match1;
            switch ($match1) {
                case 'date':
                    $res = $this->calculation(strtotime(date("Y-m-d 00:00:00", $currTime)), $func, $match2, 86400);
                    $res = date("Ymd", $res);
                    break;
                case 'date_timestamp':
                    $res = $this->calculation($currTime, $func, $match2, 1);
                    break;
                case 'date_minute_timestamp':
                    $res = $this->calculation(strtotime(date("Y-m-d H:i:00", $currTime)), $func, $match2, 60);
                    $res = strtotime(date("Y-m-d H:i:00", $res));
                    break;
                case 'date_hour_timestamp':
                    $res = $this->calculation(strtotime(date("Y-m-d H:00:00", $currTime)), $func, $match2, 3600);
                    $res = strtotime(date("Y-m-d H:00:00", $res));
                    break;
                default:
                    break;
            }
            $replace[] = $res;
        }

        return str_replace($matches, $replace, $sql);
    }

    private function calculation($base, $func, $param, $step = 1) {
        $res = $base;
        if (empty($func) || empty($param) || empty($step)) {
            return $res;
        }
        switch ($func) {
            case '+':
                $res = $base + $param * $step;
                break;
            case '-':
                $res = $base - $param * $step;
                break;
            default:
                break;
        }
        return $res;
    }