mysql 定时器

mysql定时器的使用场景

定时任务:

• ​ 如定时清除日志表三个月之前的数据
• ​ 凌晨1:00执行一次 日志表数据的删除

使用前的准备和注意事项

1.时间的问题

服务器Apache(PHP)时间和mysql的时间默认时区是跟我们中国的时区是不一样的，所以为了确保时间准确。时区这个玩意儿一定要设置

1.1Apache(PHP) 服务器时区的设置：

​ 找到php.ini 将 ";date.timezone =" 前面的分号去掉=号后面加上时区设置，然后重启apache服务既可。下面列出几个常用的时区

• ​ Asia/Chongqing (重庆)

• ​ Asia/Shanghai (上海)

• ​ Asia/Urumqi(乌鲁木齐)

• ​ Asia/Macao (澳门)

• ​ Asia/Hong_Kong (香港)

• ​ Asia/Taipei (台北)

例：

​ date.timezone = Asia/Shanghai

​ 上面这种是永久性修改，你也可以临时性修改具体可以自行百度

1.2mysql时区的修改

​ 方式有两种，推荐第二种(永久修改)

第一种：临时性修改

###第一种
select NOW();
##一：通过sql命令临时修改
set global time_zone = '+8:00';
set time_zone = '+8:00';
## 刷新内存中的时区，让修改立即生效，也可以通过重启服务达到
flush privileges;
select NOW();

​ 第二种:永久性修改

###二：修改my.cnf实现永久修改
# vi /etc/mysql/my.cnf
# 然后在mysqld下边的配置(Basic Settings)中添加一行：
default-time_zone = '+8:00'
#然后重启mysql

​ 重启完成后，可以再次使用sql 语句“select NOW();”来确认是否修改成功

2.检查mysql是否开启了定时器功能

2.1 查询定时器是否开启

#查看是否开启了event定时器功能
SHOW VARIABLES LIKE 'event_scheduler';

可以看到定时的功能是没有开启的，在这种情况下就需要开启定时器功能

2.2开启mysql 定时器

开启mysql的定时器其实很简单，其实就是一句sql

#开启mysql定时器
set GLOBAL event_scheduler = 1;

最后检查状态如下图所示

3.msyql定时器重启服务器或重启mysql 定时器失效的问题

​ 虽然上面用set global event_scheduler = on语句开启了事件，但是每次重启电脑或重启mysql服务后，会发现，事件自动关闭（event_scheduler=OFF），所以想让事件一直保持开启，最好修改配置文件，让mysql服务启动的时候开启事件，只需要在my.ini配置文件（window10 是my.ini ，linux上是mysqld.cnf）的[mysqld]部分加上event_scheduler=ON 即可

window 上修改的方法

找到mysql 的my.ini配置文件，在mysqld 部分加上event_scheduler=ON

ubuntu 16.04 上修改的方法

找到mysql的配置文件

vi /etc/mysql/mysql.conf.d/mysqld.cnf

在mysqld 部分加上event_scheduler=ON

修改完成后，可以重启服务或重启服务器并通过2.1小节的方法来检查是否设置成功

mysql定时器的写法

​ 格式：

​ 参考https://dev.mysql.com/doc/refman/5.6/en/create-event.html

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    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}

上面这是mysql 官方文档的格式介绍

其实，用伪sql 表示格式大概就是这样的

create 
	[DEFINER = 用户] # 不是所有用户都有权限创建定时器的，在这里指定用户，当然也可以不指定，这个选项是可选的
	event  #事件关键字
	
	[IF NOT EXISTS] #IF NOT EXISTS的含义CREATE EVENT与 CREATE TABLE：具有相同的含义 ：如果event_name已经存在一个名为的事件 ，则不执行任何操作，也不会产生错误。（但是，在这种情况下会生成警告。） 可选
	
	event_name #事件名称
	
	ON SCHEDULE #设置定时的模式，有一次性(AT timestamp)的和持续性(EVERY interva)两种，这个选项为必须设置的
		schedule # 两种模式
			#AT timestamp 执行一次,可以使用DATETIME或 TIMESTAMP类型的值(这两种类型的值自行百度),例：AT '2019-12-23 15:30:00' 在这个时间执行一次操作
			
			#EVERY interva 重复执行
				interval #执行频率
				STARTS timestamp #开始时间
				ENDS timestamp #结束时间
				#例：EVERY 1 MINUTE STARTS '2019-12-23 15:30:00' ENDS '2019-12-23 16:00:00' 
				#从2019-12-23 15:30:00开始到2019-12-23 16:00:00 每分钟执行一次
				#也可以不写结束时间，那就是从什么时候开始一直执行

	[ON COMPLETION [NOT] PRESERVE] #事件执行之后是否会被drop掉设置
		#ON COMPLETION PRESERVE 当这个事件不会再发生的时候不会被Drop掉
		#ON COMPLETION NOT PRESERVE 当这个事件不会再发生的时候会被Drop掉
				
	[ENABLE | DISABLE | DISABLE ON SLAVE] #设置这个事件是否是活动状态
    	#ENABLE 创建完后就是已经开启的状态
        #DISABLE 创建完后是关闭的状态
        #DISABLE ON SLAVE 复制到从属服务器上的事件状态设置，指该事件已在主服务器上创建并复制到从属服务器，但未在从属服务器上执行(一般用不上，只有设置了mysql主从数据库才会用的上)
        
    [COMMENT 'string'] # 在这里写这个定时器是用来的干嘛的，相当于描述
    
    DO event_body;# DO 后面写你需要执行的sql
    	# 注意 DO 后面有两种写法
    	# 一种是直接写sql，这种只能在DO后面写一种sql
    	#另一种是 DO 后面再接BEGIN (sql1,sql2) END

