Database Navicat for MySQL event events to implement daily data periodic operations

During our operations of the database, we typically encounter some point-in-time operational database issues, such as:
(1). Daily 12 o'clock in the morning to the database scheduled backup, settlement and summary;
(2). Delete data for the first three days of the database Daily 2 o'clock in the morning;
(3). Inserting a data over a certain period of time to change a certain value, such as an early warning system.
Here you need to do a simple operation with event events, which will be dealt with in detail below. You might think of a trigger implementation, but if the same table inserts the data, but the trigger updates the update operation is not possible, then you need to try to resolve through the event events.
In a previous article: [Database] Navicat for MySQL trigger update and insert operation
I hope this article is helpful to you, if there are errors or shortcomings in the article, please Haihan ~

I. Event implementation DELETE Events at some point of the day

Start by Navicat for MySQL to see if event events are turned on, specifically code:

Show variables like '%sche% ';

typically events are turned on, as shown in:

If not, the database Super permission setting is required.

Set global Event_scheduler = 1;

where Event_scheduler is on, off indicates off, as follows:
+-----------------+-------+| variable_name | Value |+-----------------+-------+| Event_scheduler | OFF |+-----------------+-------+The following begins the write event operation.
Suppose there is now a table loginuser, including a user name, password, and permission three fields. such as:



at the current point of March 9, 2017 23:48, we set 23:50 to perform the delete eastmount data operation. Where the event code for the SQL statement is as follows:

CREATE EVENT Testeventon SCHEDULE every 1 day starts ' 2017-03-09 23:50:00 '  DOdelete from Loginuser where Username= ' eas Tmount ';

The create event can be viewed as shown in the following:



then after 23:50 execute the query statement will find that the Eastmount user has been deleted.

Click to open the event as shown, but I would recommend that you write SQL statements.

with this SQL statement above, we know the simplest event events, which are further described below. Backup data plus a backup SQL statement execution.
Reference official website: https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

Two. Event implementation timed to insert events at regular intervals

In using event events, you need to turn on and off events, using the following code:
Shutdown Event: ALTER event testevent DISABLE;
Open event:ALTER event TestEvent ENABLE;
The code to see if the event is open is as follows:

SELECT * from Mysql.event;

the output looks like this:


The following defines an event that inserts a row of data every 10 seconds, such as real-time processing, such as receiving traffic data in real time, which is typically used for insert operations. Create a table logininfo, log login information, fields include: ID ordinal (primary key, increment int type), name username, STARTTIME (logon time), ENDTIME (logout time), state (status 0-offline 1-online).


The SQL statement creates the event events as follows:

CREATE EVENT insertevent on    SCHEDULE every SECOND     doinsert into Logininfo (name,starttime,state) VALUES (' Test01 ', now (), ' 1 ');

the results of the operation are as follows:


As you can see, a set of data is inserted every 10 seconds, and the event executes correctly.
Note: In the real development process, you will encounter a MySQL service restart or power outage, and so on, there will be a time scheduler is closed, all events do not work, the workaround, you need to add "Event_scheduler = On;" In the Mysql.ini file.

three. Event implementation exceeds a certain time update status

Suppose there is now an alarm system, when the user enters the room to start timing, when the user is placed in the 2 hours to start the alarm, set the state. The data are as follows:

The SQL statement query returns the following results:

Select Current_timestamp, StartTime, Timestampdiff (Second,starttime,current_timestamp) as T from Logininfo;

as shown in the output, a data is inserted every 10 seconds, so it is constantly updated.

The following statement of the updated update is now shown below:

CREATE EVENT updateevent on    SCHEDULE every SECOND     doupdate logininfo set state=0 WHERE Timestampdiff (second,st Arttime,current_timestamp) >300 and state=1;

the SQL statement that then queries the real-time update status is as follows:

Select ID, state, Current_timestamp, StartTime, Timestampdiff (Second,starttime,current_timestamp) as-T from Logininfo;

output as shown, found 302 may be checked for 300 seconds, may be updated in real time delay, but does not affect the effect.

In this case, the database for a simple warning system is set up, and we have set up 3 events, as shown in:

Add a section of code:

Delimiter//create EVENT EON SCHEDULE    every 5 seconddobegin    DECLARE v INTEGER;    DECLARE CONTINUE HANDLER for SQLEXCEPTION BEGIN END;    SET v = 0;    While v < 5 does        INSERT into T1 VALUES (0);            UPDATE t2 SET S1 = s1 + 1;        SET v = v + 1;    END while; END//delimiter;

Finally hope that the text will help you, basic articles, if there are errors or shortcomings, please Haihan ~
Recently oneself and she too toil, oneself need to pay more, learn more, do for her more, forever.
(By:eastmount 2017-03-10 noon 12 O'Clock http://blog.csdn.net//eastmount/ )

Database Navicat for MySQL event events to implement daily data periodic operations