Recurring event
This type of event occurs after every certain interval of time. For creating this type of event we can use below command after “ON SCHEDULE”.
CREATE TABLE ramrec(recid INT AUTO_INCREMENT,
msg VARCHAR(50),
rtime DATETIME,
PRIMARY KEY(recid));
-------
DROP EVENT IF EXISTS ram_test_rec;
DELIMITER $$
CREATE EVENT IF NOT EXISTS ram_test_rec
ON SCHEDULE EVERY 2 SECOND
STARTS NOW() + INTERVAL 2 SECOND
ENDS NOW() + INTERVAL 1 MINUTE
DO
BEGIN
INSERT INTO ramrec(msg, rtime)
VALUES('testing recurring event', now());
END$$
DELIMITER ;
https://phoenixnap.com/kb/mysql-event
----------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use ramesh
Database changed
mysql> CREATE TABLE ramrec(recid INT AUTO_INCREMENT,
-> msg VARCHAR(50),
-> rtime DATETIME,
-> PRIMARY KEY(recid));
Query OK, 0 rows affected (2.65 sec)
mysql> select * from ramrec;
Empty set (0.03 sec)
mysql> DROP EVENT IF EXISTS ram_test_rec;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> DELIMITER $$
mysql> CREATE EVENT IF NOT EXISTS ram_test_rec
-> ON SCHEDULE EVERY 2 SECOND
-> STARTS NOW() + INTERVAL 2 SECOND
-> ENDS NOW() + INTERVAL 1 MINUTE
-> DO
-> BEGIN
-> INSERT INTO ramrec(msg, rtime)
-> VALUES('testing recurring event', now());
-> END$$
Query OK, 0 rows affected (0.15 sec)
mysql> DELIMITER ;
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg | rtime |
+-------+-------------------------+---------------------+
| 1 | testing recurring event | 2021-10-10 20:16:42 |
| 2 | testing recurring event | 2021-10-10 20:16:44 |
| 3 | testing recurring event | 2021-10-10 20:16:46 |
| 4 | testing recurring event | 2021-10-10 20:16:48 |
| 5 | testing recurring event | 2021-10-10 20:16:50 |
| 6 | testing recurring event | 2021-10-10 20:16:52 |
| 7 | testing recurring event | 2021-10-10 20:16:54 |
+-------+-------------------------+---------------------+
7 rows in set (0.00 sec)
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg | rtime |
+-------+-------------------------+---------------------+
| 1 | testing recurring event | 2021-10-10 20:16:42 |
| 2 | testing recurring event | 2021-10-10 20:16:44 |
| 3 | testing recurring event | 2021-10-10 20:16:46 |
| 4 | testing recurring event | 2021-10-10 20:16:48 |
| 5 | testing recurring event | 2021-10-10 20:16:50 |
| 6 | testing recurring event | 2021-10-10 20:16:52 |
| 7 | testing recurring event | 2021-10-10 20:16:54 |
| 8 | testing recurring event | 2021-10-10 20:16:56 |
| 9 | testing recurring event | 2021-10-10 20:16:58 |
| 10 | testing recurring event | 2021-10-10 20:17:00 |
| 11 | testing recurring event | 2021-10-10 20:17:02 |
| 12 | testing recurring event | 2021-10-10 20:17:05 |
| 13 | testing recurring event | 2021-10-10 20:17:06 |
+-------+-------------------------+---------------------+
13 rows in set (0.00 sec)
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg | rtime |
+-------+-------------------------+---------------------+
| 1 | testing recurring event | 2021-10-10 20:16:42 |
| 2 | testing recurring event | 2021-10-10 20:16:44 |
| 3 | testing recurring event | 2021-10-10 20:16:46 |
| 4 | testing recurring event | 2021-10-10 20:16:48 |
| 5 | testing recurring event | 2021-10-10 20:16:50 |
| 6 | testing recurring event | 2021-10-10 20:16:52 |
| 7 | testing recurring event | 2021-10-10 20:16:54 |
| 8 | testing recurring event | 2021-10-10 20:16:56 |
| 9 | testing recurring event | 2021-10-10 20:16:58 |
| 10 | testing recurring event | 2021-10-10 20:17:00 |
| 11 | testing recurring event | 2021-10-10 20:17:02 |
| 12 | testing recurring event | 2021-10-10 20:17:05 |
| 13 | testing recurring event | 2021-10-10 20:17:06 |
| 14 | testing recurring event | 2021-10-10 20:17:08 |
| 15 | testing recurring event | 2021-10-10 20:17:10 |
| 16 | testing recurring event | 2021-10-10 20:17:12 |
| 17 | testing recurring event | 2021-10-10 20:17:14 |
| 18 | testing recurring event | 2021-10-10 20:17:16 |
+-------+-------------------------+---------------------+
18 rows in set (0.00 sec)
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg | rtime |
+-------+-------------------------+---------------------+
| 1 | testing recurring event | 2021-10-10 20:16:42 |
| 2 | testing recurring event | 2021-10-10 20:16:44 |
| 3 | testing recurring event | 2021-10-10 20:16:46 |
| 4 | testing recurring event | 2021-10-10 20:16:48 |
| 5 | testing recurring event | 2021-10-10 20:16:50 |
| 6 | testing recurring event | 2021-10-10 20:16:52 |
| 7 | testing recurring event | 2021-10-10 20:16:54 |
| 8 | testing recurring event | 2021-10-10 20:16:56 |
| 9 | testing recurring event | 2021-10-10 20:16:58 |
| 10 | testing recurring event | 2021-10-10 20:17:00 |
| 11 | testing recurring event | 2021-10-10 20:17:02 |
| 12 | testing recurring event | 2021-10-10 20:17:05 |
| 13 | testing recurring event | 2021-10-10 20:17:06 |
| 14 | testing recurring event | 2021-10-10 20:17:08 |
| 15 | testing recurring event | 2021-10-10 20:17:10 |
| 16 | testing recurring event | 2021-10-10 20:17:12 |
| 17 | testing recurring event | 2021-10-10 20:17:14 |
| 18 | testing recurring event | 2021-10-10 20:17:16 |
| 19 | testing recurring event | 2021-10-10 20:17:18 |
| 20 | testing recurring event | 2021-10-10 20:17:20 |
| 21 | testing recurring event | 2021-10-10 20:17:22 |
| 22 | testing recurring event | 2021-10-10 20:17:24 |
| 23 | testing recurring event | 2021-10-10 20:17:26 |
+-------+-------------------------+---------------------+
23 rows in set (0.00 sec)
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg | rtime |
+-------+-------------------------+---------------------+
| 1 | testing recurring event | 2021-10-10 20:16:42 |
| 2 | testing recurring event | 2021-10-10 20:16:44 |
| 3 | testing recurring event | 2021-10-10 20:16:46 |
| 4 | testing recurring event | 2021-10-10 20:16:48 |
| 5 | testing recurring event | 2021-10-10 20:16:50 |
| 6 | testing recurring event | 2021-10-10 20:16:52 |
| 7 | testing recurring event | 2021-10-10 20:16:54 |
| 8 | testing recurring event | 2021-10-10 20:16:56 |
| 9 | testing recurring event | 2021-10-10 20:16:58 |
| 10 | testing recurring event | 2021-10-10 20:17:00 |
| 11 | testing recurring event | 2021-10-10 20:17:02 |
| 12 | testing recurring event | 2021-10-10 20:17:05 |
| 13 | testing recurring event | 2021-10-10 20:17:06 |
| 14 | testing recurring event | 2021-10-10 20:17:08 |
| 15 | testing recurring event | 2021-10-10 20:17:10 |
| 16 | testing recurring event | 2021-10-10 20:17:12 |
| 17 | testing recurring event | 2021-10-10 20:17:14 |
| 18 | testing recurring event | 2021-10-10 20:17:16 |
| 19 | testing recurring event | 2021-10-10 20:17:18 |
| 20 | testing recurring event | 2021-10-10 20:17:20 |
| 21 | testing recurring event | 2021-10-10 20:17:22 |
| 22 | testing recurring event | 2021-10-10 20:17:24 |
| 23 | testing recurring event | 2021-10-10 20:17:26 |
| 24 | testing recurring event | 2021-10-10 20:17:29 |
| 25 | testing recurring event | 2021-10-10 20:17:30 |
| 26 | testing recurring event | 2021-10-10 20:17:32 |
+-------+-------------------------+---------------------+
26 rows in set (0.00 sec)
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg | rtime |
+-------+-------------------------+---------------------+
| 1 | testing recurring event | 2021-10-10 20:16:42 |
| 2 | testing recurring event | 2021-10-10 20:16:44 |
| 3 | testing recurring event | 2021-10-10 20:16:46 |
| 4 | testing recurring event | 2021-10-10 20:16:48 |
| 5 | testing recurring event | 2021-10-10 20:16:50 |
| 6 | testing recurring event | 2021-10-10 20:16:52 |
| 7 | testing recurring event | 2021-10-10 20:16:54 |
| 8 | testing recurring event | 2021-10-10 20:16:56 |
| 9 | testing recurring event | 2021-10-10 20:16:58 |
| 10 | testing recurring event | 2021-10-10 20:17:00 |
| 11 | testing recurring event | 2021-10-10 20:17:02 |
| 12 | testing recurring event | 2021-10-10 20:17:05 |
| 13 | testing recurring event | 2021-10-10 20:17:06 |
| 14 | testing recurring event | 2021-10-10 20:17:08 |
| 15 | testing recurring event | 2021-10-10 20:17:10 |
| 16 | testing recurring event | 2021-10-10 20:17:12 |
| 17 | testing recurring event | 2021-10-10 20:17:14 |
| 18 | testing recurring event | 2021-10-10 20:17:16 |
| 19 | testing recurring event | 2021-10-10 20:17:18 |
| 20 | testing recurring event | 2021-10-10 20:17:20 |
| 21 | testing recurring event | 2021-10-10 20:17:22 |
| 22 | testing recurring event | 2021-10-10 20:17:24 |
| 23 | testing recurring event | 2021-10-10 20:17:26 |
| 24 | testing recurring event | 2021-10-10 20:17:29 |
| 25 | testing recurring event | 2021-10-10 20:17:30 |
| 26 | testing recurring event | 2021-10-10 20:17:32 |
| 27 | testing recurring event | 2021-10-10 20:17:34 |
+-------+-------------------------+---------------------+
27 rows in set (0.05 sec)
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg | rtime |
+-------+-------------------------+---------------------+
| 1 | testing recurring event | 2021-10-10 20:16:42 |
| 2 | testing recurring event | 2021-10-10 20:16:44 |
| 3 | testing recurring event | 2021-10-10 20:16:46 |
| 4 | testing recurring event | 2021-10-10 20:16:48 |
| 5 | testing recurring event | 2021-10-10 20:16:50 |
| 6 | testing recurring event | 2021-10-10 20:16:52 |
| 7 | testing recurring event | 2021-10-10 20:16:54 |
| 8 | testing recurring event | 2021-10-10 20:16:56 |
| 9 | testing recurring event | 2021-10-10 20:16:58 |
| 10 | testing recurring event | 2021-10-10 20:17:00 |
| 11 | testing recurring event | 2021-10-10 20:17:02 |
| 12 | testing recurring event | 2021-10-10 20:17:05 |
| 13 | testing recurring event | 2021-10-10 20:17:06 |
| 14 | testing recurring event | 2021-10-10 20:17:08 |
| 15 | testing recurring event | 2021-10-10 20:17:10 |
| 16 | testing recurring event | 2021-10-10 20:17:12 |
| 17 | testing recurring event | 2021-10-10 20:17:14 |
| 18 | testing recurring event | 2021-10-10 20:17:16 |
| 19 | testing recurring event | 2021-10-10 20:17:18 |
| 20 | testing recurring event | 2021-10-10 20:17:20 |
| 21 | testing recurring event | 2021-10-10 20:17:22 |
| 22 | testing recurring event | 2021-10-10 20:17:24 |
| 23 | testing recurring event | 2021-10-10 20:17:26 |
| 24 | testing recurring event | 2021-10-10 20:17:29 |
| 25 | testing recurring event | 2021-10-10 20:17:30 |
| 26 | testing recurring event | 2021-10-10 20:17:32 |
| 27 | testing recurring event | 2021-10-10 20:17:34 |
| 28 | testing recurring event | 2021-10-10 20:17:36 |
+-------+-------------------------+---------------------+
28 rows in set (0.00 sec)
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg | rtime |
+-------+-------------------------+---------------------+
| 1 | testing recurring event | 2021-10-10 20:16:42 |
| 2 | testing recurring event | 2021-10-10 20:16:44 |
| 3 | testing recurring event | 2021-10-10 20:16:46 |
| 4 | testing recurring event | 2021-10-10 20:16:48 |
| 5 | testing recurring event | 2021-10-10 20:16:50 |
| 6 | testing recurring event | 2021-10-10 20:16:52 |
| 7 | testing recurring event | 2021-10-10 20:16:54 |
| 8 | testing recurring event | 2021-10-10 20:16:56 |
| 9 | testing recurring event | 2021-10-10 20:16:58 |
| 10 | testing recurring event | 2021-10-10 20:17:00 |
| 11 | testing recurring event | 2021-10-10 20:17:02 |
| 12 | testing recurring event | 2021-10-10 20:17:05 |
| 13 | testing recurring event | 2021-10-10 20:17:06 |
| 14 | testing recurring event | 2021-10-10 20:17:08 |
| 15 | testing recurring event | 2021-10-10 20:17:10 |
| 16 | testing recurring event | 2021-10-10 20:17:12 |
| 17 | testing recurring event | 2021-10-10 20:17:14 |
| 18 | testing recurring event | 2021-10-10 20:17:16 |
| 19 | testing recurring event | 2021-10-10 20:17:18 |
| 20 | testing recurring event | 2021-10-10 20:17:20 |
| 21 | testing recurring event | 2021-10-10 20:17:22 |
| 22 | testing recurring event | 2021-10-10 20:17:24 |
| 23 | testing recurring event | 2021-10-10 20:17:26 |
| 24 | testing recurring event | 2021-10-10 20:17:29 |
| 25 | testing recurring event | 2021-10-10 20:17:30 |
| 26 | testing recurring event | 2021-10-10 20:17:32 |
| 27 | testing recurring event | 2021-10-10 20:17:34 |
| 28 | testing recurring event | 2021-10-10 20:17:36 |
| 29 | testing recurring event | 2021-10-10 20:17:38 |
| 30 | testing recurring event | 2021-10-10 20:17:40 |
+-------+-------------------------+---------------------+
30 rows in set (0.00 sec)
mysql> select * from ramrec;
+-------+-------------------------+---------------------+
| recid | msg | rtime |
+-------+-------------------------+---------------------+
| 1 | testing recurring event | 2021-10-10 20:16:42 |
| 2 | testing recurring event | 2021-10-10 20:16:44 |
| 3 | testing recurring event | 2021-10-10 20:16:46 |
| 4 | testing recurring event | 2021-10-10 20:16:48 |
| 5 | testing recurring event | 2021-10-10 20:16:50 |
| 6 | testing recurring event | 2021-10-10 20:16:52 |
| 7 | testing recurring event | 2021-10-10 20:16:54 |
| 8 | testing recurring event | 2021-10-10 20:16:56 |
| 9 | testing recurring event | 2021-10-10 20:16:58 |
| 10 | testing recurring event | 2021-10-10 20:17:00 |
| 11 | testing recurring event | 2021-10-10 20:17:02 |
| 12 | testing recurring event | 2021-10-10 20:17:05 |
| 13 | testing recurring event | 2021-10-10 20:17:06 |
| 14 | testing recurring event | 2021-10-10 20:17:08 |
| 15 | testing recurring event | 2021-10-10 20:17:10 |
| 16 | testing recurring event | 2021-10-10 20:17:12 |
| 17 | testing recurring event | 2021-10-10 20:17:14 |
| 18 | testing recurring event | 2021-10-10 20:17:16 |
| 19 | testing recurring event | 2021-10-10 20:17:18 |
| 20 | testing recurring event | 2021-10-10 20:17:20 |
| 21 | testing recurring event | 2021-10-10 20:17:22 |
| 22 | testing recurring event | 2021-10-10 20:17:24 |
| 23 | testing recurring event | 2021-10-10 20:17:26 |
| 24 | testing recurring event | 2021-10-10 20:17:29 |
| 25 | testing recurring event | 2021-10-10 20:17:30 |
| 26 | testing recurring event | 2021-10-10 20:17:32 |
| 27 | testing recurring event | 2021-10-10 20:17:34 |
| 28 | testing recurring event | 2021-10-10 20:17:36 |
| 29 | testing recurring event | 2021-10-10 20:17:38 |
| 30 | testing recurring event | 2021-10-10 20:17:40 |
+-------+-------------------------+---------------------+
30 rows in set (0.00 sec)
mysql> show events;
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| ramesh | upd_orders | root@localhost | SYSTEM | ONE TIME | 2021-10-10 18:17:13 | NULL | NULL | NULL | NULL | DISABLED | 1 | cp850 | cp850_general_ci | utf8mb4_0900_ai_ci |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set (0.33 sec)
mysql> show events/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show events /G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show events \G;
*************************** 1. row ***************************
Db: ramesh
Name: upd_orders
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2021-10-10 18:17:13
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: DISABLED
Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.