下面我们创建一张用来测试我们的定时器的表

CREATE TABLE `test` (
	`id`  int NOT NULL ,
	`test`  varchar(255) NULL ,
	PRIMARY KEY (`id`)
)
;

例：

1.一次性定时器，将当前时间以字符串的形式插入到test表

DROP EVENT IF EXISTS `event_at`; #检查这个定时器是否存在，如果存在就删除
DELIMITER ;; # 修改sql语句结束符
#以root@localhost这个用户的身份创建一次性定时器，执行时间为2019-12-24 09:09:00
CREATE DEFINER=`root`@`localhost` EVENT `event_at` ON SCHEDULE AT '2019-12-24 09:09:00' 
ON COMPLETION NOT PRESERVE #当这个事件不会再发生的时候会被Drop掉
ENABLE # 创建时为开启状态
DO 
    INSERT INTO test(test) VALUES(date_format(now(), '%Y-%m-%d %H:%i:%s'));
;;
DELIMITER ;

过了执行时间之后，我们通过sql语句查询可以看到test表已经插入一条数据，如下结果：

2.重复定时器，将当前时间以字符串的形式插入到test表

DROP EVENT IF EXISTS `event_minute`; #检查这个定时器是否存在，如果存在就删除
DELIMITER ;; # 修改sql语句结束符
#以root@localhost这个用户的身份创建重复性定时器，执行时间为2019-12-24 09:30:00 到 2019-12-24 09:35:30 ，执行频率为每分钟一次，执行结束后应该多6条数据，结束时间也可以不写，那就是从开始时间一直执行
CREATE DEFINER=`root`@`localhost` EVENT `event_minute` ON SCHEDULE EVERY 1 MINUTE STARTS '2019-12-24 09:30:00' ENDS '2019-12-24 09:35:30'
ON COMPLETION PRESERVE #当这个事件不会再发生的时候不会被Drop掉
ENABLE # 创建时为开启状态
DO 

BEGIN
    #这种格式可以写多条sql
    INSERT INTO test(test) VALUES(date_format(now(), '%Y-%m-%d %H:%i:%s'));
END
;;
DELIMITER ;

过了执行时间之后，我们通过sql语句查询可以看到test表已经插入6条数据，如下结果：

3.重复定时器，删除日志表除了这个月的日志数据，只保留当月的数据

表结构如下：

sql：

DROP EVENT IF EXISTS `event_day`; #检查这个定时器是否存在，如果存在就删除
DELIMITER ;; # 修改sql语句结束符
#以root@localhost这个用户的身份创建重复性定时器，从2019-12-24 11:50:00开始每周执行一次
CREATE DEFINER=`root`@`localhost` EVENT `event_day` ON SCHEDULE EVERY 1 DAY STARTS '2019-12-24 11:50:00'
ON COMPLETION PRESERVE #当这个事件不会再发生的时候不会被Drop掉
ENABLE # 创建时为开启状态
DO 

BEGIN
    #这种格式可以写多条sql
    delete from logs where `datetime` < str_to_date(concat(DATE_FORMAT(curdate(), '%Y-%m'), '-01'), '%Y-%m-%d') or `datetime` > last_day(curdate());
END
;;
DELIMITER ;

其他比较重要的sql

#关闭打开定时器  ALTER EVENT 定时器名称 {DISABLE|ENABLE};
ALTER EVENT event_minute DISABLE; 
ALTER EVENT event_minute ENABLE;
#查询定时器状态
select * from mysql.event ;