How to create a daily event in Mysql 5.6?

Question

I need Mysql to remove all data from a table always at midnight.

Answer 1

You can create a EVENT!

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Example:

SET GLOBAL event_scheduler = ON;

DELIMITER $
CREATE EVENT eraseData 
    ON SCHEDULE EVERY 1 DAY
    STARTS '2014-11-22 23:59:59' -- precisa ser no futuro
    DO BEGIN 
        DELETE FROM db.tabela; -- apaga os dados
    END
$ DELIMITER ;