mysql定时任务 分钟_MYSQL 定时任务

MySQL5.1.6起增加了事件调度器(Event Scheduler)，可用来做定时执行某些特定任务，用于取代原先只能由操作系统的计划任务来执行的工作。MySQL的事件调度器可以精确到每秒执行一个任务，而操作系统的计划任务只能精确到分钟级别。对于对数据实时性要求比较高的应用非常合适。

事件调度器也称为临时触发器(Temporal Triggers)，因为事件调度器是基于特定时间周期触发来执行某些任务，而触发器(Triggers)是基于某个表所产生的事件触发的。

MySQL定时任务的实现方式有两种：

使用MySQL的event定时任务

使用MySQL的事件计划，首先需要在服务器开启event_scheduler后才能处理。

使用Linux的定时任务crontab

如何开启事件计划呢？

$ SHOW VARIABLES LIKE 'event_scheduler';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| event_scheduler | OFF |

+-----------------+-------+

1 row in set

如果执行命令后返回值为OFF则表示目前事件计划是处于关闭的状态。

开启的方式也分为两种，临时方式使用命令行或脚本操作，永久修改则需要修改MySQL主配置文件my.ini在其中添加event_schduler=1的配置后重启MySQL。

临时性修改只要不重启MySQL在当前运行状态下会直接生效，一旦重启后则失效。

$ SET GLOBAL event_scheduler = ON;

$ SET @@global.event_scheduler = ON;

$ SET GLOBAL event_scheduler = 1;

$ SET @@global.event_scheduler = 1;

事件调度器

要保证能够执行事件，就必须保证事件计划是开启状态，事件计划默认为关闭状态。

# 查看MySQL版本

$ SELECT VERSION();

+------------+

| VERSION() |

+------------+

| 5.7.18-log |

+------------+

1 row in set

# 事件计划是否开启

$ SHOW VARIABLES LIKE 'event%'

$ SHOW VARIABLES LIKE 'event_scheduler';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| event_scheduler | ON |

+-----------------+-------+

1 row in set

# 查看事件任务是否开启

$ SELECT @@event_scheduler;

+-------------------+

| @@event_scheduler |

+-------------------+

| ON |

+-------------------+

1 row in set

# 开启事件计划

$ SET GLOBAL event_scheduler=1

$ SET GLOBAL event_scheduler=ON

duler=1;

Query OK, 0 rows affected

# 关闭事件计划

$ SET GLOBAL event_scheduler=0

在真实开发环境中会遇到MySQL服务重启或断电的情况，此时会出现事件调度器被关闭的情况。所有事件都不再起作用，解决的方式需要在MySQL的配置文件mysql.ini中加入event_scheduler=ON的配置。

事件任务

事件任务

# 查看事件任务

$ SHOW EVENTS;

Empty set

# 查看事件任务错误 - 权限不足

$ SELECT * FROM mysql.event

1142 - SELECT command denied to user 'username'@'127.0.0.1' for table 'event'

# 开启事件任务

$ ALTER EVENT event_name ON COMPLETION PRESERVE ENABLE

# 关闭事件任务

$ ALTER EVENT event_name ON COMPLETION PRESERVE DISABLE

# 删除事件

$ DROP EVENT [IF EXISTS] event_name

设置定时任务执行SQL语句

例如：从当日开始每天凌晨4点删除fight表超过一个月的数据

计划任务

DROP EVENT IF EXISTS event_fight_delete;

CREATE EVENT event_fight_delete

ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE(CURDATE() + 1), INTERVAL 4 HOUR)

DO

BEGIN

DELETE FROM center_fight WHERE 1=1 AND createdate < DATE_ADD(CURDATE(), INTERVAL -1 MONTH)

END

设置定时任务调用存储过程

# 若计划任务存在则删除

DROP EVENT IF EXISTS event_name

# 创建计划任务

CREATE EVENT event_name

ON SCHEDULE EVERY 10 second

STARTS TIMESTAMP '2018-07-12 00:00:00'

ON COMPLETION PRESERVE

DO

BEGIN

CALL producer()

END

参数说明

ON SCHDULE schduler 定义执行的时间和时间间隔

ON COMPLETION [NOT] PRESERVE 定义事件是一次性执行还是永久执行，默认为一次性执行，即NOT PRESERVE。

