What is a "MySQL event"?

Question

What is a "MySQL event"? What is its purpose? How is it different from a "job scheduler"?

Many posts on SO, and the MySQL documentation, describe how to implement MySQL events, but I am just interested in their purpose.

Monck

Why do you think it should be different than a 'job scheduler'? – Ostia

Answer 1

MySQL events offer an alternative to scheduled tasks and cron jobs.

Events can be used to create backups, delete stale records, aggregate data for reports, and so on. Unlike standard triggers which execute given a certain condition, an event is an object that is triggered by the passage of time and is sometimes referred to as a temporal trigger.

Refer below link explained everything here :

http://phpmaster.com/working-with-mysql-events/

Devisal

Thanks Teez - great answer :). One additional question, it sounds like MySQL events only execute SQL commands. Do you know of a good job scheduler if I want to be executing a PHP script? – Monck

Thanks for the PHPmaster link, very helpful. – Monck

Suppose that I allow users to edit a thing (which has editable=true in the database) for 7 days. Would it be appropriate usage to set a MySQL event to set editable=false after 7 days? Or it should only be used for batch processing etc? – Ib

Answer 2

An event is a scheduled task in MySQL. *The doc explains an event in detail.

For example, you create test table as shown below:

CREATE TABLE test (
  num int
);

Then, you insert the row whose num is 1 as shown below:

INSERT INTO test (num) VALUES (1);

Now, you can create plus_one event which starts adding 1 to num every one second since 2023-11-15 00:00:00 as shown below. *CREATE EVENT statement must have DO statement and DO statement must have at least one SQL statement otherwise there is the error and if 2023-11-15 00:00:00 has already passed, plus_one event starts adding 1 to num every one second immediately just after you create plus_one event and basically, you need to change the default delimiter ; to something like $$ when creating an event otherwise there is error, then after creating an event, you need to change the delimiter $$ back to ; as shown below and my answer explains delimiter and you must select a database when creating an event otherwise there is the error:

DELIMITER $$

CREATE EVENT plus_one
ON SCHEDULE EVERY 1 SECOND
STARTS '2023-11-15 00:00:00'
DO 
BEGIN
UPDATE test SET num = num + 1;
END$$

DELIMITER ;

Then, you can check plus_one event adds 1 to num every one second as shown below:

mysql> SELECT num FROM test;
+------+
| num  |
+------+
|  23  |
+------+
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|  61  |
+------+

Be careful, even if you set the user-defined session variable e.g. @my_value with 1, then use it in an event as shown below:

SET @my_value = 1;

DELIMITER $$

CREATE EVENT plus_one
ON SCHEDULE EVERY 1 SECOND
STARTS '2023-11-15 00:00:00'
DO
BEGIN
UPDATE test SET num = @my_value; -- Here
END$$

DELIMITER ;

Then, @my_value is NULL as shown below:

mysql> SELECT @my_value; 
+----------------------+
| @my_value            |
+----------------------+
| NULL                 |
+----------------------+

And, even if you define the user-defined session variable e.g. @my_value with Hello in an event as shown below:

DELIMITER $$

CREATE EVENT my_event
ON SCHEDULE EVERY 1 SECOND
STARTS '2023-11-15 00:00:00'
DO
BEGIN
SET @my_value = "Hello"; -- Here
END$$

DELIMITER ;

Then, @my_value is NULL as shown below:

mysql> SELECT @my_value; 
+----------------------+
| @my_value            |
+----------------------+
| NULL                 |
+----------------------+

In addition, in this case below, you can create plus_one event not changing the delimiter to $$ without error:

CREATE EVENT plus_one
ON SCHEDULE EVERY 1 SECOND
STARTS '2023-11-15 00:00:00'
DO 
UPDATE test SET num = num + 1;

Sodalite