浅谈MySql的event

「爱情、让人受尽委屈。」 2022-06-14 07:17 328阅读 0赞

从mysql5.1起，提供了event定时器，所以低于5.1的需升级版本。

1、查看mysql版本

select version() version;

结果:

![Center][]

2、查看mysql是否开启event功能（默认是关闭的）

show VARIABLES LIKE '%sche%';

结果:

![Center 1][]

3、开启event功能

SET GLOBAL event\_scheduler = 1;

或 `set global event_scheduler='on';`

再检查下是否成功开启

show VARIABLES LIKE '%sche%';

结果:

![Center 2][]说明开启成功

4、新建一张test表

![Center 3][]

drop table if exists test;  
create table test  
(  
id int(11) not null auto\_increment primary key,  
time datetime not null  
) engine=innodb default charset=utf8;

结果:

![Center 4][]

5、创建event要调用的存储过程

delimiter //  
drop procedure if exists test\_proce//  
create procedure test\_proce()  
begin  
insert into test(time) values(now());  
end//  
delimiter ;

![Center 5][]

6、查看是否创建成功

![Center 6][]

7、创建一个`事件test_event（其作用：每隔一秒自动调用test_proce()存储过程）`

`drop event if exists test_event; create event test_event on schedule every 1 second on completion preserve disable do call test_proce();`

8、查看是否成功创建

![Center 7][]

9、`查看自己创建的event`

`如果要查看更加详细的信息，你需要root用户的授权，如果是你自己的数据库你可以用下面语句查看`

`select * from  mysql.event;`

![Center 8][]

`为了方便查看，直接用php打印出来`

`<?php     mysql_connect('localhost','root','');     mysql_select_db('mysql');     mysql_query('set names utf8');     $result = mysql_query('select * from  event;');     while($row = mysql_fetch_assoc($result)){         echo "<pre>";         print_r($row);     }`

`结果:`

其实，mysql系统自带的mysql库里有一张event表，就可以查看了

![Center 9][]

10、`开启事件test_event（因为默认是未启用）`

`alter event test_event on completion preserve enable;`

`11、查看是否启用成功`

`说明启用成功`

`12、查看定时器是否正常工作？`

`发现test表每隔一秒，就插入了一条数据，说明定时器成功执行`

`13、关闭事件test_event alter event test_event on completion preserve disable;`

`14、event的时间设置（难点）`

`设置event很简单，但是麻烦的是如何设置执行的时间`

`设置完结束时间，到了结束时间，定时器就不执行了`

`此时的定时器的状态自动变为`

15、一些常用的时间设置

`EVERY 后面的是时间间隔，可以选 1 second，3 minute，5 hour，9 day，1 month，1 quarter（季度），1 year `

`从2013年1月13号0点开始，每天运行一次`

`ON SCHEDULE EVERY 1 DAY STARTS '2013-01-13 00:00:00'`

`从现在开始每隔九天定时执行`

`ON SCHEDULE EVERY 9 DAY STARTS NOW() ；`

`每个月的一号凌晨1 点执行`

`on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour);`

`每个季度一号的凌晨1点执行`

`on schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour);`

`每年1月1号凌晨1点执行`

`on schedule every 1 year starts date_add(date_add(date(concat(year(curdate()),'-',1,'-',1)),interval 1 year),interval 1 hour);`

PS:

DATE\_SUB() 函数从日期减去指定的时间间隔。

### 语法 ###

