今天有個(gè)業(yè)務(wù)需求,每天要重置流水號(hào).想起oracle有job 于是聯(lián)想到Mysql應(yīng)該有類似的.發(fā)現(xiàn)mysql

通過EVENT 來實(shí)現(xiàn)

語法如下

    CREATE EVENT [IF NOT EXISTS] event_name  
      
        ON SCHEDULE schedule  
      
        [ON COMPLETION [NOT] PRESERVE]  
      
        [ENABLE | DISABLE]  
      
        [COMMENT 'comment']  
      
        DO sql_statement;  
      
    schedule:  
      
        AT TIMESTAMP [+ INTERVAL INTERVAL]  
      
    | EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]  
      
    INTERVAL:  
      
        quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |  
      
                  WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |  
      
                  DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}  

簡(jiǎn)單使用如下

    DELIMITER $$  
      /**  
         * 重置流水號(hào)  
         *   
         * @author xuyw  
         * @email xyw10000@163.com  
         * @date 2014-05-06  
         */  
    -- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create      
      
    CREATE  /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `xxx`.`reset_serialNumber`  
      
    ON SCHEDULE EVERY 1 DAY STARTS '2014-05-06 23:59:59'  
         /* uncomment the example below you want to use */  
      
        -- scheduleexample 1: run once  
      
           --  AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }  
      
        -- scheduleexample 2: run at intervals forever after creation  
      
           -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]  
      
        -- scheduleexample 3: specified start time, end time and interval for execution  
           /*EVERY 1  [HOUR|MONTH|WEEK|DAY|MINUTE|...]  
      
           STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }  
      
           ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */  
      
    /*[ON COMPLETION [NOT] PRESERVE]  
    [ENABLE | DISABLE]  
    [COMMENT 'comment']*/  
      
    DO  
        BEGIN  
            UPDATE xxx_sequence  
                       SET current_value = 0  
                       WHERE id = 1;  
        END$$  
      
    DELIMITER ;  

標(biāo)簽： Mysql

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。