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.
posted @ 2018-09-15 11:44  勤奋的蓝猫  阅读(4)  评论(0编辑  收藏  举报  来源