MYSQL stored procedure and event Learning notes

About the creation of stored procedures and events in mysql

The following stored procedure mainly implements the function of querying the data in the table half a year ago. If yes, it stores the data in the file and then deletes the data.

The code is as follows:

Copy code

Create definer = 'root' @ 'localhost' PROCEDURE 'newproc '() BEGIN Select COUNT (*) INTO @ count from S_ACCOUNT where date_add (registerTime, interval 6 month) <= NOW (); IF @ count> 0 THEN Set @ today = TIME_TO_SEC (now ()); Set @ select_ SQL = concat ("select * from S_ACCOUNT where date_add (registerTime, interval 6 month) <= NOW () Into outfile 'C:/", @ today,". txt 'fields terminated ',';"); PREPARE charu FROM @ select_ SQL; EXECUTE charu; Delete from S_ACCOUNT where date_add (registerTime, interval 6 month) <= NOW (); Commit; End if; END;

Fields terminated by ',' delimiter between FIELDS
Optionally enclosed by '"' does not apply a numeric value when fields are surrounded.
Lines terminated by 'N' line break
The following is the code for creating an event. The logic starts from '2017-11-05 09:00:00 'and runs the pro_test () process every day.

The code is as follows:	Copy code
Create definer = 'root' @ 'localhost' EVENT 'newevent' On schedule every 1 day starts '2017-11-05 09:00:00' ON COMPLETION NOT PRESERVE ENABLE DO Call pro_test ();

When creating an event, we may prompt that the event is closed. In this case, you need to manually enable the event.

First, query the status of the scheduled event in SQL: SHOW VARIABLES LIKE 'event _ schedning'
If the returned result is off, it indicates that the task is disabled. If it is on, it indicates that the scheduled task has been enabled.
Find the my. Ini file in the mysql program directory and add an item: event_scheduler = 1
After saving, restart the mysql service. You can find the restart service in service management.
You can also use the script to implement:
Mysql event_scheduler
Enable the event_scheduler SQL command:
Set global event_scheduler = ON;
SET @ global. event_scheduler = ON;
Set global event_scheduler = 1;
SET @ global. event_scheduler = 1;
Instead, disable the event_scheduler command:
Set global event_scheduler = OFF;
SET @ global. event_scheduler = OFF;
Set global event_scheduler = 0;
SET @ global. event_scheduler = 0;