MySQL创建定时任务并执行sql

CREATE EVENT database_clean
  ON SCHEDULE EVERY 1 HOUR
  DO
    BEGIN
      – INSERT INTO mytable (col1) VALUES (‘value’);
      DELETE FROM so_api_inst WHERE CREATE_TIME < NOW() - INTERVAL 7 DAY;
    END

MySQL查询所有event的具体细节

方法一：

show events;

方法二：

SELECT
    EVENT_SCHEMA as ‘数据库’,
    EVENT_NAME as ‘事件名’,
    DEFINER,
    EVENT_TYPE as ‘事件类型’,
    EXECUTE_AT as ‘执行时间’,
    INTERVAL_VALUE as ‘间隔值’,
    INTERVAL_FIELD as ‘间隔字段’,
    LAST_EXECUTED as ‘上次执行时间’,
    STARTS as ‘开始时间’,
    ENDS as ‘结束时间’,
    STATUS as ‘状态’,
    EVENT_DEFINITION as ‘事件定义’
FROM
    information_schema.EVENTS;

删除event

DROP EVENT IF EXISTS database_clean;

查看事件定义

SHOW CREATE EVENT event_name;

修改事件

ALTER EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_name]
[ENABLE | DISABLE]
[DO sql_statement];

修改event里面的sql

ALTER EVENT event_name
DO
BEGIN
  -- 新的SQL语句
  UPDATE your_table SET column_name = value WHERE condition;
END;

修改时间

ALTER EVENT event_name
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR;