mysql 定时器概念_MySQL定时器十例演示

最新推荐文章于 2022-03-04 11:12:43 发布

weixin_39938165

最新推荐文章于 2022-03-04 11:12:43 发布

阅读量73

点赞数

文章标签： mysql 定时器概念

本文链接：https://blog.csdn.net/weixin_39938165/article/details/111724673

版权

MySQL定时器十例演示

查看MySQL的版本号：

mysql> select version();

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

| version() |

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

| 5.6.39-log |

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

1 row in set (0.00 sec)

创建一个记录时间表

USE test;

CREATE TABLE test01 (timeline TIMESTAMP);

实例一：

查看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);

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>

查看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)

实例二：

创建一个定时器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;

经测试

是新建了一个表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'

BEGIN

create table test.test01_lastmonth as select * from test.test01;

TRUNCATE TABLE test.test01;

END $$

delimiter ;

此实例演示过程如下：

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 ;

此时查看新表还没被创建，旧表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)

此时查看新表被创建，旧表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)

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

再次总结测试实例二：

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);

delimiter $$

CREATE EVENT test02

ON SCHEDULE AT TIMESTAMP '2018-09-15 10:10:00' + INTERVAL 2 MINUTE

COMMENT 'xiaowu create'

BEGIN

create table test.test01_lastmonth as select * from test.test01;

TRUNCATE TABLE test.test01;

END $$

delimiter ;

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>

此时新表已经创建并且旧表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)

此时被清空的旧表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)

实例三:定时每天的某个时间点来清空表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;

定时每天的下午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;

实例四：定时每天的上午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)

实例五：

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

CREATE EVENT e1_test

ON SCHEDULE AT TIMESTAMP '2018-07-20 12:00:00'

DO TRUNCATE TABLE test.aaa;

实例六：

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

CREATE EVENT e2_test

ON SCHEDULE EVERY 1 DAY

STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY

DO TRUNCATE TABLE test.aaa;

实例七：

每天定时清空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。

实例九：

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

CREATE EVENT e5_test

ON SCHEDULE EVERY 1 DAY

ON COMPLETION NOT PRESERVE

DO TRUNCATE TABLE test.aaa;

[ENABLE | DISABLE]可是设置该事件创建后状态是否开启或关闭，默认为ENABLE。

[COMMENT ‘comment’]可以给该事件加上注释。

实例十：

修改事件(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]

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

ALTER EVENT e_test

ON SCHEDULE EVERY 5 DAY;

weixin_39938165

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 定时器概念_MySQL定时器十例演示

MySQL定时器十例演示查看MySQL的版本号：mysql> select version();+------------+| version() |+------------+| 5.6.39-log |+------------+1 row in set (0.00 sec)创建一个记录时间表USE test;CREATE TABLE test01 (timeline TIMESTAM...
复制链接

扫一扫