在事件中ON SCHEDULE计划任务中有2种设定的方式

用来完成单次计划任务。

AT 时间戳

eg：5天后

AT CURRENT_TIMESTAMP + INTERVAL 5 DAY

eg：某时间点

AT TIMESTAMP '2018-07-12 12:00:00'

用来完成重复的计划任务

EVERY 时间(单位)的数量 时间单位 [STARTS 时间戳] [ENDS时间戳]

eg：每隔1秒

EVERY 1 SECOND

eg：每隔10分钟。

EVERY 10 MINUTE

eg：从2018-08-01 12:00:00开始每隔1天

EVERY 1 DAY STARTS '2018-08-01 12:00:00'

EVERY 10 second STARTS TIMESTAMP '2018-08-01 12:00:00'

eg：5天后开启每天定时处理

EVERY 1 DAY START CURRENT_TIMESTAMP + INTERVAL 5 DAY

eg：每天定时处理5天后停止

EVERY 1 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY

在两种计划任务中，时间戳可以是任意的TIMESTAMP和DATETIME数据类型，时间戳需要大于当前时间。

在重复的计划任务中，时间(单位)的数量可以是任意非空(NOT NULL)的整数形式，时间单位是关键词：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND...

[ON COMPLETION [NOT] PRESERVE]

ON COMPLETION参数表示“当这个事件不会再发生的时候”，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被DROP掉，建议使用该参数，以便于查看EVENT具体信息。

CREATE DEFINER=`root`@`localhost` EVENT `event_knapsacks_remember_expire`

ON SCHEDULE EVERY 1 MINUTE STARTS '2018-07-13 15:09:49'

ON COMPLETION PRESERVE ENABLE

COMMENT '每分钟检测背包中换牌卡到期并每日自动减少'

DO

BEGIN

CALL produce_knapsacks_remember_expire();

END

存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS procedure_name

CREATE PROCEDURE procedure_name()

BEGIN

INSERT INTO procedure_name(name, create_time) VALUES('name_value', now())

END $$

DELIMITER ;

-- 存储过程 produce_knapsacks_remember_expire

-- 作用：判断背包中道具记牌卡，是否过期，且每日减一。

CREATE DEFINER=`root`@`localhost` PROCEDURE `produce_knapsacks_remember_expire`()

BEGIN

DECLARE pk INT DEFAULT 0;

DECLARE sec INT DEFAULT 0;

DECLARE days INT DEFAULT 0;

DECLARE expire INT DEFAULT 0;

DECLARE mc CURSOR FOR (SELECT id,TIMESTAMPDIFF(SECOND,effect_time,NOW()) AS diff,TIMESTAMPDIFF(SECOND,NOW(),expire_time) AS expire FROM knapsacks WHERE name='REMEBER' AND expired=0);

OPEN mc;

ml:LOOP

FETCH mc INTO pk,sec,expire;

IF(expire <= 0) THEN

UPDATE `knapsacks` SET `expired`=1 WHERE `id`=pk;

ELSE

IF(sec>0 && sec<=86400) THEN

SET days = 1;

ELSEIF(sec>86400) THEN

SET days=CEILING(sec/86400);

END IF;

UPDATE `knapsacks` SET `quantity`=`purchase`-days,`consume`=days WHERE `id`=pk;

END IF;

COMMIT;

END LOOP ml;

CLOSE mc;

END

错误处理

出现错误

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方案

$ SELECT VERSION();

$ @@sql_mode;

$ SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY' , ''));

出现提示

ERROR

# 在SQL中查询计划事件的状态

$ SHOW VARIABLES LIKE 'event_scheduler'

# 在mysql程序的目录下找到my.ini文件添加

$ vim my.ini

event_scheduler = 1

# 保存后重启mysql服务

# 用脚本来实现

# 开启event_scheduler sql指令：

SET GLOBAL event_scheduler = ON;

SET @@global.event_scheduler = ON;

SET GLOBAL event_scheduler = 1;

SET @@global.event_scheduler = 1;

$ 关闭event_scheduler指令：

SET GLOBAL event_scheduler = OFF;

SET @@global.event_scheduler = OFF;

SET GLOBAL event_scheduler = 0;

SET @@global.event_scheduler = 0;

2人点赞

作者：JunChow520

链接：https://www.jianshu.com/p/917...

来源：简书

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。