MySQL定时器十例演示

MySQL定时器十例演示

查看MySQL的版本号：

登录后复制

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.39-log |
+------------+
1 row in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.

创建一个记录时间表

登录后复制

USE test;
CREATE TABLE test01 (timeline TIMESTAMP);
1.
2.

实例一：

查看MySQL的当前时间并且创建一个定时器，每20秒中插入一次当前的时间

登录后复制

select now();
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 20 SECOND STARTS TIMESTAMP  '2018-09-15 09:20:00'
DO INSERT INTO test.test01 VALUES (CURRENT_TIMESTAMP);
1.
2.
3.
4.

登录后复制

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-09-15 09:16:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> CREATE EVENT e_test_insert
    -> ON SCHEDULE EVERY 20 SECOND STARTS TIMESTAMP  '2018-09-15 09:20:00'
    -> DO INSERT INTO test.test01 VALUES (CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

查看MySQL当前时间：
mysql> select now();

等待20秒钟后，再执行查询成功。

登录后复制

mysql> select * from test01;
+---------------------+
| timeline            |
+---------------------+
| 2018-09-15 09:01:07 |
| 2018-09-15 09:01:27 |
+---------------------+
2 rows in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.

实例二：

创建一个定时器test01，当前时间2分钟后写入数据到test01_lastmonth表，并且清空test01表：

登录后复制

select now();
CREATE EVENT test01
ON SCHEDULE AT TIMESTAMP  '2018-09-15 09:30:00' + INTERVAL 2 MINUTE
do create table test.test01_lastmonth as select * from test.test01;TRUNCATE TABLE test.test01;
1.
2.
3.
4.

**经测试
是新建了一个表test01_lastmonth，但是test01表没有被清空，并且一直在写入数据。说明上述的sql语句的写法是有问题的。
**
于是采用下面的写法是正确的。于是得出结论：采用下面的写法定时器是可以同时添加多条sql语句作为计划任务来执行的

登录后复制

delimiter $$
CREATE EVENT test02
ON SCHEDULE AT TIMESTAMP  '2018-09-15 09:40:00' + INTERVAL 2 MINUTE
COMMENT 'xiaowu create'
do 
    BEGIN
            create table test.test01_lastmonth as select * from test.test01;
            TRUNCATE TABLE test.test01;
    END $$
delimiter ;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

此实例演示过程如下：

登录后复制

mysql> delimiter $$
mysql> CREATE EVENT test02
    -> ON SCHEDULE AT TIMESTAMP  '2018-09-15 09:40:00' + INTERVAL 2 MINUTE
    -> COMMENT 'xiaowu create'
    -> do 
    ->     BEGIN
    ->             create table test.test01_lastmonth as select * from test.test01;
    ->             TRUNCATE TABLE test.test01;
    ->     END $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

此时查看新表还没被创建，旧表test01数据还没被清空

登录后复制

mysql>  select now();select * from test01_lastmonth;select * from test01;
+---------------------+
| now()               |
+---------------------+
| 2018-09-15 09:41:54 |
+---------------------+
1 row in set (0.00 sec)

ERROR 1146 (42S02): Table 'test.test01_lastmonth' doesn't exist
+---------------------+
| timeline            |
+---------------------+
| 2018-09-15 09:39:00 |
| 2018-09-15 09:39:20 |
| 2018-09-15 09:39:40 |
| 2018-09-15 09:40:00 |
| 2018-09-15 09:40:20 |
| 2018-09-15 09:40:40 |
| 2018-09-15 09:41:00 |
| 2018-09-15 09:41:20 |
| 2018-09-15 09:41:40 |
+---------------------+
42 rows in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

此时查看新表被创建，旧表test01数据被清空，并且已经插入一条数据
提示：旧表test01已经被清空了，但是之前的计划事件e_test_insert每20秒插入一条记录到test01表仍然在继续执行

登录后复制

mysql>  select now();select * from test01_lastmonth;select * from test01;
+---------------------+
| now()               |
+---------------------+
| 2018-09-15 09:42:24 |
+---------------------+
1 row in set (0.00 sec)

+---------------------+
| timeline            |
+---------------------+
| 2018-09-15 09:39:00 |
| 2018-09-15 09:39:20 |
| 2018-09-15 09:39:40 |
| 2018-09-15 09:40:00 |
| 2018-09-15 09:40:20 |
| 2018-09-15 09:40:40 |
| 2018-09-15 09:41:00 |
| 2018-09-15 09:41:20 |
| 2018-09-15 09:41:40 |
| 2018-09-15 09:42:00 |
+---------------------+
43 rows in set (0.00 sec)

+---------------------+
| timeline            |
+---------------------+
| 2018-09-15 09:42:20 |
+---------------------+
1 row in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

再次总结测试实例二：

登录后复制

USE test;
CREATE TABLE test01 (timeline TIMESTAMP);
select now();
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 20 SECOND STARTS TIMESTAMP  '2018-09-15 10:10:00'
DO INSERT INTO test.test01 VALUES (CURRENT_TIMESTAMP);
1.
2.
3.
4.
5.
6.

登录后复制

delimiter $$
CREATE EVENT test02
ON SCHEDULE AT TIMESTAMP  '2018-09-15 10:10:00' + INTERVAL 2 MINUTE
COMMENT 'xiaowu create'
do 
    BEGIN
            create table test.test01_lastmonth as select * from test.test01;
            TRUNCATE TABLE test.test01;
    END $$
delimiter ;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

登录后复制

mysql> select now();select * from test01_lastmonth;select * from test01;
+---------------------+
| now()               |
+---------------------+
| 2018-09-15 10:10:39 |
+---------------------+
1 row in set (0.00 sec)

ERROR 1146 (42S02): Table 'test.test01_lastmonth' doesn't exist
+---------------------+
| timeline            |
+---------------------+
| 2018-09-15 10:10:00 |
| 2018-09-15 10:10:20 |
+---------------------+
2 rows in set (0.00 sec)

mysql>
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

此时新表已经创建并且旧表test01数据被清空：

登录后复制

mysql> select now();select * from test01_lastmonth;select * from test01;
+---------------------+
| now()               |
+---------------------+
| 2018-09-15 10:12:00 |
+---------------------+
1 row in set (0.00 sec)

+---------------------+
| timeline            |
+---------------------+
| 2018-09-15 10:10:00 |
| 2018-09-15 10:10:20 |
| 2018-09-15 10:10:40 |
| 2018-09-15 10:11:00 |
| 2018-09-15 10:11:20 |
| 2018-09-15 10:11:40 |
| 2018-09-15 10:12:00 |
+---------------------+
7 rows in set (0.00 sec)

Empty set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

此时被清空的旧表test01 20秒后已经开始插入数据了
提示：旧表test01已经被清空了，但是之前的计划事件e_test_insert每20秒插入一条记录到test01表仍然在继续执行

登录后复制

mysql> select now();select * from test01_lastmonth;select * from test01;
+---------------------+
| now()               |
+---------------------+
| 2018-09-15 10:12:37 |
+---------------------+
1 row in set (0.00 sec)

+---------------------+
| timeline            |
+---------------------+
| 2018-09-15 10:10:00 |
| 2018-09-15 10:10:20 |
| 2018-09-15 10:10:40 |
| 2018-09-15 10:11:00 |
| 2018-09-15 10:11:20 |
| 2018-09-15 10:11:40 |
| 2018-09-15 10:12:00 |
+---------------------+
7 rows in set (0.00 sec)

+---------------------+
| timeline            |
+---------------------+
| 2018-09-15 10:12:20 |
+---------------------+
1 row in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

实例三:定时每天的某个时间点来清空表test03

定时每天的上午10:45:00清空表test03

登录后复制

DROP EVENT IF EXISTS test03;
 CREATE  EVENT  test03
 ON SCHEDULE EVERY 1 day STARTS date_add(concat(current_date(), ' 10:45:00'), interval 0 second)
 ON COMPLETION PRESERVE ENABLE
 DO TRUNCATE TABLE test01;
1.
2.
3.
4.
5.

定时每天的下午18:00:00清空表test03

登录后复制

DROP EVENT IF EXISTS test04;
 CREATE  EVENT  test04 
 ON SCHEDULE EVERY 1 day STARTS date_add(concat(current_date(), ' 18:00:00'), interval 0 second)
 ON COMPLETION PRESERVE ENABLE
 DO TRUNCATE TABLE test03;
1.
2.
3.
4.
5.
6.

实例四：定时每天的上午11:00:00清空表test01

登录后复制

CREATE EVENT test05
ON SCHEDULE  EVERY 1 day STARTS TIMESTAMP (current_date(),'11:00:00')
DO TRUNCATE TABLE test.test01;
查看创建事件调度器语句：
mysql> show create event test05\G
*************************** 1. row ***************************
               Event: test05
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `test05` ON SCHEDULE EVERY 1 DAY STARTS '2018-09-15 11:00:00' ON COMPLETION NOT PRESERVE ENABLE DO TRUNCATE TABLE test.test01
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

实例五：

** 2018年7月20日12点整清空aaa表：**

登录后复制

CREATE EVENT e1_test
ON SCHEDULE AT TIMESTAMP '2018-07-20 12:00:00'
DO TRUNCATE TABLE test.aaa;
1.
2.
3.

实例六：

5天后开启每天定时清空aaa表：

登录后复制

CREATE EVENT e2_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
1.
2.
3.
4.

实例七：
每天定时清空test表，5天后停止执行：

CREATE EVENT e3_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

实例八：

5天后开启每天定时清空test表，一个月后停止执行：

登录后复制

CREATE EVENT e4_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO TRUNCATE TABLE test.aaa;
[ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行，默认为NOT PRESERVE。
1.
2.
3.
4.
5.
6.
7.

实例九：

每天定时清空test表(只执行一次，任务完成后就终止该事件)：

登录后复制

CREATE EVENT e5_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;
[ENABLE | DISABLE]可是设置该事件创建后状态是否开启或关闭，默认为ENABLE。
[COMMENT ‘comment’]可以给该事件加上注释。
1.
2.
3.
4.
5.
6.

实例十：

登录后复制

修改事件(ALTER EVENT)
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
1.
2.
3.
4.
5.
6.
7.
8.

将每天清空test表改为5天清空一次：

登录后复制

ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;
1.
2.