MySql定时任务

在项目开发中需要定时从其他数据库中捞取数据，这时可以使用MySQL的定时任务完成。

定时任务共需要三个部分：

1.数据表：记录捞取的时间，每次捞取时作为查询条件以防止重复捞取，每次捞取数据后获得当前时间更新该数据表

2.函数：捞取数据并存档当前数据库的存储过程

3.时间：时间里实现定时执行函数中的存储过程

数据表结构：

sysName	updateTime
DH	2020-03-24 08:07:19

 

 

函数：

CREATE DEFINER=`usmadmin`@`%` PROCEDURE `Pro_syn_c_dprecord`(OUT out_status CHAR(5))
    COMMENT 'Violation records are synchronized from DSS to USM'
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE last_syn_datetime datetime;
    DECLARE next_syn_datetime datetime;
    DECLARE usm_reu_id VARCHAR(40) DEFAULT '0' ;-- 大保卫的的违章ID
    DECLARE vid INT DEFAULT 0 ;-- 违章ID
    DECLARE vcar_num varchar(12);-- 车牌号
    DECLARE vcar_speed int;-- 车速
    DECLARE vcar_type int ;-- 车辆类型
    DECLARE usm_car_type varchar(2) DEFAULT 'F' ;-- 大保卫中车辆类型   H货车 K客车 T特种车辆 F非机动车 Q其他机动车
    DECLARE vcar_color int ;-- 车辆颜色
    DECLARE vcap_date datetime;-- 违章时间
     DECLARE vrec_type int ;-- 违章类型
    DECLARE vdev_name varchar(50) ;-- 抓拍地点
  DECLARE usm_typeVio VARCHAR(2) DEFAULT '32' ;-- 大保卫违章类型 32：闯红灯   10：超速
    DECLARE vcombined_pic_url varchar(255); -- 总违章图片url
    DECLARE vcar_img_url varchar(255);-- 违章图片1
    DECLARE vcar_img1_url varchar(255);-- 违章图片2
    DECLARE dprecord_data CURSOR
    FOR
    SELECT   ID, CAR_NUM, CAR_SPEED, CAR_TYPE, CAR_COLOR, CAP_DATE, REC_TYPE, DEV_NAME, COMBINED_PIC_URL,               CAR_IMG_URL, CAR_IMG1_URL
    FROM  c_dprecord_link_dh 
     WHERE  CAP_DATE > last_syn_datetime;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 没有下一条数据 修改标记为1
    BEGIN
    SELECT t.updateTime INTO last_syn_datetime FROM syn_datetime t WHERE t.systemName= 'DH';
    SET next_syn_datetime= SYSDATE();
    END;
    OPEN dprecord_data;
    FETCH NEXT FROM dprecord_data INTO vid, vcar_num, vcar_speed, vcar_type, vcar_color, vcap_date, vrec_type, vdev_name, vcombined_pic_url, vcar_img_url, vcar_img1_url;
    WHILE (done<>1) DO
    SET usm_reu_id = REPLACE(UUID(),"-","");
    IF vcar_type=1 THEN 
    SET usm_car_type = 'K';
    END IF;
    IF vcar_type=2 THEN 
    SET usm_car_type = 'H';
    END IF;
    IF vrec_type=1 THEN 
    SET usm_typeVio = '10';
    ELSE
    SET usm_typeVio = '32';
    END IF; 
     BEGIN
      INSERT into vehicle_regulation 
        (
        id,
        recordSource,
        carType,
        carNumber,
        datetimeVio,
        siteVio,
        typeVio,
        processState,
        recieveDatetime,
        isNeedfine,
        isInFactry,
        sourceId,
        intIsActive,
        intIsDelete,
        createUserId,
        createDateTime,
        updateUserId,
        updateDateTime,
        companyId
        )
        VALUES
        (
        usm_reu_id,
        '1',
        usm_car_type,
        vcar_num,
        vcap_date,
        vdev_name,
        usm_typeVio,
        '1',
        SYSDATE(),
        'Y',
        '1',
        vid,
        '1',
        '0',
        '000000',
        SYSDATE(),
        '000000',
        SYSDATE(),
        '1'
        ); 
     END;
    BEGIN
  INSERT INTO image_regulation
    (
        imageUrl,
        regulationid,
        iscombined,
        intIsActive,
        intIsDelete,
        createUserId,
        createDateTime,
        updateUserId,
        updateDateTime,
        companyId
    )
    VALUES
    (
        CONCAT('http://172.18.**.***:8081',substring(vcombined_pic_url, 5)),
        usm_reu_id,
        '1',
        '1',
        '0',
        '000000',
        SYSDATE(),
        '000000',
        SYSDATE(),
        '1'
    );
    END;
    IF(vcar_img_url<>'0') THEN
    BEGIN
    INSERT INTO image_regulation
    (
        imageUrl,
        regulationid,
        iscombined,
        intIsActive,
        intIsDelete,
        createUserId,
        createDateTime,
        updateUserId,
        updateDateTime,
        companyId
    )
    VALUES
    (
      CONCAT('http://172.18.**.***:8081',substring(vcar_img_url, 5)),
        usm_reu_id,
        '0',
        '1',
        '0',
        '000000',
        SYSDATE(),
        '000000',
        SYSDATE(),
        '1'
    );
    END;
    END IF;
    IF(vcar_img1_url<>'0') THEN
    BEGIN
    INSERT INTO image_regulation
    (
        imageUrl,
        regulationid,
        iscombined,
        intIsActive,
        intIsDelete,
        createUserId,
        createDateTime,
        updateUserId,
        updateDateTime,
        companyId
    )
    VALUES
    (
        CONCAT('http://172.18.**.***:8081',substring(vcar_img1_url, 5)),
        usm_reu_id,
        '0',
        '1',
        '0',
        '000000',
        SYSDATE(),
        '000000',
        SYSDATE(),
        '1'
    );
    END;
    END IF;
     BEGIN
     UPDATE syn_datetime SET updateTime = next_syn_datetime WHERE systemName='DH';
     END;
    FETCH NEXT FROM dprecord_data INTO vid, vcar_num, vcar_speed, vcar_type, vcar_color, vcap_date, vrec_type, vdev_name, vcombined_pic_url, vcar_img_url, vcar_img1_url;
  END WHILE;
    CLOSE dprecord_data;
    set out_status='OK';
END

事件（选择计划，每30分钟执行一次）：

CALL Pro_syn_c_dprecord(@out_status)