Step-by-Step explanation of the creation of timed event plan in Mysql _mysql

First, the use of the process
1. There are 3 ways to see whether an event plan is currently open (scheduler):

Show VARIABLES like ' Event_scheduler ';
SELECT @ @event_scheduler;
Show Processlist;

2. There are 4 ways to open the event Plan (scheduler) switch:

SET GLOBAL event_scheduler = 1;
SET @ @global. Event_scheduler = 1;
SET GLOBAL event_scheduler = on;
SET @ @global. Event_scheduler = on;

The key value 1 or on indicates open; 0 or off means close;

3. Permissions on the event plan:
When you invoke an SQL statement by using the event alone, viewing and creating requires the user to have event permissions, and when invoking the SQL statement, the user is required to have permission to execute the SQL. The settings for the event permission are saved in the Event_priv field of the Mysql.user table and the Mysql.db table. (FLUSH privileges;)
When event and procedure are used together, viewing and creating a stored procedure requires the user to have create routine permission, the call stored procedure execution requires the use of Excute permissions, and when the stored procedure calls a specific SQL statement, the user has the right to execute the SQL.

SELECT Host,user,event_priv from Mysql.user;

Gets the currently logged in user and database: SELECT current_user (), SCHEMA ();
You can tell from Figure1 that bfsql@% does not have Event_priv permissions, and the following error occurs when you create an event under that user:
Error code:1044access denied for user ' bfsql ' @ '% ' to database ' blog '
If the above error occurs, executing the following SQL gives bfsql@% permission to create an event:

UPDATE mysql.user SET event_priv = ' Y ' WHERE host= '% ' and user= ' bfsql '; 
FLUSH privileges; 

Finally, you can view all permissions by show grants for ' bfsql ' @ '% ';

4. Create Event:
(1) The syntax for creating events is as follows:

CREATE EVENT [IF not EXISTS] event_name on
SCHEDULE SCHEDULE
[on completion [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ' COMMENT '] do
sql_statement

(2) An example of creating an event is as follows:

DELIMITER $$
CREATE EVENT IF not EXISTS e_blog on SCHEDULE EVERY-SECOND on
completion PRESERVE do
B Egin call
moveblogdata ();
end$$
DELIMITER;

The

 
do sql_statement field represents the SQL statement or stored procedure that the event needs to execute. The SQL statements here can be compound statements that use the Begin and end identifiers to place the compound SQL statements in the order in which they are executed.

5. Event opening and closing:
To open an event:

ALTER EVENT e_test on completion PRESERVE ENABLE;

To close an event:

ALTER EVENT e_test on completion PRESERVE DISABLE;

Second, the example:
the MySQL timer is the system that gives the event, and the timer inside Oracle is the job provided by the system. Less nonsense, create a table below:

CREATE TABLE mytable (
ID int auto_increment NOT NULL,
name varchar (MB) NOT null default ",
introduce text n OT null,
createtime timestamp NOT null,
constraint pk_mytable primary key (ID)
)

Create stored procedures, where the stored procedures are mainly provided to the MySQL timer event to invoke to execute:

CREATE PROCEDURE Mypro ()
BEGIN
insert INTO mytable (name,introduce,createtime) VALUES (' 1111 ', ' inner Mongolia ', now ());
End

This is simply written, just to illustrate the example.

Immediately following the creation of the MySQL timer event:

Create event if not exists eventjob on 
schedule every 1 second on completion does call
PRESERVE ();

This is set to perform once per second

So far all the preparation has been written, to do this, MySQL to use the timer must be done to prepare, that is, the MySQL timer to open:

SET GLOBAL event_scheduler = 1; --Start timer
SET GLOBAL event_scheduler = 0;--Stop timer

The event is also followed by an opening:

ALTER EVENT eventjob on completion PRESERVE ENABLE;  --Open Event
ALTER event eventjob on completion PRESERVE DISABLE;--Close event


show VARIABLES like '%sche% ';--View timer status

At this point, you go to the database inside the table mytable look inside, the system will every second to insert a piece of data, Xi Hee, the task is completed.

SELECT * FROM MyTable