MySQL job/定时任务/event 学习笔记

我会带着你远行 2022-05-13 04:18 223阅读 0赞

由于周末做的数据迁移过程中，对迁移时间段内产生的数据及之后新产生的数据没有做到数据迁移，所以我写了个job让系统定时任务的跑到我的一张临时表，之后通过程序处理数据实现不同独立的数据库之间的数据同步。

程序代码就不做记录，可以用Java、python等都能实现，这次笔记主要记录MySQL event的使用；

MySQL的定时任务（简称job）是可以通过MySQL自身实现的，相比较系统的计划任务，MySQL自身job可以实现秒级定位，即可以精确到秒单位进行任务的执行（系统计划任务一般是分钟）；所以MySQL的job在要求精确化的任务上有极大的优势；MySQL 的job官方称为event，MySQL EVENT的原理和触发器非常的相似，区别在于，MySQL的event是系统定时驱动执行，而触发器是通过需求触发进行执行的，两者各有优势。

MySQL event和触发器一样可以调用存储过程，然而存储过程不能调用event，event只能系统执行，对于执行需求变更，我们只能手动的去调整event。

创建语法：

--------创建语法
    CREATE 
        [DEFINER = { user | CURRENT_USER }] --一般不写
        EVENT --event标识
        [IF NOT EXISTS] --如果没有就新建，建议不写这一行，确保每个event独立不冲突
        event_name -- 取个event名字
        ON SCHEDULE --计划任务标识
        schedule --计划任务时间，有两个参数（at 和 every），at代表当前一次，every表示每过多少时间执行一次
        [ON COMPLETION [NOT] PRESERVE] --可以不写，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ON COMPLETION  PRESERVE则不会drop
        [ENABLE | DISABLE | DISABLE ON SLAVE] --可以不写，默认状态，默认是启用状态，可以指定为新建关闭状态 
        [COMMENT 'comment'] --注释event，可以不写
        DO --执行语句标识
        event_body; --执行的sql
     
    
    
    --------------------------------------------计划任务可以定义的时间方式：
    schedule: 
        AT timestamp [+ INTERVAL interval] ... --at方式
      | EVERY interval                         --every方式
        [STARTS timestamp [+ INTERVAL interval] ...] --在at/every后可以指定什么时候开始
        [ENDS timestamp [+ INTERVAL interval] ...]  --指定什么时候结束
    
    -------------------------------------------MySQL官方提供的时间字符，可以参考   
    interval: 
        quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | 
                  WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | 
                  DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

本次我的语法是：

CREATE EVENT 
    ev_db_custinfo_sync 
    ON SCHEDULE 
    EVERY 1 DAY 
    ON COMPLETION PRESERVE 
    DO
    INSERT INTO db_cust_sync.t_dq_user ( CustID, DeviceNo ) SELECT
    s.cust_id,
    s.loginname 
    FROM
    	jf_custdb.t_cust_info s 
    WHERE
    	s.logintype = '18' 
    	AND s.create_time > DATE_SUB( CURDATE( ), INTERVAL 1 DAY ) 
    	AND NOT EXISTS ( SELECT 1 FROM db_cust_sync.t_dq_user l WHERE l.CustID = s.cust_id );

我定义每天跑一次，当然我偷懒了一下，理论上定义起始时间在晚上最好，然后由于压力不大，我就没有定义起始时间了，如果对于数据库优化，建议考虑写个起始时间尽量减少数据库对业务产生的压力。

在创建job之前，需要做以下几件事：

1、确保系统的event事件开关是打开的，允许我们创建event；

2、确保我们创建后是在成功运行的，所以要会查看当前系统有的event

3、确保我们创建是按照需求来的，如果需要更改，我们要会更改event

逐步记录：

1、查看系统开关：

-- 方法一
    select @@event_scheduler;
    
    --方法二
    show variables like 'event_scheduler';

以上语句都可以，随你选择，执行结果如下：

![70][]

如果value是off或者是0；代表没有开启，我们可以手动开启：

-- 开启event事件：
    -- 方法1
    set GLOBAL event_scheduler=ON;
    -- 方法2
    set GLOBAL event_scheduler=1;

当然关闭就是反过来，只有在确保event全局可用的情况下我们才能创建event；

2、创建event之前，看看系统存在哪些：

-- 查看系统有哪些event事件
    --方法一
    select * from mysql.event;
    
    --方法二
    SELECT * FROM information_schema.events;

创建过程开头已记录，在创建完后也可以通过语句核实创建的event，执行结果如下：

![70 1][]

3、调整，变更event：

--完整修改：
    ALTER EVENT event_name
    ON SCHEDULE schedule
    ON COMPLETION [NOT] PRESERVE
    RENAME TO new_event_name
    ENABLE | DISABLE
    DO
      event_body
    
    
    ----------------------------------------------单独修改一些数据：
    -- 修改时间
    ALTER EVENT ev_custinfo_sync ON SCHEDULE EVERY 1 DAY;
    
    
    --修改do执行语句
    ALTER EVENT ev_custinfo_sync
    do
    INSERT INTO db_cust_sync.t_dq_user ( CustID, DeviceNo ) SELECT
    s.cust_id,
    s.loginname 
    FROM
    	jf_custdb.t_cust_info s 
    
    --修改可用（启用/禁用）
    ALTER EVENT ev_custinfo_sync
    DISABLE;
    
    --重命名这个event：
    ALTER EVENT ev_custinfo_sync RENAME TO db_cust_sync_event
    
    --如果event建立的时候有指定库，想移动到其他库：
    ALTER EVENT cust_info_db.ev_custinfo_sync RENAME TO  cust_sync_db.ev_custinfo_sync
    
    -------------------------------------------------------------------------------
    --删除这个event
    DROP EVENT ev_custinfo_sync

以上所有修改参数都是要基于当前存在于该event的情况下，所以在修改前，最好先show一下；

趟过的坑：

1、默认创建的event是在当前位置所在的库，event可以指定所在库的，所以，骚年们，如果其他库要用，请指定下或者移动下。event需要指定一个库，不指定会创建报错的；

2、异常排查，如果在event测试中，发现测试数据没有存在或者执行，请单独跑一次do后面的执行体，有时候不是event出问题了，而是do后面的执行体sql本身就没有数据，所以event没有反应；

3、如果定义两个event在同一时间同一秒钟执行，会冲突，所以请至少间隔一秒；

4、如果只是执行select不做什么动作（导出，变更等操作），系统是不会保存数据的，所以只是select你会发现啥也没有，并不是event没有执行。

5、on completion参数最好写下，指定参数为preserve，这项参数会让event记录一直存在于，如果不带这项参数，默认是drop属性，意为着执行完后就drop掉，很多情况就像是没有创建一样。

[70]: /images/20220513/1020c400868e415c97ee0820d39a57a7.png
[70 1]: /images/20220513/28b0354dba6145a1b7335f61d601710f.png