DATE_SUB(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

<table style="margin:15px 0px 0px; padding:0px; border:1px solid rgb(170,170,170); border-collapse:collapse; width:323px"> 
 <tbody style="margin:0px; padding:0px; border:0px"> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(63,63,63); vertical-align:baseline; background-color:rgb(63,63,63); text-align:left; color:rgb(255,255,255)"> Type 值</th> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> MICROSECOND</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> SECOND</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> MINUTE</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> HOUR</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> DAY</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> WEEK</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> MONTH</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> QUARTER</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> YEAR</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> SECOND_MICROSECOND</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> MINUTE_MICROSECOND</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> MINUTE_SECOND</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> HOUR_MICROSECOND</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> HOUR_SECOND</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> HOUR_MINUTE</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> DAY_MICROSECOND</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> DAY_SECOND</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> DAY_MINUTE</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> DAY_HOUR</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> YEAR_MONTH</td> 
  </tr> 
 </tbody> 
</table>

## 实例 ##

假设我们有如下的表：

<table style="margin:15px 0px 0px; padding:0px; border:1px solid rgb(170,170,170); border-collapse:collapse; width:809px"> 
 <tbody style="margin:0px; padding:0px; border:0px"> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(63,63,63); vertical-align:baseline; background-color:rgb(63,63,63); text-align:left; color:rgb(255,255,255)"> OrderId</th> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(63,63,63); vertical-align:baseline; background-color:rgb(63,63,63); text-align:left; color:rgb(255,255,255)"> ProductName</th> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(63,63,63); vertical-align:baseline; background-color:rgb(63,63,63); text-align:left; color:rgb(255,255,255)"> OrderDate</th> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> 1</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> 'Computer'</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> 2008-12-29 16:25:46.635</td> 
  </tr> 
 </tbody> 
</table>

现在，我们希望从 "OrderDate" 减去 2 天。

我们使用下面的 SELECT 语句：

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
    FROM Orders

结果：

<table style="margin:15px 0px 0px; padding:0px; border:1px solid rgb(170,170,170); border-collapse:collapse; width:809px"> 
 <tbody style="margin:0px; padding:0px; border:0px"> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(63,63,63); vertical-align:baseline; background-color:rgb(63,63,63); text-align:left; color:rgb(255,255,255)"> OrderId</th> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(63,63,63); vertical-align:baseline; background-color:rgb(63,63,63); text-align:left; color:rgb(255,255,255)"> OrderPayDate</th> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> 1</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> 2008-12-27 16:25:46.635</td> 
  </tr> 
 </tbody> 
</table>

DATE\_ADD() 函数向日期添加指定的时间间隔。

### 语法 ###

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

## 实例 ##

假设我们有如下的表：

现在，我们希望向 "OrderDate" 添加 2 天，这样就可以找到付款日期。

我们使用下面的 SELECT 语句：

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
    FROM Orders

结果：

<table style="margin:15px 0px 0px; padding:0px; border:1px solid rgb(170,170,170); border-collapse:collapse; width:809px"> 
 <tbody style="margin:0px; padding:0px; border:0px"> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(245,245,245)"> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(63,63,63); vertical-align:baseline; background-color:rgb(63,63,63); text-align:left; color:rgb(255,255,255)"> OrderId</th> 
   <th style="margin:0px; padding:5px 15px 5px 6px; border:1px solid rgb(63,63,63); vertical-align:baseline; background-color:rgb(63,63,63); text-align:left; color:rgb(255,255,255)"> OrderPayDate</th> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px; background-color:rgb(255,255,255)"> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> 1</td> 
   <td style="margin:0px; padding:6px 15px 6px 6px; border:1px solid rgb(170,170,170); vertical-align:text-top"> 2008-12-31 16:25:46.635</td> 
  </tr> 
 </tbody> 
</table>

ELT(N,str1,str2,str3,...)  
  
如果N =1返回str1，如果N= 2返回str2，等等。返回NULL如果参数的数量小于1或大于N。ELT()是FIELD()的补集。

[Center]: /images/20220614/8f05ae0e5f154d53b249cc56117930a6.png
[Center 1]: /images/20220614/38deda5ffce4416aa8d554b04ea0b9e6.png
[Center 2]: /images/20220614/7ddab626ff63423692e36536eab1925c.png
[Center 3]: /images/20220614/7288791b981b4a63bb2bc428bed399bb.png
[Center 4]: /images/20220614/422bbbcf45c5453d962130324340a107.png
[Center 5]: /images/20220614/7c8bddd6f93d4a10b691805f4e686881.png
[Center 6]: /images/20220614/9f4e00a5f0374707a1c14f5c5b2b6b3a.png
[Center 7]: /images/20220614/8817cf2e79ee4090a708d1572956884c.png
[Center 8]: /images/20220614/61ad32577d4443f5bc42b9b0aee29123.png
[Center 9]: /images/20220614/b3fd0741ba234c7bb6aa13f2e4a6597e.png